If you use named ranges in Excel, you’ll know how useful they can be. If you are not familiar with them check my blog ( http://wp.me/p2EAVc-99 ) on setting up named ranges and how they can be used.
If you are currently using them you will also know that it can be a bit of pain having to update the range all the time as new records are added or removed from your data. If your data does change regularly, wouldn’t it be nice to have a named range that automatically adjusts to the correct number of rows or columns? Well…you can, by creating dynamic ranges.
At the heart of a dynamic range is the OFFSET function. Before we embark on creating ranges, let’s look at the syntax first as it is not that straightforward.
The official syntax you get from Microsoft when you enter the function is;
=OFFSET(reference, rows, columns, [height], [width])
…which doesn’t really tell you much if you have never used this function before.
Reference: every range has a starting point, even dynamic ones. Reference is like an anchor point from which the rest of the range is referenced.
Rows: is the number of rows away from the reference or anchor point. Positive numbers represent rows down, and negative numbers rows up.
Columns: is the number of columns away from the reference or anchor point. Positive numbers represent columns to the right, and negative numbers columns to the left.
=OFFSET($A$1,0,0 would mean the range starts from A1
=OFFSET($A$1,1,0 would mean the range starts from A2 (1 row below)
=OFFSET$A$1,0,1 would mean the range starts from B1 (1 column to the right)
…and so on and so forth.
Height: this is optional, but represents the number of rows you want to include in your range. So if we had =OFFSET($A$1,0,0,5 then the range would be 5 rows starting from A1.
Width: another optional argument. This sets the number of columns that make up your range. Continuing the formula from above, if we have =OFFSET($A$1,0,0,5,3) then we would set our range to 5 rows high and 3 columns wide starting at A1.
This of course gives us a fixed range, as we are specifically defining the number of rows and columns we want in the named range.
So how do we get it to be dynamic?
We need to build in functions that calculate the number of rows and/or columns within the OFFSET function. Generally speaking we do this within the height and width parts of the function, but you may need to alter the other arguments such as when you work with dynamic charts.
So let’s build a dynamic range;
First of all, you can’t create a dynamic named range in the same way as a normal named range i.e. select a bunch of cells and give them a name. We need to go to the NAME MANAGER and click on NEW, or go to DEFINE NAME.
In either case, give your range a name.
Decide whether the range is specific to the worksheet or can be referenced from anywhere in the workbook.
Add a comment…purely optional as a note to yourself or anyone else vaguely interested in the named range.
And then the important bit – REFERS TO: Rather than entering a basic range, this is where we have to use the OFFSET function.
Starting with a basic table, I want to create a dynamic range that automatically works out the number of rows in a fixed width table of 5 columns.
So our formula will look like this;
In plain English…starting 1 row below A1, count the number of cells that contain numbers in column A (to work out number of rows) and set the width to 5 columns.
In this example I am excluding the heading row, but you may well need to include it, if for example, you are creating a dynamic range to use in a Pivot table.
Below, we can see the correctly selected data.
If I add a few rows of data, the range is automatically recalculated to include them.
If I want the range to check the number of columns too then I need to modify the formula as follows;
Now, not only does the formula count the number of cells containing numbers in column A, it counts the number cells containing text (hence COUNTA)across the top of my data table to give me the number of columns that make up my table.
So now you can create either a one-way or two-way dynamic range depending on your personal requirements, and no need to manually update the REFERS TO cell references.
In another blog, I will show you how you can use the OFFSET function to create dynamic charts.
When you first come across named ranges they don’t seem to be the most useful of things in Excel, yet the more you learn in Excel the more uses and benefits you will discover in using them.
So what is a named range?
Basically, it is a name that has been given to a single cell or group of cells in a worksheet. But why call it by something that is longer than its address?
Let’s look at an example naming a single cell.
In A1 let’s enter the UK VAT rate (sales tax) of 20%. In some cells elsewhere in the worksheet I have some values and I want to calculate the VAT on each of those.
You can see my values in column A, the calculated VAT in column B and for ease of understanding I have displayed the formula in column C. Basic stuff you might say…a simple calculation using $ symbols to fix the cell address of the 20% rate.
Now let’s give our 20% cell a sensible name that reflects what it contains e.g. VAT_Rate. Note the use of an underscore in the name. When creating named ranges do not use spaces…it won’t work. Also be aware that calling a cell VAT20 for instance, which may seem like a sensible name, will actually take you to cell address VAT20. Don’t forget that if you are using 2007 or later, columns go all the way to XFD so add underscores or something to the name that will not turn it into a cell reference.
To give your cell a name, the easiest way is to click in the NAME BOX.
Where you see the cell address, start typing the name you want to give to your cell remembering the rules about names I mentioned earlier. Make sure you press ENTER otherwise the name won’t be saved.
If you want to check that the name works, click on the arrow on the right hand side of the NAME BOX, click on the name you have just created and all being well the cell you gave a name to will be highlighted.
“Fantastic!” I hear you say…”but you have just spent about 30 seconds creating and testing a name that is three times the length of the actual cell address and no obvious benefits…would have been quicker typing $A$1!”.
And so it would seem. But let’s rewrite our formula…
Start writing your formula exactly as before but instead of typing in $A$1, start typing the name you assigned to the cell. As we are entering a formula, when I type the first letter of the name it comes up with a list of functions, and rather conveniently, named ranges beginning with the same letter…
Either arrow down to the name or type the name in full and press enter as you would for any normal formula once all the cell references etc. have been entered. And because I’m using a name, it has a fixed location so no need for those $ symbols.
The end result in column B is no different to what we had before, but when you look at the formula, instead of some cell reference we can now see a name that gives us an indication of what is actually being calculated. So there is advantage number 1 of a NAMED RANGE. If you are used to building more complex formulas, then they will become much shorter and what they are calculating becomes much more obvious.
The second advantage is that I can now make reference to my cell anywhere in the workbook without the need to know where it is, or even ty and find it to click on it to build it into any of my formulas.
Just type the name as part of your formula as before, select the name from the list that appears, and done! Simple, and if you look at the formula you know exactly what it is calculating – the name tells you.
If you forget a name that you have created, you can bring up a list of all named ranges in your workbook by pressing the F3 key as you build your formula. Click on the name you want to use and click on OK.
So far I have only named a single cell, but you can create named ranges for entire blocks of cells, and you can even have overlapping named ranges.
Here I have manually named a block of cells containing sales data for “North”. I could then repeat this for each column, however, I will show you a more efficient way of doing this a bit further on, but here it is just to show that blocks of cells can be named too.
I can now write formulas that refer to the block of named cells;
…which will sum up the cells A2:A12. No need for me to physically select any cells, just use the name and once again when I look at my formula I know exactly what it is adding up.
Hopefully by now you can start seeing some of the advantages of using named ranges.
A bit of a random geek fact for you: if you zoom down to 39% or less, named ranges are actually displayed in the worksheet (works best with blocks of cells rather than single ones).
The quickest way to name a range is to simply type the name in the NAME BOX and press enter but you can also do it through the NAME MANAGER or DEFINE NAME in the FORMULAS tab.
Use the dialog box to name your range, decide on its scope i.e. is referencing the name limited to a single worksheet or can it be used across the entire workbook? You can add comments, personally I never bother but the choice is yours. And finally which cell(s) does your name refer to? Click on OK when done. The new name you have created will then appear in the drop down list in the NAME BOX.
If you go through the NAME MANAGER click on NEW in the top left hand corner to open the same dialog box as above.
If you want to EDIT or DELETE your named ranges you need to come through the NAME MANAGER. Unfortunately, you cannot delete named ranges via the name box.
If you have dozens of named ranges, you can use the FILTER button to limit the list by filtering on different criteria.
I mentioned earlier a quick way to generate range names. Previously I created named ranges by selecting the cells myself and using the NAME BOX to assign a name. That’s fine if you only have 2-3 to create but what if you have loads of columns or rows that you would like to refer to by name?
Using a basic two-way table (you can do this on any size of table by the way) highlight the whole table. On the FORMULAS tab, click on CREATE FROM SELECTION or if you like keyboard shortcuts use Ctrl + Shift + F3 and you will see this…
Decide which row/column values you want to use as range names and tick/un-tick the relevant boxes in the dialog window. Click on OK. Nothing apparent happens until you check out the NAME BOX or the NAME MANAGER.
We now have a whole bunch of newly created named ranges using the row and column headings from the table!
Test them out if you want by clicking on any of the names and see which cells are highlighted. You can now start writing formulas using as many names as necessary;
Now tell me that’s not better than =SUM(Sheet4!B2:B5,Sheet4!D2:D5)!
Finally, be aware that all the methods I have shown you here apply to FIXED dimension ranges i.e. the cell(s) you select and name remain unchanged unless you go into the NAME MANAGER and edit the REFERS TO information adding further rows/columns. You can create dynamic ranges but I will write about those separately as they are quite a big topic in their own right.
A named range CAN be automatically resized as long as new rows/columns are added WITHIN the boundaries of the original named range. If my named range is from A1 to D20, as long as I insert or delete rows between rows 1 and 20, or insert/delete columns between A and D, the range will include those automatically. If I add data from row 21 or column E and beyond I would need to manually change the REFERS TO references in the NAME MANAGER.