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.

Formula showing use of absolute reference

Formula showing use of absolute reference

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.

The Name Box

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.

Name added to the Name Box

Name added to the Name Box

“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…

Named ranges appear in the functions/names list when you type a formula

Named ranges appear in the functions/names list when you type a formula

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.

Same formula as before but using the new named range

Same formula as before but using the new named range

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.

Using the named range elsewhere in the same workbook

Using the named range elsewhere in the same workbook

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.

Use F3 to see a list of all named ranges in your workbook

Use F3 to see a list of all named ranges in your workbook

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.

Naming a block of cells

Naming a block of cells

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;

=SUM(SalesNorth)

…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).

Zooming down to 39% (or less) to see named ranges in a  worksheet

Zooming down to 39% (or less) to see named ranges in a worksheet

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.

Choosing the scope of a named range

Choosing the scope of a named range

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.

The name manager

The name manager

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.

Using the filter in the name manager

Using the filter in the name manager

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…

Using Create From Selection to create named ranges

Using Create From Selection to create named ranges

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.

The full list of named ranges in the workbook

The full list of named ranges in the workbook

We now have a whole bunch of newly created named ranges using the row and column headings from the table!

Showing the automatically created named ranges

Showing the automatically created named ranges

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;

=SUM(Yr_2009,Yr_2011)

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.

Advertisements

Posted on April 8, 2014, in Info and tagged , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: