In this article we are going to look at one of the basic elements of Excel, specifically how to name a cell, and alternately how to name a range of cells. This is useful for general readability and efficiency when it comes to using functions and formulas. Naming cells is also very useful, with readability, when using the Scenario Manager Summary report.

We’ll use the following example to illustrate how to name cells and later in the article how to name a ranges of cells. Suppose we are a fictitious business that sells candies and sweets. We sell these items, likely to other businesses, by the crate. Each crate only has one type of product. The sizes of all the crates are all the same, but the weight for each crate, of different products will also be different.

Fig. 1  – Example Data Set – Starting point

Fig. 1 shows a spread sheet with a list of products (in locations A3 to A15) along with the weight of each crate (B3 to B15). The number of crates sold are in F3 to F15. We also have values for The Crate Base Weight in cell J4, the Overweight Charged per pound in cell J7, and the Crate Base price in cell J10.

The charging model for this business is to price each crate with a starting value of $559 (stored in J10) assuming a minimum weight of 200 pounds (the value stored in J4). Any weight over 200 pounds, is multiplied by $5 (that value is stored in J7) and added to the minimum starting value. (Note that for this example we are assuming all the values in cells B3 to B15 are greater than 200.)

Calculations Using Relative and Absolute Cell Reference:

Let’s do the first calculation in cell C3; this will be the value in B3 (i.e., 223.2) minus the value in J4 (the 200). The formula =B3-J4 seems straight forward, and we get the following:

Fig. 2: Example file. First calculation – the amount over the base weight.

We run into a problem when we autofill the calculations in cells C4 to C15:

Fig. 3: Example file. Errors with the calculation of the “Amount over the Base weight”.

So, what went wrong? If we click in cell C4 we can see that the formula that Excel used is: =B4-J5

Fig. 4: Example file. Details of the errors with the calculation of the “Amount over Base weight”. Focusing on location C4.

Excel changed B3 to B4 which is what we want, but Excel also changed J4 to J5 and there is no value in cell J5.

Let’s check one more and look at the location C5. This has the formula =B5-J6. Again, Excel updated the cells, which is okay for the B5 location, but the other location, J6, contains text and not a number, so we get the error.

Fig. 5: Example file. Details of the errors with the calculation of the “Amount over Base weight”. Focusing on location C5.

One way to get around this feature, of the fill handle completing a sequence, is to use absolute referencing for locations that we don’t want Excel to change. This can be done by using the $ sign before the column reference and another $ before the row reference. For this example, the formula would be =B3-$J$4. (One way of remembering this is to say to yourself: lock column J and lock row 4. Note that even though we don’t necessarily need to lock row 4 in this example, it also doesn’t interfere will the calculation):

Fig. 6: Example file: Calculating the “Amount over Base weight” using absolute referencing for cell location.

Next, we’ll use the fill handle. And this time we get the results that we are looking for:

Fig. 7: Example file: Results for the calculation for the “Amount over Base weight”

If we click on cell C4 we’ll see the formula =B4-$J$4, which is correct.

Fig. 8: Example file: Details of calculation for the “Amount over Base weight” and absolute referencing focusing on cell C4.

And if we click on cell C5 we’ll see the formula =B5-$J$4, which is also correct.

Fig. 9: Example file. Details of calculation for the “Amount over Base weight” and absolute referencing focusing on cell C5.

Now, we are getting the correct answers for all the locations. However, the formula is getting harder to read. Even the first formula, =B3-J4, is not very intuitive.

Let’s go back to the original file and this time name the cells before doing the calculation.

Using Name Cells – Method One:

Using names rather than locations tends to be easier for people to read and maintain. In this part, let’s change the name of cell J4 to CrateBaseWeight.

Note that the spaces between the words were deliberately removed. There are restrictions as to what is allowed for names, and here are the main ones:

  • must start with a letter
  • spaces aren’t allowed
  • range names must be unique
  • names can’t correspond to a cell address – not “C10”

There are several methods that can be used, and we’ll look at a couple of them as we work through other calculations in this spread sheet. First let’s look at the simplest and most used method.  Simply click on the cell you wish to name, J4 in this example, then in the name box type in the name, CrateBaseWeight, and tap on the Enter key to commit the name:

Fig. 10: Example file: Starting file with a name entered for cell J4

Now let’s enter the formula in cell C3, but instead of the locations we used previously, we will use the name that we just entered. =B3-CrateBaseWeight:

Fig. 11: Example file. Calculation for “Amount over Base weight” using a cell name, CrateBaseWeight, for location J4.

Next, we use the auto fill to get the results:

Fig. 12: Example file: Calculation results for “Amount over Base weight”

Note that the issue of fixing or locking the cell has disappeared. Excel assumes absolute references when a name is used. Next let’s continue on with the calculations needed for the rest of spread sheet, while exploring other methods of naming cells.

Using Name Cells – Method Two:

Next, we will work on the calculation for cell D3. We need to use the value in cell C3 and multiply it with the value in cell J7, i.e., $5. For this calculation we will use a different method for naming. Click on cell J7 then in the Formulas tab, in the Defined Names group, click on the command Define Name:

Fig. 13: Example file: Naming a cell using the Define Name command

The New Name dialog box will pop-up. Notice that Excel has assumed a name for us, it got that from the cell directly above the cell we originally clicked on. It has also substituted underscores “_” for spaces and brackets. We are just going to use the defaults, so click on OK.

Fig. 14: Example file: The New Name dialog box.

You should now see that name in the name box:

Fig. 15: Example file: Showing the new name for cell J7

We are ready to enter the calculation. Click in cell D3 and type an equal sign “=”, then click on cell C3, next type an asterisk “*”, followed by clicking on cell J7 (this saves some typing, which is handy for longer names):

Fig. 16: Example file: Formula for  the “Over Base Crate Charge” using a name rather than location.

Using the fill handle will give us the results for column D:

Fig. 17: Example file: Resulting calculation for the “Over Base Crate Charge”

Using Name Cells – Method Three:

For the calculation in cell E3 we just want to add the value from D3 with the Crate Base Price, which is $559 located in cell J10. We will use a third method for creating a name for cell J10. First select both cells J9 and J10, note that J9 has the text of the name we want. Then, in the Formulas Tab, in the Defined Names group, click on Create from Selection:

Fig. 18: Example file: Cell name using the “Create from Selection” command

We get the “Create Names from Selection” dialog box. Click on OK to commit the command:

Fig. 19: Example file: Focus on the “Create Names from Selection” dialog box

Click on cell J10 and notice that the name is in the name box:

Fig. 20: Example file: Showing the name for cell J10

Now for the calculation in cell E3, which is simply: =D3+Crate_Base_Price

Fig. 21: Example file: The formula, using a named cell for the “Crate Selling Price”

And with the autofill we have the results:

Fig. 22: Example File: Results for the “Crate Selling Price”

Using Name Ranges:

We can also use ranges for the names.  For our example we will first name the range from E3 to E15 as Crate_Selling_Price, the range from F3 to F15 as Crates_Sold, and the range from G3 to G15 as Total_Sales. We will also use the same three methods we just used for single cells.

Method 1: Select E3 to E15 and in the name box type in Crate_Selling_Price, followed by tapping on the Enter Key:

Fig. 23: Example File: Naming a range using the Name box. The Crate Selling Price.

Method 2: First select F3 to F15. Then in the Formulas Tab click on the command Define Name and a New Name dialog box will pop-up. Click on OK to commit the command.

Fig. 24: Example file: Naming a range using the Define Name command, showing the New Name dialog box

And you should see the name in the name box:

Fig. 25: Example file: Showing the name of the range F3 to F15 as Crates_Sold

Method 3: Select G2 to G15 (note we are selecting the header cell with the data cells), then in the Formulas Tab click on Create from Selection. Click on OK since we just need the defaults:

Fig. 26: Example file: Using the “Create from Selection” command, showing the dialog box, for “Total Sales”

This new name will also be in the name box:

Fig. 27: Example file: Showing the name for cells G3 to G15

Calculation using name ranges:

Now to calculate the Total Sales first select G3 to G15 (if not still selected). Type in an “=”, then Crate_Selling_Price, a “*”, then “Crates_Sold” and finally tap on the Enter key. The results will populate the selected cells:

Fig. 28: Example file: Calculations for Total Sales showing the formula with name ranges.

For the very last calculation, the Grand Total of Sales, in cell J15; Type in =SUM(Total_Sales) and finally, tap on the Enter key.

Fig. 29: Example file: Completed calculations. Focusing on the Grand Total of Sales.

Naming individual cells, or ranges of cells, allows for a number of benefits. The main ones being readability and efficiency in creating and maintaining calculations; as well as reducing typos, and spotting mistakes.