How to Create Dynamic Range Names for Automatically Expanding Menus

Excel

Range names are words you assign to cell references or formulas. You can use them instead of a cell reference or formula to make your worksheets more understandable and to give you added power. They are one of the powerful Excel features that separate the novice user from power users. And dynamic range names, names that adjust to new data automatically, belong to the upper-level of power users.

Range names can simply refer to a cell location. For example, ApplePrice could refer to cell $B$12 containing the price of apples. But, range names can also refer to a custom formula that you’ve created, that formula could calculate the size and shape as chart data or databases change.

Imagine, for example, having charts that automatically expand to include new data. Imagine having charts that animate. Imagine having drop-down lists that automatically expand to include new menu items. That’s all possible with range names and formulas.

Create an Automatically Expanding Menu using a Dynamic Range Name

The article How to Create a Dynamic Chart Using a Data Validation List used a data validation list to select the row of data to be charted. Another article How to Create a Dynamic Chart Using a Drop-Down List uses a list for the menu items in the drop-down list. Both the data validation list and the drop-down list can use dynamic range names to create automatically expanding lists.

In the following example we’ll use the Data Validation list with a dynamic range name, but remember you can use the dynamic range name anywhere you need a cell reference that updates automatically.

The article, How to Create a Dynamic Chart Using a Data Validation List, used a Data Validation list in cell C16 that referenced the menu items in $C$21:$C$24 as shown in this figure. If you add another row of regional data below the existing data it doesn’t automatically add to the data validation list. With a dynamic range name the Data Validation list automatically expands as more data is added into the rows 25 and below.

Use a Data Validation list to select data for a dynamic chart.

Use a Data Validation list to select data for a dynamic chart.

Creating a Dynamic Range that Expands to Include New Menu Items

To make this automatically expanding list you need to create a range name that refers to the cells in $C$21:$C$24, but instead of referring to this cell reference you will use a range name that refers to a formula. That formula calculates the cell reference so that as data is added the formula calculates the new size of the range.

The valuable function that Excel developers use to create automatically adjusting range names is the OFFSET function. The parameters for OFFSET are,

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

In the worksheet example above the formula to calculate an expanding range of menu items is,

=OFFSET(‘Sheetname!’$C$21,0,0,COUNTA(‘Sheetname!’$C$21:$C$30),1)

The reference is ‘Sheetname!’$C$21 to specify the reference of the top left corner of the range containing menu items. The rows and columns are 0 because we do not want the top left corner of the range to be offset from the reference point in C21.

The second powerful formula is to use COUNTA to calculate the [height] of the menu range. This adjusts the height of the range as new data is added in the rows below. COUNTA counts the number of cells in a range that contain alphanumeric contents. Use it to find out how many cells in the maximum menu range actually contain a menu item. In this case the formula uses

COUNTA(‘Sheetname!’$C$21:$C$30)

This counts how many cells in C21:C30 contain alphanumeric contents. That tells OFFSET how tall the range will be. Add another menu item below West for example, and the range expands by one.

BE CAREFUL

COUNTA counts the contents in C21:C30 and doesn’t care whether the filled cells are contiguous or touching each other. If you add a menu item in C26 and leave a blank in C25 then the range and menu will expand, but the menu will show the blank C25 at the bottom.

The final piece is to specify the [width] of the menu range as 1. That’s because the range containing the menu list is only one column wide.

So that’s the explanation. Here’s how to enter it in Excel.

Entering a Dynamic Range Name

There are many ways to create range names, but there’s only one way to create a dynamic range name.

1. On the Formulas tab, in the Defined Name group, select the Name Manager tool to display the Name Manager dialog box.

2. Click New to open the New Name box to create a new range name.

3. Enter the range name mnuRegions in the Name box. mnuRegions uses the prefix mnu to make it easy to remember that this range name is used by a menu and Regions describes the menu contents. (I’ve had complex workbooks with well over one hundred names so having a range naming convention to group and identify your range names is an important time saver.)

4. In the Refers to box enter the OFFSET formula that will calculate the dynamic range name. As you enter this formula it’s easiest to type the text and drag across the ranges. To enter a reference by dragging, click the cell reference icon on the corner and drag across the cells in the worksheet. The formula you want to enter is,

=OFFSET(‘sheetname’!$C$21,0,0,COUNTA(‘sheetname’!$C$21:$C$30),1)

5. Click Ok, then Close.

Create an automatically expanding range name using OFFSET and COUNTA.

Create an automatically expanding range name using OFFSET and COUNTA.

IMPORTANT EDITING TIP

If you aren’t familiar with editing cell references in a dialog box you could be going crazy at this point. While the cursor is in the Refers To box any arrow key or mouse selection appears as a cell reference. The Refers To box is just like the formula bar! Cell references change as you move or click. If you want to edit, press the F2-Edit key, then use the left or right arrows and edit keys.

How to See What a Dynamic Range Name Refers To

When you want to see what cell range the range name refers to press the F5, Go To, key. In the Reference box type the range name mnuRegions and press Ok. If you’ve entered it correctly the calculated range will be selected on the worksheet.

Try adding an item in C25 and repeating. Did the range expand?

Using the Dynamic Range Name in a Data Validation List

Once you have the dynamic range name created you can use it in just about anywhere Excel expects a reference to a list. But, this time the reference will automatically expand as new data is added.

For example, using the example from the article “How to Create a Dynamic Chart Using a Data Validation List” <link> you can make the data validation list in cell C16 automatically expand. Where the Data Validation dialog box asks for a reference don’t enter a cell reference, instead type in the range name,

=mnuRegions

Use a rane name instead of cell references.

Use a rane name instead of cell references.

Using dynamic range names is a very powerful technique that can be used to create very impressive and functional charts and databases. I’ll write more on how to take advantage of it.

Now go out and be dynamic!

Related Articles

How to Create a Dynamic Chart Using a Data Validation List

How to Create a Dynamic Chart Using a Drop-Down List

 

Share the power...