ExcelUser logo Free guides and templates
Work In Progress...After nearly ten years, I'm redesigning ExcelUser.com. This is the new design. You can learn more here. Also, if you find something wrong with the site, please tell me about the problems. And thanks for your patience.--Charley Kyd

Excel Range Names

How to Create and Use Dynamic Range Names in Excel

Dynamic range names in Excel are easy to use and give your formulas more power. Here's how to set them up in your spreadsheet.


Charley Kyd is a Microsoft Excel MVP by Charley Kyd, MBA
Microsoft Excel MVP

Often, we Excel users refer to ranges that need to move or expand in future versions of our reports. For example:

  • Each month, we must report the current month, the year to date, and, perhaps, the twelve most-recent months of financial data. Next month, we must point our existing report to new data.
  • We might use the TREND function to forecast weekly sales. Next week, we must point the function at one more week of data.
  • We occasionally need an easy way to point one chart at different areas of a spreadsheet database.

Dynamic range names can meet all these requirements.

Names Have More Power Than You Might Think

Most of the time, Excel range names are just that: a name assigned to a range in an Excel worksheet. With this approach, when you choose Insert, Name, Define, and then click on a defined name, the Refers To box will show a definition that looks something like one of these definitions:

=Sheet1!$B$2
=Sheet1!$B$3:$C$6

However, Excel names aren't limited merely to cell addresses. To illustrate, you could assign the name "Test" to either of these definitions:

="Hi There!"
When you enter the formula =Test in a cell, Excel returns "Hi There!". This isn't a very useful result, but it does illustrate that you can use names to do more than reference cells in a spreadsheet.

=SQRT(Sheet1!$B$2)
Here, the name Test returns the square root of the value entered in cell B2. That is, names can contain spreadsheet functions.

However, neither of these definitions returns a reference to a range of a spreadsheet. Neither defines a dynamic range name. To do that, we need to use spreadsheet functions that return references.

Functions That Return References

In Excel, a reference points to an area of a spreadsheet. To illustrate, these are references:

=$A$1
Sheet1!$F$10:$F$11
Sheet1!$D$4,Sheet1!$E$7

In contrast, ="$A$1" is not a reference, the formula merely returns the text "$A$1".

Here's a quick way to test whether a formula returns a reference: Choose Edit, Go To (or press the F5 function key); enter the formula in the Reference box; then choose OK. If Excel selects an area of your worksheet, the formula returns a reference.

I typically use two functions to return references for dynamic range names: OFFSET and INDEX.

Using the OFFSET Function

The OFFSET function returns a reference that's offset from a specified reference. It has these arguments:

=OFFSET(reference, rows, cols, height, width)

The height and width arguments are optional. If they are excluded, the resulting reference takes the shape of the reference argument.

Suppose in the nearby spreadsheet we want to define the name MyData as:

=OFFSET(RefCell, Rows, Cols, Height, Width)

First assign the names shown in the range A1:A4 to the adjacent cells in column B. To do so, select the range A1:B4; choose Insert, Name, Create; choose Left Column; then choose OK.

Next, use Insert, Name, Define to assign the name RefCell to cell B6.

To define the MyData name, choose Insert, Name, Define; enter MyData as the Name; enter the OFFSET formula shown above into the Refers To box; then choose OK.

To test that your name is working correctly, press the F5 function key to launch the Go To dialog; enter MyData as the Reference; then choose OK. If you've defined MyData correctly, Excel should select the range B7:B9.

