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 newly added 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:
- On the Formula tab, in the Defined Names group, click Define Name. Or, press Ctrl + F3 to open the Excel Name Manger, and click the New… button.
- 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.
- Click OK. Done!
In the following screenshot, we define a dynamic named range items that accommodates all cells with data in column A, except for the header row:
OFFSET formula to define an Excel dynamic named range
The generic formula to make a dynamic named range in Excel is as follows:
OFFSET(first_cell, 0, 0, COUNTA(column), 1)
Where:
- first_cell - the first item to be included in the named range, for example $A$2.
- column - an absolute reference to the column like $A:$A.
At the core of this formula, you use the COUNTA function to get the number of non-blank cells in the column of interest. That number goes directly to the height argument of the OFFSET(reference, rows, cols, [height], [width]) function telling it how many rows to return.
Beyond that, it's an ordinary Offset formula, where:
- reference is the starting point from which you base the offset (first_cell).
- rows and cols are both 0, since there are no columns or rows to offset.
- width is equal to 1 column.
For example, to build a dynamic named range for column A in Sheet3, beginning in cell A2, we use this formula:
=OFFSET(Sheet3!$A$2, 0, 0, COUNTA(Sheet3!$A:$A), 1)
Note. If you are defining a dynamic range in the current worksheet, you do not need to include the sheet name in the references, Excel will do it for you automatically. If you are building a range for some other sheet, prefix the cell or range reference with the sheet's name followed by the exclamation point (like in the formula example above).
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 (:), you put the hard-coded starting reference like $A$2.
- On the right side, you use the INDEX(array, row_num, [column_num]) 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-entry cells in column A).
For our sample dataset (please see the screenshot above), 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)
If all done properly, the result of the formula will change once you add or remove items to/from the list:
Note. The two formulas discussed above produce the same result, however there is a difference in performance you should be aware of. OFFSET is a volatile function that recalculates with every change to a sheet. On powerful modern machines and reasonably sized data sets, this should not be a problem. On low-capacity machines and large data sets, this may slow down your Excel. In that case, you'd better use the INDEX formula to create a dynamic named range.
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 2016 - 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))
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 bellow, 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?
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))
…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 Excel will insert it in the formula right away:
The completed formula looks as follows:
=INDEX(Return_range, MATCH(Lookup_value, Lookup_range, 0))
And works perfectly!
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 Excel Dynamic Named Range Workbook. I thank you for reading and hope to see you on our blog next week!
You may also be interested in
- Excel names and named ranges
- Microsoft Excel formulas with examples
FAQs
How do you dynamically reference a named range? ›
INDEX formula to make a dynamic named range in Excel
On the right side, you use the INDEX(array, row_num, [column_num]) 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-entry cells in column A).
To create an Excel dynamic reference to any of the above named ranges, just enter its name in some cell, say G1, and refer to that cell from an Indirect formula =INDIRECT(G1) .
How to create defined names for a range using the create from selection option in Excel? ›- Select the range you want to name, including the row or column labels.
- Click Formulas > Create from Selection.
- In the Create Names from Selection dialog box, select the checkbox (es) depending on the location of your row/column header. ...
- Click OK.
Perhaps what is most notable about named ranges is that they allow you to construct formulas before adding the data. When you are designing your worksheet, you can create formulas using names instead of traditional cell references, and then define the names for the corresponding ranges as data becomes available.
How do you create defined names based on the values in the range? ›Define names from a selected range
Select Formulas > Create from Selection. In the Create Names from Selection dialog box, designate the location that contains the labels by selecting the Top row,Left column, Bottom row, or Right column check box. Select OK.
A dynamic array formula is entered in one cell and completed with a regular Enter keystroke. To complete an old-fashioned array formula, you need to press Ctrl + Shift + Enter. New array formulas spill to many cells automatically. CSE formulas must be copied to a range of cells to return multiple results.
How do you make a dynamic range formula? ›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.
How do I create a dropdown from a named range in Excel? ›When you select the cells of a named range, you'll see the name in the name box. Now, you are ready to create a drop-down list that uses the named range. Select the cell where you want a drop-down list. Click Data Validation, select List, click in Source, press F3, select the name, click OK, and click OK again.
How do I create a unique list in Excel based on criteria? ›Get a list of unique values based on criteria. To extract unique values with condition, use the Excel UNIQUE and FILTER functions together: The FILTER function limits the data only to values that meet the condition. The UNIQUE function removes duplicates from the filtered list.
How do you create a scenario with changing cells? ›- Select the cells that contain values that could change.
- Click the Data tab on the ribbon.
- Click the What-If Analysis button.
- Select Scenario Manager. ...
- Click the Add button to add a new scenario.
- Type a name for the new scenario. ...
- Click OK. ...
- Update any values you want to see for the given scenario.
What are the disadvantages of name ranges in Excel? ›
So, what are the disadvantages of name ranges? Well, at first, if you don't use them often, they can seem just a little bit fiddly. The second disadvantage of name ranges is that they don't lend themselves to copying relative references. However, there are some workarounds for this when we are creating name ranges.
Do dynamic ranges slow down Excel? ›The only problem is that named ranges, especially dynamic ranges, consume a considerable amount of working memory. Overusing them may slow down your Excel spreadsheet.
What is not allowed in range names? ›The name string must begin with a letter or underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters. Spaces are not allowed, but two words can be joined with an underscore (_) or period (.).
Which of the following are ways that you can Create a named range? ›Type a name in the Name Box
The Name Box in Excel is fastest way to create a named range: Select a cell or a range of cells that you want to name. Type a name into the Name Box. Press the Enter key.
Set Multiple Named Ranges at the Same Time
The simple solution to this is to use a built-in feature by Excel called “Create from Selection” under the Formulas tab within the Named Ranges group. You'll see that the named ranges have been applied to the values in the table.
Regarding a named range, the scope of a name is the location within which Excel recognizes the name without qualification. Excel recognizes a construct like 3+4= as a legitimate formula. Range names may begin with the caret (^) character. You cannot use a named range in a formula that references another worksheet.
Are dynamic arrays faster in Excel? ›The dynamic array is going to be all in RAM or swap file. So, yes, I expect it will use more RAM when calculating than the separate cell formulas. Since the dynamic array is mostly in RAM, I expect it will calculate faster. The hardcoded formulas will have to all be loaded to RAM to be calculated.
How do you add values to a dynamic array? ›To add an element in a dynamic array, we use the add() function. Explanation: We have created an ArrayList i.e., a dynamic array and added elements to it. We can see that after the elements are added, the size of the dynamic array increases.
How do you add data to an array dynamically? ›- Convert the array to ArrayList object.
- Add the required element to the array list.
- Convert the Array list to array.
A high-contrast image is one with both dark shadows and bright highlights. The dynamic range of such an image can exceed your camera's ability to capture it, which is why a photo of a person standing in front of a light-filled window fails to catch the details of the person's face as well as the scene outside.
How can we provide a dynamic range in the data? ›
To provide a dynamic range in “Data Source” of Pivot tables, first create a named range using offset function and base the pivot table using a named range created in the first step.
How do you set dynamic value? ›You can usually set a dynamic value by right-clicking a property and clicking “Use dynamic value”. This will bring up the data picker, which shows you all the data available in the current component or page.
How do I auto populate data in Excel based on another cell? ›- Select one or more cells you want to use as a basis for filling additional cells. For a series like 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. ...
- Drag the fill handle .
- If needed, click Auto Fill Options. and choose the option you want.
- We can use OFFSET function to make dynamic data validation list.
- Press ALT + D + L.
- From Settings tab; click on Allow.
- In Source box, enter the following formula.
- =OFFSET($A$2,,,COUNTA($A:$A)-1)
- Stay in the Settings tab;
- Select List in the Allow drop-down list;
- Enter the following formula in the Source box; =IF($E$2=$B$2,$B$3:$B$6,$C$3:$C$6) Copy.
- Click the OK button. See screenshot:
You can use the combination of the SUM and COUNTIF functions to count unique values in Excel. The syntax for this combined formula is = SUM(IF(1/COUNTIF(data, data)=1,1,0)). Here the COUNTIF formula counts the number of times each value in the range appears. The resulting array looks like {1;2;1;1;1;1}.
How do you populate Excel sheet from another based on criteria? ›- Go to Sheet2 (see the steps on the video above)
- Select a cell in an unused part of the sheet (cell C4 in this example).
- On the Excel Ribbon's Data tab, click Advanced.
- Choose Copy to another location.
- Click in the List Range box.
Scenarios and Data tables take sets of input values and determine possible results. A Data Table works with only one or two variables, but it can accept many different values for those variables. A Scenario can have multiple variables, but it can only accommodate up to 32 values.
What is the difference between scenario and multiple operations? ›Like scenarios, Data > Multiple Operations is a planning tool for “what if” questions. Unlike a scenario, the Multiple Operations tool does not present the alternate versions in the same cells or with a drop-down list. ... It is created by entering a list of alternative values for one or two of the original values.
How do you create a Solver answer report? ›To create a report that is based on your solution after Solver finds a solution, you can click a report type in the Reports box and then click OK. The report is created on a new worksheet in your workbook. If Solver doesn't find a solution, only certain reports or no reports are available.
What is a benefit of using a range name in Excel group of answer choices? ›
What advantage do you have when using a named range in a formula? Easier to remember than a cell reference. The formula can be used anywhere on the worksheet.
What is dynamic range good for? ›Essentially, dynamic range is how far the brightest spots in your image can vary from the darkest spots in your image and still maintain proper details in a single exposure. The more you have, the more you can capture scenes with a great contrast between lights and darks.
What slows down Excel the most? ›Too big spreadsheets in Excel can slow down the Excel speed because big Excel spreadsheets also take up large space on your system. You also know that you need enough free storage space on your system so that you can do your task on the system smoothly and efficiently.
How do I optimize Excel performance? ›- Avoid Volatile Formulas. ...
- Use Helper Columns. ...
- Avoid Array Formulas. ...
- Use Conditional Formatting with Caution. ...
- Use Excel Tables and Named Ranges. ...
- Convert Unused Formulas to Static Values. ...
- Keep All Referenced Data in One Sheet. ...
- Avoid Using the Entire Row/Column as Reference (A:A)
You can use a named range instead of a cell address in a formula. Answer: The statement is true. MS Excel allows us to assign names to cells in a worksheet and use it for quickly locating specific cells by entering the names. This could be useful when working with large spreadsheets.
What is the advantage of name range? ›Conclusion: naming ranges in Excel has many advantages, making your workbooks easier to read, making formulas faster to type and debug, and allowing to create ranges with variable-size to make your spreadsheets dynamic and easy to update.
Why use named ranges? ›A named range is one or more cells that have been given a name. Using named ranges can make formulas easier to read and understand. They also provide simple navigation via the Name Box. Note: named ranges are absolute references by default.
How do you reference a named range in VBA? ›Refer to this named range by just the name in the same worksheet, but from another worksheet you must use the worksheet name including "!" the name of the range (example: the range "Name" "=Sheet1! Name").
How to use range dynamically in VBA? ›- Method-1: Select Cells Containing Values Through UsedRange Property.
- Method-2: Choose Dynamic Range Including Headers with CurrentRegion Property.
- Method-3: SpecialCells for Selecting Dynamic Range Excluding Headers.
- Method-4: Change Color for Last Used Row with Excel VBA.
Go to Data > Data validation. On the sidebar, click “Add a rule”. Select “Dropdown (from a range)” under 'Criteria'. In the range selection box, type the name of your range and press 'Enter'.
What is a dynamic named range index? ›
Dynamic named ranges automatically expand and contract when data is added or removed. They are an alternative to using an Excel Table, which also resizes as data is added or removed. The INDEX function returns the value at a given position in a range or array.
Can you use a cell to reference a named range? ›A named range can refer to a single cell, an area of cells or cells that are scattered throughout a worksheet. Named ranges can then be used in place of cryptic cell references in formulas and VBA code.
Can you use Vlookup with named ranges? ›Named Ranges with Vlookup Formula
Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array. A Named Range makes it easier to understand Excel formulas, especially if the said formula contains an array argument.
- On the Ribbon, click the Formulas tab.
- Click Name Manager.
- In the list, click on the name that you want to change.
- In the Refers To box, change the range reference, or drag on the worksheet, to select the new range.
- Click the check mark, to save the change.
Therefore, we need to use the “Set” keyword to set the reference. The variable “Rng” refers to the range of cells from A2 to B10. So, instead of writing “Range(“A2:B10″))” every time, we can write the word “Rng.” In the next line, mention the variable name “Rng” and put a dot to see the magic.
How do you use a range or a cell as a variable in VBA? ›To use a range or a single cell as a variable, first, you need to declare that variable with the range data type. Once you do that you need to specify a range of a cell to that variable using the range object. This also gives you access to all the properties and methods that you can use with a range.
Can I use named range in conditional formatting? ›Conditional Formatting Rules can be applied to ranges referenced by Name, but as soon as the rule is applied, that Name is replaced with an absolute reference to the range.
How do I get Excel to recognize a named range? ›Regarding a named range, the scope of a name is the location within which Excel recognizes the name without qualification. Excel recognizes a construct like 3+4= as a legitimate formula. Range names may begin with the caret (^) character. You cannot use a named range in a formula that references another worksheet.
What are the two types of dynamic range? ›The 2 Types of Dynamic Range
One is the dynamic range of the subject, the second is the dynamic range of your camera. The dynamic range of the subject is a measure of the range of light intensities from the shadows to the highlights.
Summary. 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.
What is a good dynamic range? ›
For most tracks, it's best to shoot for a Loudness Range of -4 to -8 LU and a Dynamic Range above 6DR. This ensures that your mix will be loud enough to hear clearly on any system and dynamic enough to prevent unwanted audio distortion. However, these ranges can vary depending on the genre.