Dynamic Named Ranges are unquestionably one of the most important tricks for automating Excel Telecom Dashboards and recurring reports. Dynamic Named Ranges (DNR) are exactly what the name implies: an Excel named range whose size can change automatically.

It’s hard to imagine a real-world example of a recurring report which does not benefit from Dynamic Named Ranges (DNR). DNR makes many complex things simpler, and some otherwise impossible things possible. You’ll see huge benefits if you begin using DNR in your reports after reading this article.

I’ve already written many Excel Telecom Tricks. If you’ve followed along and applied these tricks, your Dashboards and reports should already have improved dramatically. You should be well along to complete Excel Automation.

This trick might be the key piece you are missing. Why do you need a DNR? Here are some typical use cases.

1. Adjust the Print Area
2. Expand Pivot Table source data
3. Allow user input to influes your models
4. Extend a chart series

In this article, I’ll illustrate the first 2 use cases to show you exactly how to create DNR. I’ve written another article automatically extending a chart series.

Follow Along

I’ve created a workbook which shows you exactly how to do what I’ve written about. I’ll refer to it a lot during this article. You can download it to follow along with the article. The workbook will help you understand what I’ve written, and also allow you to copy/paste it into your own workbooks.

I do ask for your email address so that we can keep in touch. I dislike SPAM as much as you, and promise I won’t ever send any. You can unsubscribe at any time.

Approach

You’re sure to find many uses for DNR, many different implementations. Every use of DNR I know about relies on Excel’s OFFSET() function. You provide OFFSET() a starting reference or a range, individual row and column offsets which are optionally 0, and individual row counts (height of the area) and column counts (width of the area).

The most important feature of OFFSET() is that it reads these inputs dynamically. That means if the value of any of the input arguments changes, it is immediately recognized and the impact returned by OFFSET().

Use Cases

To show you real-world examples of the benefits of DNR, I’ll describe 2 specific use cases: Auto-expanding a print area and avoiding blanks in Excel Pivot Tables.

Auto-Expand Print Area

This is a very common occurrence. It happens like this: new data is imported into a report, and the size of the area to print changes. It happens all the time, right?

I first used this technique as part of an annual budget cycle. If you’ve ever participated in budget planning, you know how iterative it is. You go over everything again and again and again, each time re-synching all your data then printing new pages for the budget team to review. Even worse, as the deadline to complete the budget draws near, each iteration happens more quickly. Manually resetting 10 to 15 print areas or more wastes a huge amount of time.

DNR solves this completely. DNR allowed me to make the changes during each iteration, and quickly revert the changes to the budget team.

By the way, Capital budgeting is so labor intensive and tool-dependent that I could write an article about it. Maybe more than one article. Please let me know in the comments if you’d like me to write an article about using Excel to help with the Capital budget.

Avoiding blanks in Pivot Tables

When creating Pivot Tables without using DNR, people often use a source data range which has many blank rows. As time goes by, adding updates to the source table use up the blank rows. This approach means you don’t have to continually change the Source Data for the Pivot table.

But it also means your Pivot Table will have blank rows. Yes, Pivot Tables allow you to hide the blank rows. But once you do this, you must manually reveal new data as it is added.

You see, the technique Pivot Tables use to hide blanks is a bit like a filter. In effect, you are explicitly telling Excel which rows to show. When new rows are added, they will not be shown unless you explicitly enable them.

I don’t like this because it is an extra manual step. And I’m always trying to minimize the number of manual steps. I want my Dashboards and reports to be automated. By using a DNR the Pivot Table displays all the data without showing the “(blank)” rows.

Another use case for DNR is the Auto-Expanding Chart Series. I like my Dashboards to show a chart of the historical performance of my Key Performance Indicators, KPI. I call it a Historical Performance Chart.

A historical performance chart shows one or more KPI all the way back to the beginning of time; as much data as is available. This can be very important to show off the team’s long-term dedication to a concept, such as Cost Containment. (Link to Least Cost Routing.) I wrote about this in my series for the Excel Project Tracker – Dynamic Named Ranges. Using a Dynamic Named Range is the only way I know to automatically expand the range of the chart SERIES() function

Downloaded Workbook -> Worksheet “Dynamic Print Area”

To define the print area automatically, we’re going to hijack the Print_Area defined on the active worksheet. In the downloaded workbook, worksheet Dynamic Print Area, I’ve entered a few rows of data from a mock CapEx budget. Each row has 3 columns, a Priority, a Project Name, and an Amount.  Then, I used those rows as source_data to create a Pivot Table.

Print Area as a Dynamic Named Range

We want Print_Area to adjust itself to precisely match the Pivot table. As always when defining a DNR, we open the Name Manager and enter an OFFSET() function. Here is the syntax for OFFSET()

=OFFSET(row offset,column offset,[height],[width])