(Note that the Go To dialog doesn't display dynamic range names in its list box. This is why you must enter MyData manually, rather than selecting the name from a list.)

If you enter =MyData in a cell, Excel returns the #VALUE! error, because the formula is returning a multi-cell reference, rather than one cell. You would get the same result if you were to enter this formula:

=B7:B9

However, you can use MyData in any formula that expects a reference. For example, the formula...

=SUM(MyData)

...returns the value 8.

And because this is a dynamic range name, you could change the Cols value to -1 in cell B3 above, which would cause the SUM formula to return the sum of the range A7:A9.

Using the INDEX Function

Suppose that the Data1 column in this figure contains monthly performance data. As before, I assigned the labels in the range A1:A2 to the corresponding cells in column B; and I defined cell B4 as RefCell.

To display the current month's data you could define CurMonth as:

=INDEX($B$5:$B$11,Month)

Here, with Month equal to 4, the INDEX function in the CurMonth cell returns a reference to cell B8.

However, there are two practical problems with this approach. First, the INDEX function requires that we specify a full range to choose from. Therefore, when we add data to cell B12, the function no longer works.

Second, even if we could solve the first problem, we would have at least two functions to worry about: one function that returns the current month and a second that returns the year-to-date amount.

Instead, we can use INDEX in combination with OFFSET.

Combining INDEX and OFFSET

When you define a range name using OFFSET, you can use it like any other name. Specifically, you can return an INDEX of this name.

To see what I mean, you could define CurYr (the current year) as:

=OFFSET(RefCell,1,0,Month,1)

And you could define CurMonth (the current month) as:

=INDEX(CurYr, Month)

Here, the dynamic name CurYr returns the range of cells for the current year. By indexing on the last cell in the range, the Month cell, we return the value for the current month. Next month, when you enter a value in cell B9 and update the Month value in cell B1, the CurMonth value automatically will update to report cell B9. 

Using Dynamic Names in Charts

At the beginning of this article I said that you could use dynamic range names in charts. To do so, select any appropriate range, set up your chart, then modify the SERIES function in the chart.

As above, you could define RefCell as cell B4 and CurYr (the current year) as:

=OFFSET(RefCell,1,0,Month,1)

Then chart the range B5:B7 in this figure. This would produce a SERIES function something like this:

=SERIES(,,Sheet1!$B$5:$B$7,1)

Edit this formula to be:

=SERIES(,,Sheet1!CurYear,1)

Now, whenever you change the value in cell B4 above, the CurYear range changes, which changes the data displayed by the chart.

Dynamic Names for Monthly Reporting

Using dynamic names can make monthly reporting a breeze.

To illustrate, this figure shows three displays that typically would be in three sheets of one workbook.

I typically put the top display in a sheet called Control, the middle display in a sheet called Data, and the bottom display in a sheet called Report.

Often, when several reports need to use the same data, I'll set up a separate report workbook for each report. Also, of course, the Data section could have hundreds of rows of data, not merely two rows, as shown here.

Each month, you merely need to insert a new column to the left of the right border column, which is column F for the month shown. You enter the data for the new month. You add 1 to the value of the MonthNum in cell D3. Then you recalculate and print.

To set up this worksheet, first set up the middle section. Here, all values are numbers and text entered into cells; no data is returned by formula. The range names in this section are:

Acct  =Sheet1!$A$8:$A$11
Data  =Sheet1!$B$8:$F$11
Dates  =Sheet1!$B$7:$F$7

For the top section, use Insert, Name, Create to assign the labels in the range C1:C4 as names for the adjacent cells in column D. Cells D1 and D2 have the values shown. The other two cells use the MATCH function in these formulas:

D3:   =MATCH(Month,Dates,0)-1
D4:   =MATCH(StartMo,Dates,0)-1

Next, define two dynamic range names for the workbook:

CurYrData   =OFFSET(Data,0,StartMoNum,,MonthNum-StartMoNum+1)
CurMoData  =INDEX(CurYrData,,MonthNum-StartMoNum+1)

Notice that both the OFFSET and INDEX functions have missing arguments. In the OFFSET function, the missing height argument causes Excel to use the height of the original Data range reference. Similarly, in the INDEX function, the missing row argument causes Excel to return all rows in the CurYrData range.

Finally, in the simple report, cell D13 contains the formula for the current month:

=TEXT(Month,"mmm yyyy")

Cell A15 contains the code to report from the Data range. Cell B15 contains a formula that returns the row number of that code in the data:

=MATCH($A15,Acct,0)

Cell C15 contains a formula to return data from the appropriate row number of the current month's data:

=INDEX(CurMoData,$B15,)

Cell D15 contains a formula to return the year-to-date total for the specified account number:

=SUM(INDEX(CurYrData,$B15,))

Copy the formulas in the range B15:D15 to the range B16:D16.

I've only touched on the power that dynamic range names offer. Why don't you see what you can discover?

 




Dashboard Reporting With Excel


Charley's SwipeFile charts