We’ve never met before. But I already know a lot about you. You probably work in technology, maybe in a Mobile Network RAN. You already use Microsoft Excel, and you’d like to get better at it. You are not a manager and don’t have a team of people to supervise or manage. Lastly, you want to get more out of your career.

How do I know all these things about you? I didn’t sleep at 221B Baker Street last night, but I do read and watch a lot of Sherlock Holmes stories. Seriously, I’ve shared this article to Telecoms and Excel groups, where Telecoms professionals and people trying to improve their Excel skills hang out. So you’re probably one of them.

And what about not being a Manager, was I right about that? That was easy, it’s a trick question: when was the last time you met any manager who thought there was anything they didn’t already know? Hah!

About getting more from your career, what else should I expect from someone scouring technology groups and blogs trying to learn new skills?

This article will cover one of Excel’s most useful and most difficult topics: Dynamic Named Ranges. I’ve talked about Named Ranges before, how they make your formulas easier to read and to update. Dynamic Named Ranges give you a way to automatically re-size ranges as your dataset changes. Two places where this is often done is with Charts and with Print Areas. In this article, I’ll show you have a Dynamic Named Range can automate the data displayed in your charts. The result is one less step whenever you update the Project Tracker.

Series Summary

This is the sixth article in my “Excel BTS Project Tracker” series. In this series, I’m developing a completely functional Project Tracker for workers in the Mobile Telecom RAN using Microsoft Excel. With each new article in the series, I enhance the previous version of the Tracker and teach you some new Excel techniques at the same time. If you work in Technology, this tutorial is for you. If you work in the Mobile RAN, you’ll be able to download the tracker and begin using it with very little modification.

And if you don’t work in Mobile Telecoms, don’t worry: with very little modification you can use the tracker for other kinds of projects. It’ll work great with most any project that has relatively few steps, dependencies aren’t a big concern, and the same sequence of steps get applied many times.

This article teaches you about Dynamic Named Ranges, a great way to automate

The Excel skills you’ll learn can be used in many, many different situations. I call them fundamentals because you are likely to apply some of these techniques in every Excel workbook you ever create. I expect that pretty soon your peers, your boss, and other coworkers, will be asking you for help when they use Excel. So get ready to become indispensable.

You can download my tracker to follow along with this article

Send me the BTS Project Tracker

In the previous article, I added time-based reporting to the basic tracker by implementing weekly date buckets using Synthetic Data. The Synthetic Data technique is a way to derive richer reporting from a core dataset. Weekly reporting is a bit trickier than monthly reporting because Excel has no native date format that includes week numbers. To add weekly reporting I had to invent a new date format showing week numbers. Monthly reporting is much easier because you can use a DATE to bucketize a range of dates into months. (I showed this is the article before that.) I expect you’ll use both these techniques frequently.

You can read about weekly reporting at this link.

Excel BTS Project Tracker – New Date Format

If you’re new to this series, start with the first article. In just a few minutes of reading, you can get current with the series and then proceed to read this article.

Excel Project Tracker (with the Coolest Excel Function Ever!)

Approach

In this article, I’ll show you how to use Dynamic Name Ranges. I’m already tired of typing that so let’s agree to use “DNR” from here on. DNR, like the name implies, dynamically adjusts a range of cells based upon some criteria you establish.

The approach is to first create a DNR for the dates which will be the horizontal axis of our chart. Then, a separate DNR will be created to show the project completion values for each region. These values display on the vertical axis. The date-based DNR only needs to be created once, and the way I’ll set up my data table means the DNR for each region can be based directly on the date DNR. This will be more clear when I actually implement it. Don’t worry if this is not yet clear.

Preparations

To simplify the process of implementing DNR, I first inserted a new worksheet and renamed it “Dynamic Named Ranges”. I could have used the worksheet from the last article, but doing it this way makes a less-cluttered space.

In cell A1 of the “Dynamic Named Ranges” worksheet, I recreated the Pivot table based upon the same data range as in the earlier articles. I reversed the display order, putting the regions in the columns and the week numbers in the rows. I think this layout makes the DNR somewhat easier to implement and to read, and the Named Ranges for the regions are much easier to create. The values are exactly the same, so the different layout is a simple cosmetic change.

I then set up a data table adjacent to the Pivot. I linked the Region headings to the region names in the Pivot. I did it this way to make it easier if you later add a region or change the name of a region. There still will be some manual changes to the Data Table if you do that. But it should be pretty clear what needs to be done. I repeat this for the Weekly dates by linking the “Row Labels” in the Pivot.

When I wrapped the dates to hide errors, I also changed what is displayed on error. Previously, I had set this up to display a blank value. But it will be a little easier to create the DNR if I display a zero instead, so I’ve done that.

Creating Dynamic Named Ranges

