Dynamic Named Ranges in Excel 2010

A named range is useful but a dynamic named range is the foundation of a fully automated Excel dashboard, learn how to make your ranges dynamic so updating Excel files becomes a thing of the past…





In a previous post we covered how to create named ranges in Excel 2010.  Now it’s time to look at taking this concept to the next level by creating a dynamic named range in Excel 2010.

A dynamic named range in Excel 2010 is a term used to describe a named range of cells that expands, or contracts, as your data does.

For example, if you had the following in your Excel worksheet:

You might create a named range for “Sales” in cells B5:B8 and then use this named range in your Total Sales figure for cell B2 with the formula =SUM(Sales).

That would be a good practice but what happens next month when your data is refreshed?

You get an extra row of data in row 9 for May but your named range for “Sales” is still referring to B5:B8…that means you need to edit the named range.

Why not just edit the range?

In truth if you have just 1 named range to update then you could probably get by just editing the range every month, but in practice this is rarely the case.

Named ranges are useful in large reports and dashboards so more often than not you will have multiple named ranges in your Excel workbook, that makes remembering to edit each one a lot harder, a lot more time-consuming and leaves you exposed to more errors in your reports and Excel dashboards.

Dynamic Ranges – The solution

The solution therefore is to make each named range dynamic.  This can be done when you are creating the named ranges for your Excel report or dashboard or after-the-event you can edit the named ranges to make them dynamic.

Introducing your new friend, OFFSET

Meet your new friend in dynamic ranges, the OFFSET function, which takes the format:

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

The OFFSET function is a way of telling Excel to navigate from a certain cell, the reference, to another cell which is so many rows and so many columns away from that reference point, the height and width, contained in square brackets meaning they are optional, are important in dynamic ranges.

Height refers to the number of rows you want returned, and width determines the number of columns you want returned (therefore height and width must always be a positive number when used).

How to use the OFFSET to make the range Dynamic

In order to make the example named range “Sales” dynamic the formula for the range needs to be edited to an OFFSET version.  To start with open the Name Manager, which can be found in the ribbon under “Formulas”, then select “Sales” which is the named range to edit:

In the current formula the actual data range B5:B8 is specified (see the “refers to:” box) but that needs to change with the help of the OFFSET function.

To start with let’s build the OFFSET formula piece by piece:

Reference: The first part is the reference.  This is our starting point so it makes sense that it begins where the data begins.  It is also useful as this point rarely changes; even with expanding data the start point generally stays the same.  For the example in question Jan-11 Sales is in cell B5 so that becomes the reference.

Rows: This tells Excel how many rows to move up or down from the reference point.  In this case we don’t want to navigate away from the reference point so it is set to 0.

Cols: This tells Excel how many columns to move left or right from the reference point.  Again we do not want to navigate away from our reference point, so again this is set to 0.

Height: This is the key part.  The reference point has told Excel where to start and now this will tell Excel how many rows to return in the answer so this is the part that needs to be dynamic.  In order to tell Excel how many rows to bring back we can use another function, COUNTA, this will count the number of non-blank cells in a specified range.  So in this place we can specify COUNTA(B5:B999) which means it will count every non-blank cell between B5 and B999, as long as the range does not exceed 999 it will count it.

Top tip: If you set the end of the COUNTA range to the end of the worksheet you eliminate any worries of the data exceeding your range.

Width: As this tells Excel how many columns to return and we only want 1 column of data we can just set to 1 or leave blank, in this case set to 1 for completeness.

Dynamic Formula

Put all the components above together and the formula looks like:

=OFFSET(B5,0,0,COUNTA(B5:B999),1)

And this can be placed directly in the Name Manager box in the “Refers To:” section:

Then the next step is to click on the check-mark arrow icon to the left of the formula to verify it, if completed correctly sheet names will populate in your formula (assuming you haven’t already used them):

Now when you return to the worksheet you will find the total sales figure has updated correctly:

And when June, July and August come along it will keep updating itself:

You now have a truly dynamic named range. Any formulas or references made to the named range “Sales” will no longer have to be updated each month so you can quickly see the benefits of these steps, especially in larger reports and Excel dashboards.

Summary

The process does seem tricky to beginners and that is mainly down to using the OFFSET function which is unfamiliar territory for most, however once you break it down into parts all the OFFSET function does is say “tell me where I am, tell me where to move, tell me what to return”.

If you get time I would recommend spending 10 minutes playing around with the formula just to see how easy it is once you have the basics.  Then start implementing into a few named ranges and once you have practice a few times it will become second nature.

As an Excel analyst you should always be striving for as much automation in your reports and dashboards as possible, it will save you time in the long-run, make the report less likely to contain errors and help with any handover of report production.  Dynamic ranges in Excel are another crucial technique to learn to achieve automation.

Keep Excelling,

Leave a Comment