Excel – Creating & Using Named Ranges
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.