How to create and use dynamic named range in Excel

In this tutorial, you will learn how to create a dynamic named range in Excel and how to use it in formulas to have new data included in calculations automatically.

In last week's tutorial, we looked at different ways to define a static named range in Excel. A static name always refers to the same cells, meaning you would have to update the range reference manually whenever you add new or remove existing data.

If you are working with a continuously changing data set, you may want to make your named range dynamic so that it automatically expands to accommodate new entries or contracts to exclude removed data. Further on in this tutorial, you will find detailed step-by-step guidance on how to do this.

How to create a dynamic named range in Excel

For starters, let's build a dynamic named range consisting of a single column and a variable number of rows. To have it done, perform these steps:

  1. On the Formula tab, in the Defined Names group, click Define Name. Or, press Ctrl + F3 to open the Excel Name Manager, and click the New… button.
  2. Either way, the New Name dialogue box will open, where you specify the following details:
    • In the Name box, type the name for your dynamic range.
    • In the Scope dropdown, set the name's scope. Workbook (default) is recommended in most cases.
    • In the Refers to box, enter either OFFSET COUNTA or INDEX COUNTA formula.
  3. Click OK. Done!

In the following screenshot, we define a dynamic range named Items that accommodates all cells with data in column A, except for the header row:
Building a dynamic named range in Excel

OFFSET formula to define an Excel dynamic named range

To make a dynamic named range in Excel, you can use the following generic formula:

OFFSET(first_cell, 0, 0, COUNTA(range), 1)

Where:

  • first_cell - the first item to be included in the named range, for example $A$1.
  • range - an absolute reference to the entire column such as $A:$A if your data starts in row 1; or an absolute range reference like $A$2:$A$2000 if your data begins in some other row.

This formula leverages the COUNTA function to count the non-blank cells in the specified column, and this count is used for the height argument in OFFSET, specifying how many rows to include in the range.

For instance, to create a dynamic named range starting in cell A1 of Sheet3, use this formula:

=OFFSET(Sheet3!$A$1, 0, 0, COUNTA(Sheet3!$A:$A), 1)

If there is some data above the topmost cell of your range, adjust for it by subtracting the corresponding number of rows from COUNTA's result.

For example, if there is a single header row and the data starts in A2, subtract 1:

=OFFSET(Sheet3!$A$2, 0, 0, COUNTA(Sheet3!$A:$A) -1, 1)

For two header rows, with the data starting in A3, adjust by subtracting 2:

=OFFSET(Sheet3!$A$3, 0, 0, COUNTA(Sheet3!$A:$A) -2, 1)

Alternatively, you can provide a range to the COUNTA function that exceeds the expected maximum number of values in the dynamic range. For example:

=OFFSET(Sheet3!$A$2, 0, 0, COUNTA(Sheet3!$A$2:$A$2000), 1)

If you're defining the range on the current worksheet, you can omit the sheet name, as Excel will handle it automatically. If the range is on another sheet, include the sheet name followed by an exclamation point before the cell reference (as shown above).

Usage notes:

For the formula to work correctly, make sure that:

  • There are no blank cells between data entries in the referenced column.
  • There is no other data below the cells intended for your dynamic range.

INDEX formula to make a dynamic named range in Excel

Another way to create an Excel dynamic range is using COUNTA in combination with the INDEX function.

first_cell:INDEX(column, COUNTA(column))

This formula consists of two parts:

  • On the left side of the range operator (:), place the hard-coded starting reference like $A$2.
  • On the right side, you use the INDEX function to figure out the ending reference. Here, you supply the entire column A for the array and use COUNTA to get the row number (i.e. the number of non-empty cells in column A).

For our sample dataset, the formula goes as follows:

=$A$2:INDEX($A:$A, COUNTA($A:$A))

Since there are 5 non-blank cells in column A, including a column header, COUNTA returns 5. Consequently, INDEX returns $A$5, which is the last used cell in column A (usually an Index formula returns a value, but the reference operator forces it to return a reference). And because we have set $A$2 as the starting point, the final result of the formula is the range $A$2:$A$5.

To test the newly created dynamic range, you can have COUNTA fetch the items count:

=COUNTA(Items)
Testing the dynamic named range

If all done properly, the result of the formula will change once you add or remove items to/from the list:
The dynamic named range expands to include new data in the calculation.

Please keep in mind that for correct work of the formula, the referenced column should not contain any additional data below the dynamic range cells.