DNR are basically ranges whose layout, the number of rows and columns, can change automatically. They are implemented using OFFSET with a function that can count the number of rows or columns to include. This function can be COUNT, COUNTA, COUNTIF, INDEX or MATCH. Which one to use depends on the situation and personal preference. For example, if the data you’re testing is numeric, use COUNT. If alphanumeric, try COUNTA. And so on.

I feel like I should warn you. Dynamic Named Ranges can be a little fiddly. You must get everything exactly correct. If you don’t, Excel doesn’t help much. It seems to me there are only 1 or 2 Excel error messages to cover every possible mistake. So when you create your own DNR, go slow, and be very careful to get the punctuation and spelling correct for your Names.

The OFFSET Function

Let’s talk about the OFFSET function. The function returns a range. Here is the definition:

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

The first argument is a reference, think of this as the anchor point of the function. Every other argument operates on this anchor point.
The second argument is the number of rows to offset from the anchor.
The third argument is the number of columns to offset from the anchor.
The fourth argument is optional, the height, in rows, of the returned range.
The fifth argument is optional, the width, in columns, of the returned range.

I said I’d start by creating the Date DNR. The simplest case is to pick an anchor reference that is the first cell of the date range for the chart. That leaves both the row offset and the column offset values “0”. I set up the data table so that these ranges for the chart are columnar ranges, 1 column wide, variable height depending on the data. That means the 4th argument to OFFSET will be the height, which is the variable number of rows.

The COUNTIF Function

To get that variable number of rows, I used COUNTIF.

=COUNTIF(range,criteria)

The first argument is a range. In our case, it’s a range which is 1 column wide and many rows high. How many rows? There are a couple approaches. You could enter the entire column, $J:$J. But, if there is other data anywhere in this column it will confuse the results returned by COUNTIF and can be quite difficult to troubleshoot. For this reason, I tend to avoid this approach. Instead, I select a range the is larger than the project is ever likely to use. That way, the actual dates will never overrun the end of the range.
The second argument is the criterion used to determine whether the row should be counted or not. Remember, the data table is set up to display a 0 whenever there was an excel error, including the case when there was no project data. So if I use a criterion that means “not equal to zero” the correct count will be returned. That criterion is “<>0”, because “<>” is how Excel interprets “not equal to”.

To summarize, Excel uses OFFSET to create a range, based on the reference, possibly shifted or offset from the reference, and a variable number of columns wide or rows high. That’s it!

Requirements

The requirements are essentially the same since the last time. The only difference in this article is that we can now say that no manual tweaks to the Chart should be required when the tracker is updated.

It should be easy for workers in the field to submit Updates.
The PM should be able to easily import the Updates to the project tracker.
Project status should show automatically, without requiring further calculations.
Completion status should reflect the different levels of effort among the Tasks.
Completion status should be displayed for each defined region.
Progress should be displayed by Week.
No manual editing should be required for the Chart to reflect the latest project updates.

Procedure

I’ve already described the functions used to create DNR. It’s time to do it. If you’ll look on worksheet “Dynamic Named Ranges” you’ll see the data table I created. It lies in the range $J$4:$N$31. The week number dates display in column J. The “weighted_completion” sums are displayed for each region in columns K through N. I’ll use cell $J$4 as the reference for the OFFSET function. Since this cell is in the range of dates, the row offset will be zero, and so will the column offset. Next, comes the variable part.

The Date Range

To determine the variable part of the OFFSET function I said earlier that we would use the COUNTIF function. It looks like this:

COUNTIF('Dynamic Named Ranges'!$J$4:$J$31,"<>0")

In English, this formula says to “in the range J4:J31 on this worksheet, count all cells whose value is not equal to zero.” Whatever this count is, that will be the value of the height argument of OFFSET.

Combining that with OFFSET gives us:

