Spreadsheets: What's In A Name?

To quote Shakespeare's Romeo and Juliet, "What's in a name that which we call a rose? By any other name would smell as sweet." What the famous play writer is saying is that you can change the name or label given to an object, but it doesn't change what that object is.

In Microsoft Excel we use cell references in our formulas or calculations to identify the location of data. Cell references are made up of the cell's column letter (A, B, C...) followed by the cell's row number (1,2,3....). So cell A1 refers to the very first cell and A2 refers to the cell underneath.

Not many Excel users are aware that it is possible to apply alternative and meaningful names to single cells or even a range of cells. These alternative references are called Named Ranges.

For example, in a spreadsheet, column B lists prices of products excluding VAT (Value Added Tax). The rate of VAT is located in cell D1 e.g. 15%. To perform the VAT calculation for the first product in column B, the formula would be '=B1*D1' or '=B1*$D$1', where B1 is the price of the first product and D1 is the rate of VAT. It is more likely that D1 should be an absolute reference ($D$1) in order to be able to replicate the formula to the other product prices using the AutoFill or Copy & Paste.

Looking at this formula in isolation, it is difficult to interpret its meaning, especially if you didn't construct it yourself. However, if you apply a meaningful name of 'VAT' to cell D1, you can replace the above formula with '=B1*VAT'. Looking at this formula you can immediately understand its meaning and context. Another advantage is that if you need to construct another formula using the VAT rate on any worksheet in the workbook, you don't have to remember that the VAT rate is in cell D1, simply refer to it by its name 'VAT'.

The procedure for naming a cell is straightforward. First select the cell you want to apply a name to e.g. cell D1. Locate the Name Box which can be found to the left of the formula bar under the Ribbon. The Name Box always specifies the cell which is active (D1). Click in the Name Box with the mouse, the active cell reference becomes highlighted. Delete or overwrite the current reference by typing in the meaningful name for this cell ('VAT') and press the Enter/Return key on the keyboard. This last step is very important as if omitted, Excel will not create the name.

The Name box has a drop down arrow at the right and if clicked, a menu of all the names in the workbook is displayed. If you have a number of Named Ranges across different sheets in the workbook, you could use the Name Box as a navigational aid by selecting the required Named Range from the drop down menu. Excel will then make that Named Range the active cell.

Named Ranges can be used to name more than one cell. For example, if all the product prices are located in cells B1-B10, you may want to allocate the name of 'Prices' to these cells. Follow the aforementioned steps for naming a single cell except instead of selecting one cell at the beginning of the procedure, highlight all the cells in the range to be named. You could use this name in a formula to total all the product prices e.g. '=SUM(Prices)'.

From time to time you may need to change the reference of a Named Range or delete it altogether. Named Ranges are managed in the Name Manager on the Formulas tab of the Ribbon. Here you can edit the name, change which cell or range the name refers to or delete it all together.

Giving a cell a meaningful name as well as its cell reference, like Shakespeare's rose, does not change the cell, but it does make formula construction a whole lot easier.