1. The first argument is a reference, think of this as the anchor point of the function. Every other argument operates on or from this anchor point.
2. The second argument is the number of rows to offset from the anchor. The value can be 0.
3. The third argument is the number of columns to offset from the anchor. The value can be 0.
4. The fourth argument is optional, the height, in rows, of the range returned by the OFFSET() function.
5. The fifth argument is optional, the width, in columns, of the range returned by the OFFSET() function.

I usually pick the cell in the upper left of the area as the reference. I also generally use 0 as my row and column offset value because this is simplest. We want the 4th argument, height, to change automatically when the number of rows to print changes. The 5th argument is 2 because there are 2 columns in the table, and this won’t change in this example.

The Height Argument

To determine the number of rows in the table, we’ll use the COUNTIF() function. Here is the syntax:

=COUNTIF(range,criteria)

Since column I contains the Amount field, let’s use this as the range to count. I’ll start at row 4, the first row having an Amount, and include at least as many rows as the source data. The range I’ve chosen is I4:I99.

I’ll use “>0” as the criteria to test for any non-zero value in my range.

I also need to add 3 to the result returned by COUNTIF() to include the 3 header rows of the Pivot Table.  This is an important step that is easy to forget.

The Whole Enchilada

Here is the complete OFFSET() formula:

=OFFSET('Dynamic Print Area'!$H$1,0,0,3+COUNTIF('Dynamic Print Area'!I4:I99,">0"),2)

Open the Name Manager, Formulas -> Define Name -> Define Name…, enter Print_Area where it says “Enter a name …”, then enter the OFFSET() formula above where it says “Select the range of cells:”. Then click the “+” sign in the lower left of the Name Manager to add this named range.

If you select the name in the name manager, then click inside the “Select the range of cells” box, the selected range should be surrounded by a dashed line in the worksheet.  You can see this dashed line around the Pivot Table in the image.

You also can see the 3 header rows in the Pivot Table, 2 highlighted blue and 1 highlighted white, for which I added 3 to the COUNTIF() function.

Now the print area will expand and contract as you add or remove data rows from your budget, or change the Priority assigned to those budget items.

Avoiding blanks in Pivot Tables

Often people will define a Pivot Table by selecting all the rows of a table, then calling up the Pivot Table wizard. If the source data will be updated frequently, people sometimes include additional rows which presently have no data. This avoids having to change the Pivot Table data source every time data is added to the table. But it also causes your Pivot Table to display a “(blank)” row and column.
I’ll show you. First, select all rows of the data table plus a few additional rows, then click Insert -> Pivot Table. Drag the Project Name field to the Rows area, and the Amount field to the Values area. Then click OK.

The Pivot Table displays. Notice the Pivot Table shows a “(blank)” column and a “(blank)” row. These blanks display because the source data has blank rows.

There are situations where these blanks can cause trouble. Excel gives you the ability to hide the blank rows and columns by right-clicking and selecting “Hide…”. But this works by using a filter, and once a filter has been set, new Pivot Table entries must be manually enabled. I’d rather avoid any solution which requires manual intervention.

Avoid Blanks with a Dynamic Named Range

To define a DNR for a Pivot Table data source, open the Name Manager, Formulas -> Define Name -> Define Name… Enter the name you want. I’ve called it “Source_Data”. In the “range of cells” box, enter this OFFSET() function.

=OFFSET('Dynamic Print Area'!$B$1,0,0,COUNTA('Dynamic Print Area'!$B$B),3)

This is simpler than the Print_Area DNR because the data is all contiguous, no gap like in the Pivot Table Header. So no need to add rows to the result returned by COUNTA(). By using the COUNTA() function in the height argument, all non-blank cells in the column are counted, both text and numbers.

Lastly, insert a new Pivot Table.  Enter the name of this DNR, “Source_Data”, and the data source, and allow the Pivot Table to show on a new worksheet. Click OK.

See? The Pivot Table displays no blanks cells. You can verify that this works the way you want by adding a row of data to the source table, then refresh the Pivot Table. The new data should appear.

I should note that using the Excel Tables feature can also avoid

Conclusion

The OFFSET() function plays a critical role in creating Dynamic Named Ranges. It specifies the starting point, the row and column offsets, height in rows, and width in columns of the returned range. Lastly but most importantly, it automatically detects changes among its arguments.

In creating 2 different Dynamic Named Ranges, I used both COUNTIF() and COUNTA() functions to determine the number of rows in the area. Which function is better?

I like to use COUNTIF() when the column contains numbers only. Otherwise, COUNTA(), requiring only a single argument, is simpler. But it’s up to you. Use the one that works best for you, in your situation

The biggest challenge you’re likely to face is getting correct the total area selected by the DNR. To get this right, remember the trick of opening the name manager, selecting the DNR you are working on, then click once inside the input box labeled “select the range of cells:”. Excel will highlight the area defined by your DNR. Play around with different data configurations and different heights and widths in the OFFSET() function until it works just the way you want.

Once again, here is the link to download the workbook.

If you’d like to see more of my Telecoms Tools and Career-Building tips, connect with me on LinkedIn

Russell Lundberg

or follow me on Twitter

TelecomVoices