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. Here's a weird one for dynamic range defined in Formulas>Name Manager (Excel MAc v16.59)...
    Trying to define/name a dynamic range $L$1:$M$M from Parameters! Tab using:
    =OFFSET(Parameters!$L$1,0,0,COUNTA(Parameters!$M:$M),2)

    1) Type that into any cell in the workbook (even other Tabs) and seems to return exactly whats desired...
    2) Type that into the Name Manager to assign a name to this dynamic range and get nothing returned... no error message, just an empty set of data.

    Any ideas or possibly is there a bug in the Excel Mac?
    Thanks for any response...

    • Hello!
      Unfortunately, I was unable to reproduce your situation on Excel for Mac. I pasted your formula into the "Refers to" field and got a working named range.
      Check if you are doing everything correctly in accordance with the instructions described in the article above.

  2. I'm having trouble entering the formula for a dynamic range.
    =Sheet1!ADDRESS(MATCH(TODAY():$P:$P,0),16):INDEX($P:$P, COUNTA($P:$P))
    or
    =Sheet1!ADDRESS(MATCH(TODAY(),P10:P374,0),16):$P$374
    Keeps giving me a message about am I trying to enter a formula, must start with= or -.
    When I enter only the cell formula, it takes it, but I need a range.
    =Sheet1!ADDRESS(MATCH(TODAY():$P:$P,0),16) Works to give the cell address of today's date.
    This creates dynamic range starting at today's date in column P and I want to extend it to the end of the data in column P. Column P is a list of dates.
    I need this to then find the next value in a different column after the today's date row.
    This different than most dynamic ranges that only extend the bottom. I want to do both. One to move the top based on today's date and the bottom to extend it based on data being added.
    I hope you can help me. I've learned a lot from you.
    Thanks,
    Colt

    • Hello!
      If your list of dates starts in cell A1, then you can use the formula to create a dynamic range starting from the current date:

      =OFFSET(A1,MATCH(TODAY(),A1:A300,0)-1,0, COUNTA(A1:A300)-MATCH(TODAY(),A1:A300,0)+1,1)

      This should solve your task.

  3. Hi,
    How can I get the Defined Name to work for a dynamic range when I have both Blank and Non-Blank cells within my range?

      • I don't think this will work. The COUNTBLANK function will also count all of the blanks below the last item in the column giving you a very large value for modern versions of Excel.

        • Hi!
          Of course, you can use a range of cells instead of the entire column in the formula.

          For example - Instead of COUNTA(column) use COUNTA(A1:A100)

          This will seriously speed up the calculations.

  4. Each day I copy a watchlist of share prices from Yahoo Finance to Excel. By placing the latest end-of day closing share prices in the right-most column of a table, all the previous entries move one column to the left. (Excel does that for me).
    Every day's close-price of each company, from start date to the present, is listed in a row. The column holding the start date, is moving leftwards into another column, day by day.

    I make charts of each company. The chart includes the Start date, (when I first started taking the company's data). The chart records up to the present day.

    I know where is each start date. By using Index and Match I can find exactly in which column is the Start date. I can list the column reference in a cell:- e.g in cell Q1 is listed, column "CME".

    Today, the column holding my start date for the company, RCP.L, is CME. Yesterday, before I took the day's figures, it was CMF.

    I now want an automatic facility (formula) to enter CME14:CME770 into an INDEX & MATCH formula (below) which, yesterday was reading CMF14:CMF770:-

    So the Index Match formula for yesterday is:-
    INDEX(Close!CMF$14:CMF$770,MATCH($Q$1,Close!$ATK$14:$ATK$770,0))
    It reads yesterday's range, not today's.

    ($SQ$S1 reads the cell containing "RCP.L" to find the row in the column range CMF14:CMF770.)

    Today I want the INDEX Match formula to be
    INDEX(Close!CME$14:CME$770,MATCH($Q$1,Close!$ATK$14:$ATK$770,0))

    Tomorrow I will want CMD14:CMD770 in the formula and so-on.

    I've done masses of reading and completely stuck. Can you please help? Regards, Philip.

  5. Hello!
    How do I create a Dynamic Named Range for unlocked ranges by a password when sheet is protected.

  6. How do I create a Dynamic Named Range for unlocked range by a password?

  7. I would like to add numbers in a column
    using sum, offset, counta and the column has a name.
    For practice, I have column E and beginning with row 3 the title: salary. Rows 4, 5, 6,etc are the salaries. Column E3:E10 has been named as "monthly_salaries". I need to use that so I can place the sum(monthly_salaries) function on any worksheet, while the monthly-salaries will be on a seperate worksheet.
    Each month the length of this vertical column E changes as more or less people draw salaries. Also, I use different worksheets so data, like the salaries may be located on worksheet 2 and the actual sum on worksheet 1.
    I have offset(e3,0,0,counta(E:E),1.
    Question: How to combine the column name with the sum function to accommodate the dynamic nature of this problem.
    Thank you for your assistance.
    Chris

    • Hello!
      Create a dynamic named range “monthly_salaries” using a formula

      =OFFSET($E$3, 0, 0, COUNTA($E$3:$E$10), 1)

      Use it like this:

      =SUM(monthly_salaries)

      I hope I answered your question.

  8. Dynamic Named Ranges don't seem to like Dynamic Array Formulas it seems.
    I have 2 Dynamic Named Ranges, say LIST1 and LIST2. In a separate column, I have entered the formula: FILTER(LIST1, LIST2="an existing value"). This returns an error (#VALUE!). It worked correctly when my named ranges were not dynamic.
    Is there an evident reason why I am getting this error?

    Thanks in advance!
    Sabrina

  9. Hi, How do i use Dynamic Named Range in "Data Rane" Excel chart ?
    =$B$2:INDEX($1:$1048576,COUNTA($B:$B),COUNTA($2:$2)) or =$A$2:INDEX($A:$A, COUNTA($A:$A))
    is not suitable for this.
    Thank You,
    Ofer

  10. hi there.. hope someone could help me. I want to create a name in my file which is getting values from a single column table. I want to exclude one specific value and don't want it to be part of the name range. Not sure how can I do that? Column data is:
    Fruits (table header)
    Apple
    Banana
    Orange
    Grapes
    Want to have a list that exclude "Orange". As it's a table I will be adding new names after Grapes in near future.

    • Pls refer to: FILTER(A1:INDEX(A:A,COUNTA(A:A)),A1:INDEX(A:A,COUNTA(A:A))"Orange") in the New Name dialog

  11. Hello, I am painfully new to working with Excel, macros, and the like. I have recorded a macro, but right now it will only work on the cell range that I originally recorded the macro on. Right now the range reads as =Range("A2:A724"), but I need the bottom part of the range (A724) to be dependent on column B. Meaning, if column B has data going down to cell B1021, then I want my range in column A to automatically look like =Range("A2:A1021"). I would appreciate any help. Thanks!

  12. Hi! I'm a newbie in Excel, so there's a risk my question is kind os obvious. I'm trying to connect a Pivot Table in Excel to a Word File, for this, I have to edit the Name of my range in Excel and Word so the Word table automatically updates the range it is reporting. The problem I have is Excel says he doesn't understand my formula, it looks like this:

    ='DE40'!$B$4:$B$4:INDEX('DE40'!$1:$1048576,COUNTA('DE40'!$B:$B),+'DE40'!$4:$4))

    Where DE40 is the Sheet/Pivot Table I'm trying to refer. Any ideas?

  13. I have a simple table: 7 columns, 4 rows. Can I create a formula just adding Hrs of bananas?

    Jan 1 Jan 2 Jan 3
    Hrs Cases Hrs Cases Hrs Cases
    Bananas 1 10 3 30 0 0
    Apples 5 20 1 4 3 12

    • _______|___Hrs_|_Cases_|_Hrs_|_Cases_|_Hrs_|_Cases
      Bananas|____1__|___10__|__3__|___30___|_0__|___0__
      Apples_|____5__|___20__|__1__|___04___|_3__|___12_

      No sure if this will help to understand the table...

  14. An easy way to do it if you need a range is to use indirect in the name.. eg example for A2:G10
    There is 20 rows in coloumn A

    =Indirect(Sheet_name!A2:G"&CountA(A:A))

    since indirect "translate" the expression - so it reads the name to be refered to this
    Sheet_name!A2:G10

    • Correction - There is 20 rows in coloumn A - should ofcourse be 10 rows ;)

  15. I'm trying to create a dynamic range showing client names only if their status is Active in another column. Can someone help me with the formula for this?

    • Pls refer to: FILTER(A1:INDEX(A:A,COUNTA(A:A)),B1:INDEX(B:B,COUNTA(B:B))="Active") in the New Name dialog

  16. Hi,

    This index formula method for creating dynamic named range is not working.

    Sheet3!$H$2:INDEX(Sheet3!$H:$H,COUNTA(Sheet3!$H:$H))

    Using evaluate formula, this shows to result to $H$2:$H$6. But post that it acts as an array formula. Its finally returning value either H2/H3/H4/H5/H6 based on the cell.
    What i mean to say is, it is not giving a range. Hence, this seems wrong.
    Please let me know your view.

  17. Hi
    How do you create a Dynamic named range (with INDEX) but without the sheet name automatically added after the Define Name editor is closed?
    Tnks

  18. THANK YOU FOR ENLIGHTENING ME
    AM SO DAMN GRATEFUL FOR THIS WRITE UP

  19. Thank you

  20. How do I create a Dynamic Named Range with worksheets instead of rows or columns?

    I have a workbook that I add a new worksheet to every week and I want to track my vacation time over the course of the calendar year.

    Thank you for your help.

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 :)