Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to create dynamic named range in Excel?

Normally, Named Ranges are very useful for Excel users, you can define a series of values in a column, give that column a name, and then you can refer to that range by name instead of its cell references. But most time, you need to add new data to expand the data values of your referred range in future. In this case, you have to go back to Formulas > Name Manager and redefine the range to include the new value. To avoid this, you can create a dynamic named range which means you don´t need to adjust cell references every time when you add a new row or column to the list.

Create dynamic named range in Excel by creating a table

Create dynamic named range in Excel with Function

Create dynamic named range in Excel with VBA code

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial


arrow blue right bubble Create dynamic named range in Excel by creating a table


If you are using Excel 2007 or later versions, the easiest way to create a dynamic named range is to create a named Excel table.

Let’s say, you have a range of following data which need to become dynamic named range.

doc-dynamic-range1

1. Firstly, I will define range names for this range. Select the range A1:A6 and enter the name Date into the Name Box, then press Enter key. To define a name for range B1:B6 as Saleprice with the same way. At the same time, I create a formula =sum(Saleprice) in a blank cell, see screenshot:

doc-dynamic-range2

2. Select the range and click Insert > Table, see screenshot:

doc-dynamic-range3

3. In the Create Table prompt box, check My table has headers (if the range do not have headers, uncheck it), click OK button, and the range data has been converted to table. See screenshots:

doc-dynamic-range4 -2 doc-dynamic-range5

4. And when you enter new values after the data, the named range will automatically adjust and the created formula will be also changed as well. See following screenshots:

doc-dynamic-range6 -2 doc-dynamic-range7

Notes:

1. Your new entering data must be adjacent to the above data, it means there is no blank rows or columns between the new data and the existing data.

2. In the table, you are able to insert data between the existing values.


arrow blue right bubble Create dynamic named range in Excel with Function

In Excel 2003 or earlier version, the first method will not be available, so here is another way for you. The following OFFSET( ) function can do this favor for you ,but it is somewhat troublesome. Supposing I have a range of data which contains the range names that I have defined, for example, A1: A6 the range name is Date, and B1:B6 range name is Saleprice, at the same time, I create a formula for the Saleprice. See screenshot:

doc-dynamic-range2

You can change the range names to dynamic range names with following steps:

1. Go to click Formulas > Name Manager, see screenshot:

doc-dynamic-range8

2. In the Name Manager dialog box, select the item that you want to use, and click Edit button.

doc-dynamic-range9

3. In the popped out Edit Name dialog, enter this formula =OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1) into the Refers to text box, see screenshot:

doc-dynamic-range10

4. Then click OK, and then repeat the step2 and step3 to copy this formula =OFFSET(Sheet1!$B$1, 0, 0, COUNTA($B:$B), 1) into the Refers to text box for the Saleprice range name.

5. And the dynamic named ranges has been created. When you enter new values after the data, the named range will automatically adjust and the created formula will also be changed as well. See screenshots:

doc-dynamic-range6 -2 doc-dynamic-range7

Note: If there are blank cells in the middle of your range, the result of your formula will be wrong. That’s because the non-blank cells are not counted, so your range will be shorter than it should, and the last cells in the range will be left off.

Tip: explanation for this formula:

  • =OFFSET(reference,rows,cols,[height],[width])
  • -1
  • =OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1)
  • reference corresponds to the starting cell position, in this example Sheet1!$A$1;
  • row refers to the number of rows you are going to move downwards, relative to the starting cell (or upwards, if you use a negative value.), in this example, 0 indicates the list will start from the first row down
  • column corresponds to the number of columns you will move to the right, relative to the starting cell (or to the left, using a negative value.), in the above example formula, 0 indicates expand 0 columns to the right.
  • [height] corresponds to the height (or number of rows) of the range starting at the adjusted position. $A:$A, it will count all the items entered in column A.
  • [width] corresponds to the width (or number of columns) of the range starting at the adjusted position. In the above formula, the list will be 1 column wide.

You can change these arguments to your need.


arrow blue right bubble Create dynamic named range in Excel with VBA code

If you have multiple columns, you could repeat and enter individual formula for all the remaining columns, but that would be a long, repetitive process. For making things easier, you can use a code to create the dynamic named range automatically.

1. Activate your worksheet.

2. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following code in the Module Window.

Vba code: create dynamic named range

Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
    myName = Replace(Cells(Rowno, i).Value, " ", "_")
    If myName <> "" Then
        wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
    End If
Next
End Sub

4. Then press F5 key to run the code, and there will be generated some dynamic named ranges which are named with the first row values and it also creates a dynamic range called MyData which covers the whole data.

5. When you enter new values after the rows or columns, the range will be expanded as well. See screenshots:

doc-dynamic-range12
-1
doc-dynamic-range13

Notes:

1. With this code, the range names are not displayed in the Name Box, in order to view and use the range names conveniently, I have installed Kutools for Excel, with its Navigation Pane, the created dynamic range names are listed.

2. With this code, the whole range of the data can be expanded vertically or horizontally, but to remember there shouldn’t have blank rows or columns between the data when you enter new values.

3. When you use this code, your data range should be start at cell A1.


Related article:

How to auto update a chart after entering new data in Excel?


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    loyiso · 3 years ago
    please help i am trying to create a dynamic named range on excel 2016 typing "=offset(DATAENTRY!$B$6,,,counta(DATAENTRY!$B$6:$B$13))" but still it gives me an error saying it is not a formula.
  • To post as a guest, your comment is unpublished.
    marge · 3 years ago
    You are a very good teacher: 1) step-by-step approach; 2) you do not bore the student with obvious material or conclusions; 3) yet you include all necessary material. I look forward to more tutorials from you.
  • To post as a guest, your comment is unpublished.
    Iran · 5 years ago
    Thanks for good article