Note. While both formulas discussed above yield the same result, there is a performance difference to consider. OFFSET is a volatile function, meaning it recalculates with every change to the sheet. On modern, high-performance machines with reasonably sized datasets, this generally won't cause issues. However, on low-capacity machines or with large datasets, this recalculation may slow down Excel. In such cases, using the INDEX function to create a dynamic named range is a more efficient option.

How to make two-dimensional dynamic range in Excel

To build a two-dimensional named range, where not only the number of rows but also the number of columns is dynamic, use the following modification of the INDEX COUNTA formula:

first_cell:INDEX($1:$1048576, COUNTA(first_column), COUNTA(first_row)))

In this formula, you have two COUNTA functions to get the last non-empty row and last non-empty column (row_num and column_num arguments of the INDEX function, respectively). In the array argument, you feed the entire worksheet (1048576 rows in Excel 365 - 2007; 65535 rows in Excel 2003 and lower).

And now, let's define one more dynamic range for our data set: the range named sales that includes sales figures for 3 months (Jan to Mar) and adjusts automatically as you add new items (rows) or months (columns) to the table.

With the sales data beginning in column B, row 2, the formula takes the following shape:

=$B$2:INDEX($1:$1048576,COUNTA($B:$B),COUNTA($2:$2))
Making a two-dimensional dynamic range in Excel

To make sure your dynamic range works as it is supposed to, enter the following formulas somewhere on the sheet:

=SUM(sales)

=SUM(B2:D5)

As you can see in the screenshot below, both formulas return the same total. The difference reveals itself in the moment you add new entries to the table: the first formula (with the dynamic named range) will update automatically, whereas the second one will have to be updated manually with each change. That makes a huge difference, uh?
Using a two-dimensional dynamic range in a formula

How to use dynamic named ranges in Excel formulas

In the previous sections of this tutorial, you have already seen a couple of simple formulas that use dynamic ranges. Now, let's try to come up with something more meaningful that shows the real value of an Excel dynamic named range.

For this example, we are going to take the classic INDEX MATCH formula that performs Vlookup in Excel:

INDEX (return_range, MATCH (lookup_value, lookup_range, 0))

Sample data set

…and see how we can make the formula even more powerful with the use of dynamic named ranges.

As shown in the screenshot above, we are attempting to build a dashboard, where the user enters an item name in H1 and gets the total sales for that item in H2. Our sample table created for demonstration purposes contains only 4 items, but in your real-life sheets there can be hundreds and even thousands of rows. Furthermore, new items can be added on a daily basis, so using references is not an option, because you'd have to update the formula over and over again. I'm too lazy for that! :)

To force the formula to expand automatically, we are going to define 3 names: 2 dynamic ranges, and 1 static named cell:

Lookup_range: =$A$2:INDEX($A:$A, COUNTA($A:$A))

Return_range: =$E$2:INDEX($E:$E, COUNTA($E:$E))

Lookup_value: =$H$1

Note. Excel will add the name of the current sheet to all references, so before creating the names be sure to open the sheet with your source data.

Now, start typing the formula in H1. When it comes to the first argument, type a few characters of the name you want to use, and Excel will show all available matching names. Double-click the appropriate name, and it will be inserted in the formula right away:
Adding a named range to a formula

The completed formula looks as follows:

=INDEX(Return_range, MATCH(Lookup_value, Lookup_range, 0))

And works perfectly!
Using dynamic named ranges in a formula

As soon as you add new records to the table, they will be included in your calculations at once, without you having to make a single change to the formula! And if you ever need to port the formula to another Excel file, simply create the same names in the destination workbook, copy/paste the formula, and get it working immediately.

Tip. Apart from making formulas more durable, dynamic ranges come in handy for creating dynamic dropdown lists.

This is how you create and use dynamic named ranges in Excel. To have a closer look the formulas discussed in this tutorial, you are welcome to download our sample workbook below. I thank you for reading and hope to see you on our blog next week!

Available downloads

Excel Dynamic Named Range - sample workbook

Table of contents

54 comments

  1. When recording macros in Excel, is there a way to make the range dynamic so if the data I upload next time has more rows than when I first recording it will account for those?

  2. Hello.
    Please I'd like to create a dynamic range from two separate ADDRESS functions for use in an INDEX & MATCH function. I'd then like to use this range in the MATCH function. I'm doing this so that I can automate the process of collating different items based on the dynamic lookup-item in MATCH function.

    The ADDRESS functions are working good, so also is the MATCH and INDEX functions; but it all falls apart when I try to combine them like:

    INDEX(b3: w30, MATCH(c4, (ADDRESS((d5-1),7)) : (ADDRESS((d5+10),7)), 0), 20).

    Please how can I achieve creating the dynamic range for the MATCH Function?
    Thank you.

  3. Hi -
    Not sure if this is relevant --
    I want to increment a Cell index number --
    eg Cell referenced in sheet -- =Sheet1!Findx
    iow how can I modify indx if indx=5 thus F5, how can I modify it with formula to eg F6

    use-case -
    I have on a spreadsheet a integer in a Cell eg C5 = integer 10
    What I want to do --
    Extract a value from the Sheet1 -- =Sheet1!(Findx) where the indx will now be the value of integer in C5
    iow =Sheet1!F10

    How Can I modify the formula such that value retrieved will be =Sheet1F10+offsett
    so that if offset is 5 -- The value in Sheet1 @ F15 (10+5) will be retrieved

    Thank you

  4. Hello. Is it possible to store the values of a dynamic array in a named range already calculated? For example, when I enter this formula for a new named range, the name manager shows the values as an ellipsis:

    =ABS(INDEX(A$1:A$1000,0,1))

    I'm assuming this means Excel will recalculate that formula each time the name is referenced. Is there a way to avoid this without first having to create a column with the absolute values already calculated, and then pointing the new name to that column?

    Thank you.

  5. I'm trying to add a formula in the cell following a sequence formula that is dynamic. The sequence formula can autofill in 6 cells (I want to add the formula in the 7th cell) of the sequence formula can autofill in 8 cells (I want to add the formula in the 9th cell).
    Is there a way to do that automatically?

  6. I'm trying to do a graph to display the last 6 days worth of data using OFFSET. The formula works if I have 6 or more days in my table, but I'm receiving reference errors if I have less than 5 days.
    E9=01Jan2023
    F9=02Jan2023
    G9=03Jan2023
    H9=04Jan2023 .....

    this is my formula.
    =OFFSET($E$9,,IF(COUNTA($E$9:$GA$9)<6,0,COUNTA($E$9:$GA$9)-6),,IF(COUNTA($E$9:$GA$9)<6,COUNTA($E$9:$GA$9),6))

    would you be able to identify where my problem lies or how I can display the graph with only 1, 2, 3, 4 or 5 days worth of data without error message?

  7. Hi,

    I have production report with two columns one column for dates and second column is for case number so I need result how many case number completes for the date.
    Example.
    A column B column
    03/13/23 AS123
    03/13/23 AS234
    03/14/23 AS768

    I need countifs formula for with match date on second sheet.

  8. Image attached is the best way to understand what the formula supposed to achieve and the problem I am trying to resolve. However, I will try to explain the requirement which the calculation is trying to satisfy.

    Background:

    Business Entity (Corporation, such as Amazon) - US Tax Code allow a business to carryforward Operating Loss of a business entity to be used to OFFSET income in future years, until the loss is fully used. Depending on the income in future years, previous year(s) loss can be completely utilized in "One Single Year", or spread across multiple years (as shown in Row 33 - Year = 2017)

    As shown in the table below, the first three columns capture the details such as NOL Generated Amount, Used, or Expired for the Year of NOL Generated which is mentioned in column "Year". Column H calculate how much of the income from the Year (mentioned in Year column) is used in "Current Year" (which in this example is 2022 - bottom row).

    Now, based on the above-mentioned facts so far, the calculation is straightforward, where Total Available column give me the amount of NOL that can be used for Current Year, and the amount utilized needs to be deducted from the Income as calculation progress through subsequent years.

    **
    Here comes the Wrinkle and hence my problem, because of Tax Rule Changes:

    Post 2017, there is difference between how the NOL is treated (or for that matter, how much of it can be used). NOLs generated prior to 2018 (Year <2018 in column "Year") vs NOLs generated AFTER 2017.

    Tax code now limit the amount of NOL taxpayer can use if it was generated after 2017, and the way it does is taking 80% of the Taxable Income left after deducting total Pre-2018 NOL from the Taxable Income.

    Example:
    Taxable Income = $1,000,000
    Total Pre-2018 NOL (Sum of Column H for Years 2001 through 2017) = $400,000

    Taxable Income AFTER Pre-2018 NOL = $600,000
    NOL Limitation %= 80%
    Taxable Income Limit for Post-2017 NOL = $480,000

    Total Post-2017 NOL (Sum of Column H for Years 2018 through 2021) = $700,000

    Taxable Income after NOL = $120,000
    --As you can see, Total Post-2017 NOL = $700,000 and prior to change in tax rules, entire income would have been OFFSET and still $100,000 worth of loss was left which can be carried forward into the future, but due to tax rule change, taxpayer can use ONLY $480,000 of NOL from $700,000.

    **
    In the formula I am working on, I need to incorporate above mentioned calculation based on the Year NOL is generated in a "GENERIC" formula, so that I do not have to manually adjust the worksheet every year into the future.

    The highlighted cell reference in the expression shown in the image needs to be generated dynamically based on the value in Year column, so that when the formula is dragged down and reaches rows corresponding to Year = 2018, then first part of the SUM range is set as absolute cell reference and the second part is same for 2018 and increases for each subsequent year.

    Not able to insert image; hence, manually providing the formulas:

    Columns = Year | Total NOL Generated | Previously Utilized | Expired | Total NOL Available | Current Year Utilization | Carryforward | Expiration

    Rows:
    F12 = Income before NOL
    G17 = First Row [Total Available]

    Row 1 (2001) = MAX(0,MIN(F12,G17))

    Row 2 through 17 (2002 - 2017) = IF(C18<2018,MAX(0,MIN(G18,$F$12-SUM($H$17:H17)))

    Row 18 (2018) = IF(C35=2018,MAX(0,MIN(G35,(($F$12-SUM($H$17:$H$33))*$J$14)))

    Row 19 - 21 (2019 - 2021) = MAX(0,MIN(G35,(($F$12-SUM($H$17:$H$33))*$J$14)-SUM($H$34:H34)))))

    **Row 2 through 21 have a generic formula which is combination of all the individual expressions mentioned above. Above the expression is broken down to illustrate which part of the expression condition apply to which rows in the NOL schedule.

    The problem is with expression "SUM($H$34:H34)" because as the formula is dragged down from first row with Year = 2018 upwards, the formula falls apart. At this point, I have to manually manage the formula based on where the Year = 2018, and will have to do the same next year, unless the cell reference can be generated dynamically based on value in Year column.

    Any suggestion?

  9. Good day, I need some assistance please...I have an excel sheet with Dynamic columns. I display the total Working hours in columnD and only display the overtime hours if there are any. If there isn't any overtime hours then I display the week in days starting from either column E or columnG. I need to sort the days of the week according to a date range which I have just specified. So, If my Monday falls on the 19th and my Thursday falls on the 13th, I need to sort the columns so that Thursday comes first and Wednesday comes last. I don't know the column letter (could be D or E or F or G or so on) but I can determine the letter. I don't want to use the letter but when I substitute, I get an error. When I run the hardcoded substitution, it works fine "Sheets(sheetName).Range("I2:O132").Sort Key1:=Range("I2:O2"), Order1:=xlAscending, Orientation:=xlLeftToRight". When I use the following, I get an error Sheets(sheetName).Range(("""" & strColumnStart & "" & ":" & "" & strColumnEnd & """")).Sort Key1:=Range("""" & strColumnStart & "" & ":" & "" & strColumn & """"), Order1:=xlAscending, Orientation:=xlLeftToRight. When I use the immediate window, """" & strColumnStart & "" & ":" & "" & strColumnEnd & """" equates to "I2:O132" but it just doesn't work. Does anyone have a solution to this?

  10. How to Create a name manager if the is many blank cells in between cells

  11. =SUMPRODUCT(((Airport!$B$5:$B$2000)=$A$6)*((Airport!$C$5:$C$2000)=C2),(Airport!D5:D2000)) works perfect so added your formula to create a dynamic range.
    =SUMPRODUCT(((Mon_Col2)=$A$6)*((Airport!$C$5:$C$2000)=C2),(Airport!D5:D2000)) when you step in and evaluate the formula it does return the correct dynamic range. However it gives me a #N/A error when finished.

  12. HI,
    I have created a table using dynamic range. I would like to format(meaning color/borders) like a when I create a table with alternating fill colors for row. Is there a simple method?
    The button Labeled "Format as Table" creates a table them my Dynamic range goes to Spill Error...

    Thanks,
    Jmarc

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)