Excel Dynamic Named Range [4 Ways]

Excel Dynamic Named Range is one of the Excel features that not so many Excel users know about. This article will make Dynamic Named Ranges closer to you. In the further lines, hints and tricks related to Excel Dynamic Named Range will be shown.

Dynamic Named Range with Excel’s OFFSET Function

In order to make a dynamic range, Offset function can be used.

Excel’s Offset Function

Here is the syntax of Excel’s OFFSET function:

OFFSET (reference, rows, cols, [height], [width])

Offset Function, Excel, Syntax

What the arguments mean in the function:

  1. Reference – Reference from which you want to base the offset.
  2. Rows – Number of rows, up or down from the reference cell.
  3. Cols – Number of columns, right or left from the reference cell.
  4. Height, width – Define the height and width of the selection, from the referenced cell.

Dynamic Named Range with OFFSET Function

In the Name Manager dialog box, it can be used for defining Dynamic Named Ranges.

Here is how you can do that:

Go to the Formulas tab, in the Defined Names group of commands, click on the Define name command, and you should get the New Name dialog box like this:

New Name, Dialog Box

A Dynamic Named Range will be created to select all a range of cells that starts one cell lower than A1, in the same column, and selects the number of columns and rows given in K1 and K2 cells, from the example of the image below:

Dynamic Named Range, Excel

The formula in the “Refers to” field looks like this:

=OFFSET(Sheet1!$A$1;1;0;Sheet1!$K$1;Sheet1!$K$2)

All the elements in OFFSET function can be made dynamic. For example, I would like to select a number of dates for some specific day.

Here are some outputs:

=OFFSET (Sheet1!$A$1; 1; 0; 6; 2) = It will refer the range A2: B7

Dynamic Named Range, Excel

The formula that will depend on entries in K1, K2, K3 and K4 should look like this:

=OFFSET(Sheet1!$A$1;Sheet1!$K$3;Sheet1!$K$4;Sheet1!$K$1;Sheet1!$K$2)

As any other function in Excel, OFFSET can be combined with other functions that provide the number as a result, too. Let’s say that I would like to select all the Tuesday dates except the one in the last week, by taking Monday as a reference. Based on the same example, the formula should look like this:

=OFFSET(Sheet1!$A$1;1;1;COUNT(Sheet1!$B:$B);1)

Dynamic Named Range with INDEX Function

Other functions can also be used for Dynamic Named Ranges, like INDEX, for example – you would like to select all the “Mondays”, no matter how many of them there are:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A;COUNTA(Sheet1!$A:$A))

If you would like to make the reference dynamic also, you can use a combination of formula. Let’s say that you want to enter a name of a day in one cell, and then to select all the dates in the table that belong to the day.

=OFFSET(INDIRECT(ADDRESS(2;MATCH(Sheet1!$K$6;Sheet1!$1:$1;0)));0;0;COUNT(Sheet1!$A:$A);1)

Read More: What is the Order & Precedence of Operations in Excel?

Dynamic Named Range with VBA

In their everyday work, professionals face situations where they need to perform the same operation over and over. The same thing might happen when creating Dynamic Named Ranges. In order to avoid repetitive tasks, VBA can be used for this, also.

VBA works exactly in the same way as formulas, just you need to follow the syntax that is characteristic for adding Named Ranges.

The syntax goes like this:

ActiveWorkbook.names.Add Name:=”NAME”, RefersTo =”RANGE THAT YOU WANT TO SELECT”

For example, one of the previous examples can be easily applied in VBA. A complete macro would look like this:

Sub naming()

ActiveWorkbook.names.Add Name:=”NAME9″, RefersTo:=”=OFFSET(Sheet1!$A$1,0,1,counta(A:A),2)”

End sub

Dynamic Named Range with Blank cells

You can often face issues when you don’t have all the cells populated, or, in other words, some of the cells are blank. The most important is to know definitions of the formula used to find a reference. For example, COUNTA is a function that counts all NON-BLANK! cells in a range. It depends if you want to include blanks or not.

The image below shows the situation with a blank cell:

Dynamic Named Range, Excel, Blank Cells

If you want to select all the “Mondays”, then using the formula like in previous examples would select every Monday but the last one, since there is a blank cell – A5. The solution for this would be using a different formula – combination of the formula:

=OFFSET(Sheet1!$A$1;1;0;SUMPRODUCT(MAX((Sheet1!$A:$A<>””)*ROW(Sheet1!$A:$A)))-1;1)

It will select all the cells from A2 to A7.

The previous lines and examples should help you to perform your work faster and more efficient. Dynamic Named Ranges represent a huge area that has many options to explore and can be a very useful tool, depending on your skills and creativity. It is shown that there are many combinations, and it is up to you to find their application.

Read More

Download Working File

Download the working file from the link below:


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

We will be happy to hear your thoughts

      Leave a reply