Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Reverse List in Excel
  • How to add Trendline to a chart in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • Error Bars in Excel

References

  • How to use Excel COLUMN Function
  • Convert text string to valid reference in Excel using Indirect function
  • Lookup entire row in Excel
  • To count total rows in a range in Excel
  • How to get last column number in range in Excel

Data Validations

  • Excel Data validation no punctuation
  • Excel Data validation with conditional list
  • Excel Data validation whole percentage only
  • Excel Data validation don’t exceed total
  • Excel Data validation specific characters only

How to create dynamic named range with OFFSET in Excel

by

One way to create a dynamic named range with a formula is to use the OFFSET function together with the COUNTA function. Dynamic ranges are also known as expanding ranges – they automatically expand and contract  to accommodate new or deleted data.

Note: OFFSET is a volatile function, which means it recalculates with every change to a worksheet. With a modern machine and smaller data set, this should’t cause a problem but you may see slower performance on large data sets. In that case, consider building a dynamic named range with the INDEX function instead.

Formula

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

Explanation

In the example shown, the formula used for the dynamic range is:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

How this formula works

This formula uses the OFFSET function to generate a range that expands and contracts by adjusting height and width based on a count of non-empty cells.

Worked Example:   Fibonacci Sequence in Excel

The first argument in OFFSET represents the first cell in the data (the origin), which in this case is cell B5. The next two arguments are offsets for rows and columns, and are supplied as zero.

The last two arguments represent height and width. Height and width are generated on the fly by using COUNTA, which makes the the resulting reference dynamic.

Worked Example:   Average last 5 values in columns in Excel

For height, we use the COUNTA function to count non-empty values in the range B5:B100. This assumes no blank values in the data, and no values beyond B100. COUNTA returns 6.

For width, we use the COUNTA function to count non-empty values in the range B5:Z5. This assumes no header cells, and no headers beyond Z5. COUNTA returns 6.

At this point, the formula looks like this:

=OFFSET(B5,0,0,6,6)

With this information, OFFSET returns a reference to B5:G10, which corresponds to a range 6 rows height by 6 columns across.

Note: The ranges used for height and width should be adjusted to match the worksheet layout.

Worked Example:   With vs Without Array Formula in Excel

Variation with full column/row references

You can also use full column and row references for height and width like so:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Note that height is being adjusted with -2 to take into account header and title values in cells B4 and B2. The advantage to this approach is the simplicity of the ranges inside COUNTA. The disadvantage comes from the huge size full columns and rows — care must be taken to prevent errant values outside the range, as they can easily throw off the count.

Post navigation

Previous Post:

How to get address of first cell in range in Excel

Next Post:

How to retrieve first match between two ranges in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • IF, AND, OR and NOT Functions Examples in Excel
  • Invoice status with nested if in Excel
  • How to use Excel OR Function
  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function

Date Time

  • DATEDIF function: Description, Usage, Syntax, Examples and Explanation
  • Calculate number of hours between two times in Excel
  • Convert Excel time to decimal minutes
  • Calculate series of dates by workdays in Excel
  • Count day of week between dates in Excel

Grouping

  • Map text to numbers in Excel
  • Group numbers with VLOOKUP in Excel
  • Calculate conditional mode with criteria in Excel
  • Map inputs to arbitrary values in Excel
  • Group numbers at uneven intervals in Excel

General

  • AutoFit Column Width, AutoFit Row Height in Excel
  • Excel Autofill Cell Ranges, Copy, Paste
  • Create dynamic workbook reference to another workbook in Excel
  • How to calculate percent of goal in Excel
  • Count cells that do not contain errors in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning