Additionally, since a named range does not change when a formula is copied to other cells, it provides an alternative to using absolute cell references in formulas. There are three methods for defining a name in Excel: using the name box, the new name dialog box, or the name manager. This article has instructions for the name box and the name. Or, you can activate the worksheet, and then use method 7 above to select the named range: Sheets ('Sheet1').Activate Range ('Test').Select. How to Select a Named Range on a Worksheet in a Different Workbook. To select the named range 'Test' on a worksheet in a different workbook, you can use the following example.

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.

• Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
• More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
• Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
• Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
• Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
• Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment..
• More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

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.

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:

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

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:

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:

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.

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:

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

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

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

Mac Shareware is an online mac download destination for macintosh users. Mac shareware, freeware, games download updated and categorized daily. Shareware eks-9 for mac free. EKS-9 1.0 for Mac is available as a free download on our software library. The default filenames for the program's installer are eksprodemo.dmg or EKS-9 Demo.dmg etc. Our antivirus analysis shows that this Mac download is clean. The most popular version of the tool is 1.0. InTone Guitar for MAC 1.0.9 is created to be a beneficial audio application for live performance at home, on stage, or in the studio. It is featuring a broad range of built-in guitar effects controllable by MIDI pedals and together with the ability.

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:

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:

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])
• =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.

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

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:

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 installedKutools for Excel, with itsNavigation 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:

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails..
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range..
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns.. Prevent Duplicate Cells; Compare Ranges..
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select..
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more..
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments..
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic..
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF..
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

• To post as a guest, your comment is unpublished.
  really, really not helpful
• To post as a guest, your comment is unpublished.
  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.
  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.
  Thanks for good article

What is dynamic named range in Microsoft Excel?

A dynamic named range helps to automatically fetch the newly entered value in the list of defined range name in Excel.

How to create dynamic range in Microsoft Excel?

To create dynamic named range, we have to enter formula for the range which will be a combination of COUNTA and OFFSET functions.

Let’s take an example and understand:-

We have number list in Column A. Select the range of A1:A6 and name it Revenue in the Name Box. Calculate the Average in cell C1.

Now we need when we add any number in column A, Excel update the Average automatically.

Follow below given steps:-

• Go to formula tab and click on Name Manager under the Defined names group.

• Name Manager Dialog box will appear.

• Click on Edit button, Edit Name dialog box will appear.

• Enter the formula in “Refers to” box =OFFSET($A$1,0,0,COUNTA($A:$A),1).

• Click on ok.
• Click on Close to Name Manager Dialog box.

Now when you will update the value in column A, then Excel will update the Average function automatically.

Formula Explanation:-

COUNTA function calculates the available number of values in the range that are not blank, so that whenever we add any value in the range, COUNTA function gives the result according to the values available in the range. It will provide the number for height.

OFFSET function takes 5 arguments, `=OFFSET(reference,rows,cols,height,width) for reference, we have defined $A$1, rows:- 0, columns:- 0, height:- 6 and width:- 1.

This is all about we can create the dynamic named range in Microsoft Excel.

If you liked our blogs, share it with your friends on Facebook. Stereo mix for mac boot camp free. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com