=OFFSET(Dynamic Named Ranges'!$J$4,0,0,COUNTIF('Dynamic Named Ranges'!$J$4:$J$31,"<>0")

Notice that the width argument to OFFSET is not defined. This is an optional argument that we don’t need, so I’ve left it out.

Excel’s Name Manager

Now we need to create the Named Range which contains the above formula. This requires us to use Excel’s Name manager. Windows and MacOS have slight differences accessing and displaying the Name Manager. You can see the Name Manager from MacOS to the left. The Name Manager for Excel on Windows is shown below. Frankly, it is easier setting up Dynamic Named Ranges using Excel on Windows.

But regardless of the platform, it is always called “Name Manager”. I work on MacOS, so this article is based on that. If you have any problems creating Dynamic named Ranges using Windows, leave a comment and I’ll try to help you.

The Name Manager can be found using the menu INSERT->NAME->DEFINE, or under the Formulas tab in the center of the ribbon called Define Name->Define Names. Select one of these.

Create the Named Range for the Chart X axis.

You can see the images how the functionality of the Name Manager differs between MacOS above and Windows below. The scope is much clearer using Windows. To add scope using MacOS, you must prepend the range name with the worksheet name, then an “!”. Like this: “‘Dynamic Named Ranges’!Chart_X”. Note how you have to wrap the name of the worksheet in single quotes, then add “!”. Be careful! I told you, Dynamic Named Ranges are a little fiddly, especially on MacOS. Then enter the OFFSET formula in the “Select the range of cells:” input box.</P

In the MacOS screenshot above, you can see on the left the name of the range, “Chart_X”, followed by a space the “Dynamic N”. This is the how the Excel Name Manager tells you the scope of the Named Range in MacOS. Pretty lame. C’mon, Microsoft, you can easily do better than this.

Create the Named Ranges for Region data.

Once the Named Range for the x-axis is complete, the rest is much easier. The range for each region is the exact same size, height in rows and width (1) in columns, as the Named Range for the x-axis. The first range for “East” region is “offset” by 1 column from the x-axis range. So to define the Named Range for the East region, use the OFFSET function again, like this:

=OFFSET('Dynamic Named Ranges'!Chart_X,0,1)

This time, the reference argument is the x-axis Named Range, “Chart_X”. The row offset argument is 0, and the column offset argument is 1. That’s easy! The remaining ranges are each offset by 1 additional column, as follows.

North:

=OFFSET('Dynamic Named Ranges'!Chart_X,0,2)

South:

=OFFSET('Dynamic Named Ranges'!Chart_X,0,3)

West:

=OFFSET('Dynamic Named Ranges'!Chart_X,0,4)

When you look in the Name Manager you’ll see them all defined.

Use Named Ranges in the Chart

I’m now ready to put it all together to create the chart.

Highlight the data table, range J2:N31.
Insert a stacked area chart.
With the chart selected, click Chart Design -> Select Data.
In the box labeled “Legend entries (series)” click the first series labeled “East”
In the box labeled “Y-values:”, replace the range part ($K$2) with the East region Named Range “Chart_Y_East”. Don’t overwrite the worksheet name “‘Dynamic Named Ranges’!”
In the box labeled “Horizontal (Category) axis labels:”, overwrite this with the Date Named Range “=’Dynamic Named Ranges’!Chart_X”.
Click “OK”.
Repeat the previous 3 steps for each region. While you are changing these ranges the chart might not look quite right. Don’t worry. As long as the DNR are correctly defined and the changes entered correctly, the chart will be correct after all have been changed.
You can check that your work was successful by clicking on one of the areas in the chart. The data range driving that area should highlight in the data table.
You also can add an entry to the Updates table to verify that the chart automatically expands. Add a row with a date of 29 May 2014, then right-click in the pivot table and select Refresh. The chart should now show 1 additional week. It’s like magic, no?!!!

And that’s it for this article. You are now an expert using Dynamic Named Ranges. Give yourself a round of applause.

Troubleshooting Dynamic Name Ranges

DNR can be a fiddly to create, if only because Excel provides rather meager or generic error messages. As you learn to create DNR, you might occasionally have problems getting a formula to work as you want. Here are some tips that can help you debug and complete your work.

When you click inside a formula in the Name Manager, the range it creates should be highlighted in the worksheet. The highlighting likely will take the form of “Hollywood lights” around the outer edge of the range. This works when you create the formula in a cell or inside the Name Manager. If you don’t see the highlighting, the formula is not yet correct.
If your formula is incorrect, Excel will display an error message. But the error message is often not very helpful.
The Name Manager differs between Windows and MacOS, with the windows version being more helpful. For example, the scope of a Named Range can be selected from a drop-down menu on Windows. On MacOS, you must prepend the worksheet name, then an “!”, then the Name of the range. When you later view this named range in MacOS, the first few letters of the scope (worksheet name) appears next to the range name in the box on the left. It is not possible to expand the box to show the entire scope name. If you think you might have misspelled it or made an error, all you can do is delete that Name and re-add it. There also might be differences between Excel versions on the same operating system.
Sometimes, when creating your named range, it helps to paste the formula into a cell in the worksheet. This can be a little easier to debug a formula like this, and the “Hollywood lights” still work the same way.

Conclusion

In this article, I showed you how to create Dynamic Named Ranges. I used this technique to automatically extend a chart whenever Updates were added. With this step, I have reached my ultimate goal: a useful, usable and informative project tracker which confirms to my “Update, Refresh, Share” mantra. Following this mantra, incredibly rich trackers, dashboards, and models can be created which require the absolute minimum of effort to maintain and update.

I’ve warned you that using Dynamic Named Ranges techniques can be a bit fiddly. So be sure to practice it on your other Excel workbooks until you are comfortable with it.

Coming Up Next

Although I’ve now created a Tracker which conforms to “Update, Refresh, Share”, my notes show a few items that I skipped over during the series. I plan to write another article to tidy up those loose ends.

Thanks for reading!

Excel BTS Project Tracker – Dynamic Named Ranges