How to use Dynamic Named Ranges in Excel

In my recent article, I talked all about named ranges in excel. While exploring named ranges, the topic of dynamic ranged popped up. So in this article, I will explain, how can you make Dynamic Range in Excel.
243

What is Dynamic Named Range in Excel?

A normal named range is static. If you define C2:C10 as Item, Item will always refer to C2:C10, until and unless you edit it manually. In below image, we are counting blanks in the Item list. It is showing 2. If it were dynamic it would have shown 0.
244
A dynamic name range is name range that expands and shrinks according to data. For example if you have a list of items in range C2:C10 and name it Items, it should expand itself to C2:C11 if you add a new item in range and should shrink if you reduce when you delete as above.

How to Create A Dynamic Name Range

Create Named Ranges Using Excel Tables

Yes, excel tables can make dynamic named ranges. They will make each column in a table named range that is highly dynamic.
But there is one drawback of table names that you can’t use them in Data Validation and Conditional Formatting. But specific Named ranges can be used there.

Use INDIRECT and COUNTA Formula

To make a name range dynamic, we can use INDIRECT and COUNTA function

. How? Lets see.

Generic Formula to be written in Refers To: section

=INDIRECT("$startingCell:$endingColumnLetter$"&COUNTA($columnLetter:$columnLetter))

Above generic formula may look complex but it is easy actually. Let’s see by an example.
The basic idea is to determine last used cell.

Dynamic Range Example

In above example we had an static name range Item in range C2:C10. Let’s make it dynamic.
245

    • Open Name Manager by Pressing CTRL+F3.
    • Now if a name already exists on the range, click on it and then click edit. Else click on New.
    • Name it Item.
    • In Refers To: Section write Below Formula.
=INDIRECT("$C2:$C$"&COUNTA($C:$C))
  • Hit OK Button.

And it's done. Now, whenever you will type Item in name box or in any formula it will refer to C2 to last used cell in the range.
246
Caution: No cell should be blank in-between range. Otherwise, the range will be reduced by the number of blank cells.

How does it work?

As I said, its only matter finding last used cell. For this example, no cells should be blank in between. Why? You’ll Know.

INDIRECT function in excel converts a text into range. =INDIRECT(“$C$2:$C$9”) will refer to absolute range $C$2:$C$10. We just need to find the last row number dynamically (9).
Since all cells have some value in range C2:C10, we can use COUNTA function to find the last row.
So,=INDIRECT("$C2:$C$"& this part fixes the starting row and column and COUNTA($C:$C) dynamical calculates last used row.

So yeah, this is how you can make the most effective Dynamic Named Ranges that will work with every formula and functionality of Excel. You don’t need to edit your named range again when you change data.

Download file:

 

Related Article:

How to use Named Ranges in Excel

17 Amazing Features of Excel Tables

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity

How to use the VLOOKUP Function in Excel

How to use the COUNTIF function in Excel

How to use the SUMIF Function in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube