Naming Ranges, Cells, and Constants in Excel: The Whys and Hows

Here’s the data I’m going to use for this tutorial:

Naming Ranges

Why?

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.

How?

For this example, let’s say we want to name the range of cells that include the “Amounts” (cells E2:E13 above).

1. First highlight the cells, E2:E13
2. 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:
3. 
4. 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.
5. There are some rules about range names:
1. Names can not have spaces in them
2. 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. 
3. Names can not begin with a number.
4. Names can not use certain special characters.
5. 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.

6. Now that you’ve named the amounts range, select the range D2:D13 and name it quantity, using the instructions above.
7. 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

1. 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.
2. 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 your workbook.

Using named ranges to create charts or pivot tables, or any process that requires that you first highlight the range.

1. Again, go anywhere in your workbook and click in any cell.
2. Let’s pretend you are on the 50^th 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.
3. 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 easier.

Naming Cells

Why?

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 nesting. Named cells can be used like bookmarks in your workbook, and named cells can also be referenced in formulas.

How?

Using named cells as bookmarks

1. Go back to Sheet 1 where the original data is and click in cell A1.
2. Click in the Name Box and type top and hit Enter.
3. 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.
4. Go to A40 and name cell it nw and type Northwest Sales in cell A40.
5. 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 sales.
6. 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

1. Go back to the original sheet and put a formula in cell A14 that counts how many customers ordered in January:
   =COUNTIF(A2:A13,"January")
   then hit Enter. If you used the data above, the result of this formula should be 4.
2. Click back into cell A14 and name it jancust (or any name you choose which identifies it as your count of January customers)
3. Now, go to another sheet and enter this formula in any cell:
   =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.

Naming Constants

Why?

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 easily. For 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.

How?

1. Because constants are values that do not appear in any cell, you can’t just click in a cell and name it.
2. 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.
3. 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.
4. 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:

5. Now, on Sheet 1, enter this formula in cell F4:
   =E4*increase+E4
   and you will get the result of adding an increase of 12.9822% to cell E4.
6. 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 to formulas.
• 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.

Happy naming!

Linda

http://personal-computer-tutor.com