Hereís the data Iím going to use for this tutorial:
Naming ranges in Excel can save you lots of time and repetitive
work. Letís say you have a range of cells that you are including in many
different formulas or you have a block of cells that you are using a
lot to produce various charts and/or pivot tables. If you name the range,
you can easily select that range anytime you need it. Using a named range
in a formula also negates the need to make the range an absolute reference
because it will always point to the correct range, no matter where you
copy the formula.
For this example, letís say we want to name the range of
cells that include the ďAmountsĒ (cells E2:E13 above).
- First highlight the cells, E2:E13
- Now click inside the ďNameĒ box on your toolbar at the
top of your screen. Depending on which version of Excel you are using,
it will appear on a different toolbar. But, it will be on the left end
and looks like this:
- When you click inside this box, you see the cell name
(E2) is highlighted and shoots to the left of the box. While itís highlighted,
whatever you type will replace whatís in there, so just type amount then
hit your Enter key.
NOTE: You must hit the Enter key when you are finished typing the
name so it is accepted. You can tell itís been accepted when it centers
in the box.
- There are some rules about range names:
- Names can not have spaces in them
- Names can not be the same as a cell reference. For
example, you could not use the name Q1 for a range showing the
sales for your first quarter, because Q1 is a valid cell reference.
You could however, name the range Q1Sales or Q1_sales.
- Names can not begin with a number.
- Names can not use certain special characters.
- If you forget any of these rules and try to name a
cell something that Excel will not accept, you will get an error message
and will have to change it.
- Now that youíve named the amounts range, select the range
D2:D13 and name it quantity, using the instructions above.
- Then, highlight all of the data (A1:E13) and name it sales.
Now that you have three named ranges, letís look at how
to use them.
Using named ranges in formulas
- Move to another sheet in your workbook and click in any
cell and type this:
=SUM(amount) and thereís an instant sum of whatís in your Amount column.
- Go to another location in your workbook and type this:
=COUNTIF(quantity,"<200") and you get the count of how many
numbers in the Quantity column are less than 200.
You can see that using range names makes it much easier
to reference specific ranges in your formulas, no matter where you are in
Using named ranges to create charts or pivot tables, or any process that
requires that you first highlight the range.
- Again, go anywhere in your workbook and click in any
- Letís pretend you are on the 50th sheet in
your workbook and need to select a range in Sheet 1 because you want
to create a chart of that data. Obviously, you could scroll through all
the tabs at the bottom of your workbook to get to Sheet 1, then highlight
your range, but if the range is named, this is MUCH easier.
- Click on the dropdown arrow to the right of your Name
Box on your toolbar and you will see all of your named ranges in
there. Select sales from
the list and VOILA Ö you immediately jump to the sheet which contains this
range and it is already highlighted for you, so now you can easily use
that range to create your chart or pivot table.
Now that you understand the basics of this, Iím sure you
can see other instances where naming ranges will make your job quicker and
Sometimes you donít need a whole range named, but just one
cell so you can quickly jump to a specific location in your document. Or,
if a cell contains a formula and you want that formula inside a new formula,
naming the cell can make your formula writing easier, without so much
cells can be used like bookmarks in your workbook, and named cells can
also be referenced in formulas.
Using named cells as bookmarks
- Go back to Sheet 1 where the original data is and click
in cell A1.
- Click in the Name Box and type top and hit Enter.
- Go to cell A20 and type Northeast Sales in that cell
and hit Enter. Now click back on cell A20, then click in the Name
Box and type ne and hit Enter.
- Go to A40 and name cell it nw and type Northwest
Sales in cell A40.
- Repeat this in cells A60 and A80 using the names se and sw to
name those cells which will contain the info for your Southeast and Southwest
- Now move to any other sheet (or anywhere within this
sheet) and select any of these names from the Name Box and you will see
you can quickly jump to these ďbookmarksĒ youíve created.
Using named cells to nest formulas within formulas
- Go back to the original sheet and put a formula in cell
A14 that counts how many customers ordered in January:
then hit Enter. If you used the data above, the result of this formula
should be 4.
- Click back into cell A14 and name it jancust (or
any name you choose which identifies it as your count of January customers)
- Now, go to another sheet and enter this formula in any
=IF(jancust>2,"yes","no") and you will see a yes,
since we have 4 in cell A14 on Sheet 1, which is greater than 2. If you
change two of your Januarys in column A on Sheet 1 to Februarys, this will
change to a no.
NOTE: This is a simple IF statement. If you donít
understand IF statements, see my previous articles in TechTrax: Introduction
to IF Statements in Excel and Excel
IF Statements, Part II: Nesting Functions.
You can see, again, how naming this cell makes the formula
easier because you donít need to remember exactly where this cell is in your
workbook and you donít need to get into the whole nesting thing.
Sometimes you have a value that you need to use over and
over in many formulas, but the value itself does not appear in any cell
within your workbook. If you name the value, you can add it to many formulas
example, letís say the boss just returned from a big promotional tour and
told all the companies that heís increasing the quantities of all products
they ordered in March by 12.9822%. Now, you could go to each of these
cells and manually multiply them by 12.9822%, but what if you made a typo
and accidentally typed 14.9822% one time? If you name 12.9822% as a constant,
you will greatly reduce your risk of error.
- Because constants are values that do not appear in any
cell, you canít just click in a cell and name it.
- To name a constant, either go to the Insert menu and
choose Name, then Define; or simply hit Ctrl+F3 and you will come
to the Define Name dialog box.
- You will see the name at the top where it says ďNames
in workbookĒ probably reflects the contents of whatever cell you had selected
when you did this. Just replace whatís there with the word increase.
- The box at the bottom will show the cell reference for
whatever cell you were in when you did this, so replace that with the constant,
12.9822%, as you see below:
- Now, on Sheet 1, enter this formula in cell F4:
and you will get the result of adding an increase of 12.9822% to
- Or, if the boss wanted to decrease many amounts by this
same value, you could use =E4-(increase*E4) instead.
The main advantage to naming a constant is it will reduce
your chance of error, because if you accidentally type =E4*14.9822%+E4, Excel
would calculate this for you without an error, so you may not notice you
made a mistake. But, letís say you used a named constant and accidentally
mistyped the name. If you accidentally typed =E4*increaase+E4, Excel
would return a #NAME? error because it didnít recognize the name ďincreaase",
so you would immediately know you have an error in the name.
A couple more tips on names
Now that you understand how to use names, you may soon find
your workbook is filled with them and you canít remember them all, so hereís
a couple tips.
- Go to a blank cell in your workbook and type =500+ and
hit the F3 key. Thereís your Paste Name box. Double click on quantity
and you will see the name is quickly added to your formula without
you having to remember it or spell it correctly. So, when you have lots
of names in a workbook, the F3 key is your friend.
NOTE: Because named constants do not refer to any specific cell,
you wonít see them in the dropdown list from the Name Box. However, they
will be included in the Paste Name box whenever you want to add them
- While inside that Paste Name box, notice thereís a button
that says ďPaste ListĒ. Go to a blank worksheet at the end of your workbook
and type Names in Workbook in cell A1, then click in cell A2 and
hit F3 to get the Paste Name box, then click on Paste List. Now you have
a directory of all of the names in your workbook and the cells or
constants they refer to that you can access or print when you need
to refresh your memory. And, you can name cell A1 on this sheet something
like index and
you will have a bookmark that will quickly take you to this list.
I hope this helps you in organizing all that data you have
in those large workbooks. Named ranges make my life so much easier and I
hope they will do the same for you.