This note explains why named cells and ranges, are useful in spreadsheets. It also describes how they are created, managed and applied in Excel and other spreadsheets. There is also a little parable about Excel not tidying up behind you!
Why use named cell and ranges in spreadsheets?
There are several reasons for using named cells or ranges (We shall refer to them as names rather than the older term range names).
The situation shown on the right illustrates two of them. Here we have a formula, copied down a column, containing a “hardwired” constant. What is wrong with that? Firstly it is not immediately obvious that the 10% in the formula is the applicable tax rate. Secondly, and more seriously, if the tax rate changes then a whole lot of formulas need to be changed. This can lead to a major updating problem with potentially serious consequences if it is not carried out systematically. (See also our “169 + Reasons for Using a Range Named Spreadsheet Cell” article).
Ok. So now we have a separate cell (B1 in our example) containing the tax rate. It is sufficient just to refer to this cell in formulas using a fixed reference (e.g., $B$1) but it is much better to give this cell a name as we have done here. This not only makes the formula more readable and understandable but it is also prevents another problem. This is where a formula is set up using the point and click method. The formula gives the correct answer for the current row (left-hand picture below) and it is then copied down the column without too much further thought. The resulting problems (right-hand picture), occurring because the formulas in the rest of the column do not refer to the correct tax rate cell, are easily overlooked.
In our tax rate example a single cell is given a name in order to prevent errors and make formulas more readable. Exactly the same reasoning applies when a range of related cells are given a single name. For example, it is often convenient to give a name to all the cells in a lookup table as in the next example. In other situations it is convenient to give names to individual columns in tables (also known as spreadsheet lists or databases). This is particularly useful when producing charts.
Creating a Name
The easiest way to create a name in Excel, and more recent versions of OpenOffice.org Calc, is as follows: with the range selected, type the name into the Name Box and then press enter. The illustrations, produced using Excel 2003, are before and after shots! The first one shows the range A4:B7 selected, and about to be named, and the second shows it named. There are rules for valid names (Online Help will spell these out!) but if it starts with a letter and doesn’t include spaces or special characters, other than underscore ( _ ), it should be ok. Note that the easiest way to find the range that a name applies to is to select it from the pull-down list in the Name Box. Names cannot be modified via this route, however, but more about that later.
There are a couple of other ways to create names using menu/ribbon options. In older versions of Excel and OpenOffice.org Calc the Insert | Names | Define option is used. A new name in Excel 2007 can be created using Define Name from Formulas | Defined Names. In fact, Excel 2007 has enhanced name features and also provides a really useful Name Manager. A name can now have a scope that is not necessarily the entire workbook as well as a comment. But more about that later, as well.
Creating Names from Row or Column Labels
There is an easy way to create names for a range of cells using adjacent labels. Selecting the range shown on the left and using Insert | Names | Create (Formulas … Create from Selection in Excel 2007) brings up the dialog to the right.
We find this a bit confusing but what it means in this case, with Left column ticked, is that “the cells in B14:B18 will be given names derived from the labels in A14:A18”. The Define Name dialog illustrates the result. Note that because the labels Conference fees and Company displays contain spaces, the names derived from them automatically have underscores.
For the same selection if we had (stupidly) ticked Top row then the name Conference_fees would apply to cells A15:A18. The cells in column B would not be named as $60.00 is an invalid name. (We had to try this out to find out exactly what did happen here!).
Using Names
Now that we have figured out how to name cells and ranges we should discuss how to apply them! There are three good ways. When constructing a formula, clicking on a previously named cell (or range) automatically incorporates its name in the formula. The illustration shows a formula being constructed. Cell B1 has been clicked but it is its name, TaxRate, that is incorporated into the formula.
An alternative method is to use the [F3] function key to bring up a list of existing names. The required name can then be selected and automatically “pasted” into the formula as in the left hand picture below. This shortcut is most useful when filling in ranges in dialog boxes. The middle example below shows it being used when the same formula is constructed using the Function Arguments dialog. In the other picture we are modifying a chart, in Excel 2007, to add the correct x-axis labels. If we had previously named the range then using [F3] and pasting the name would be appropriate here as well.
There is a menu option to perform a similar function to [F3]. In Excel 2007 it is the Use in Formula option from the Defined Names. In earlier versions of Excel and in Calc it is Insert | Name | Paste. The last and not really recommended method, to use names, is simply to type them in. If we’re doing the typing there is a fair chance they won’t be correct!
Applying Names to Existing Formulas
If a name is created then it doesn’t automatically get applied to existing formulas that reference its range. To remedy this situation then the cells containing the formulas are selected first and then in Excel the Insert | Names | Apply or Use in Formulas option, which ever is appropriate, is used. In OpenOffice.org Calc the Insert | Names | Insert option is used.
Getting a Table of Names
As well as pasting individual names into formulas and dialog boxes it is possible to get a list of names and their ranges (useful when documenting workbooks).
The Paste List button produces the table on the right.
Changing a Name
If you want to change a name, e.g. to correct its spelling, you usually end up deleting it and defining a new name. If the deleted name is used in formulas the result will be #NAME? errors as the formulas now reference a non-existent name. In other words the formulas don’t adjust back to using cell references. The easiest way to fix these errors is to use Find & Replace to substitute the new name for the old one.
A Parable
When writing the previous section we stumbled (yet again!) upon an old problem. The list of names in the workbook included several prefixed by #REF. Why did these names exist? How did we fix the problem?
After some head scratching we figured out that those names related to the content of a sheet that had been deleted from the workbook. Excel had not deleted the names automatically. Once we were aware of the problem it was easy to fix as they could be selected and deleted manually. However, one would be forgiven for thinking you shouldn’t have to tidy up like this! Having found this problem in Excel we then checked out Calc to see if the open source community were on to this problem. No they were not. Exactly the same problem occurred.
Moral of the story – when checking a workbook examine the names to ensure they are all correct.
Excel 2007 Names
Names in Excel 2007 have greater functionality than in previous versions. As well as defining names that apply to the entire workbook, names can now apply only to a specified sheet. In the example here we now have two TaxRates, another one in Sheet2 as well as the one we used above. There are some situations where this will be useful but in general this is likely to cause problems. By default names are created as workbook names. The picture shows the Name Manager which is new in Excel 2007. Showing Values and the ability to Filter names are features new in 2007.