Welcome to our series of applied tips and tricks in Excel for accountants and financial analysts wherever you may be. In this article, Liam Bastick looks at the use of range names in spreadsheets.
Does Excel like it when you call it names?
If you were to ask modelling professionals about the merits of using range names you will find that opinion is strongly divided. In spreadsheets, used appropriately and sparingly, great value can be obtained from using range names, as it can make formulae easier to read. In macros (not discussed here), they are vital. Overuse, on the other hand, can lead to end user confusion.
Creating range names
Range names were created using ‘Define Name’ in Excel 2003 and earlier, whilst they are created via ‘Name Manager’ in Excel 2007 and later. The Name Box (circled below),
drop down menus and/or Ribbon may be used, or keyboard shortcuts such as ALT + I + N + D + N or ALT + M + N. I would suggest using none of these methods. Simply use the keyboard shortcut CTRL + F3 in all versions of Excel, and then click on the ‘New’ button in the ‘Name Manager’ dialog box), viz.
After clicking on ‘New’ (above), the following dialog box appears:
Note the second section (Scope). All names have a scope, either to a specific worksheet (also called the local worksheet level) or to the entire workbook (also called the global workbook level). The scope of a name is the location within which the name is recognised without qualification.
For example, if you have defined a range name as ‘Profit’ with its scope as Sheet1 (say) rather than ‘Workbook’, then it will only be recognised in Sheet1 as ‘Profit’ (ie without qualification).
To use this local name in another worksheet, you must qualify it by preceding it with the localised worksheet name:
If you have defined a name, such as ‘Cashflow’, and its scope is the workbook, that name is recognised for all worksheets in that workbook (but not for any other workbook).
A name must always be unique within its scope. Excel prevents you from defining a name that is not unique within its scope. However, you may use the same name in different scopes. For example, you can define a name, such as ‘Profit’ that is scoped to Sheet1, Sheet2, and Sheet3 in the same workbook.
Although each name is the same, each name is unique within its scope. You might do this to ensure that a formula that uses the name ‘GrossProfit’, for instance, is always referencing the same cells at the local worksheet level.
You can even define the same name, ‘Profit’, for the global workbook level, but again this scope is unique. In this case, there may be a name conflict. To resolve this conflict, Excel uses the name that is defined for the worksheet by default.
The local worksheet level takes precedence over the global workbook level. This can be circumvented by adding the following prefix to the name, eg rename it ‘WorkbookFile!Profit’ instead.
It is possible to override the local worksheet level for all worksheets in the workbook, except for the first worksheet. This will always use the local name if there is a name conflict and cannot be overridden. All names created are assumed to be global by default, until the same name is used on a second worksheet.
Care with names
The Name string must begin with a text or underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters. Spaces are not allowed but two words can be joined, or with an underscore (_) or period (.). For example, to enter the Name ‘Cash Flow’ you should enter ‘Cash_Flow’ or ‘Cash.Flow.’.
You cannot use a name that could otherwise be confused as a cell reference, for example, A1, as this is already a cell reference. This has become problematic for some files originally created in Excel 2003 or earlier, believe it or not, as a common range name was Day1 – but this is now a cell reference.
There is no limit on the number of names you can define, but a name may only contain up to 255 characters (why on earth you would want something this long is beyond me).
Names can contain uppercase and/or lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the global name ‘Profit’ and then create another global name called ‘PROFIT’ in the same workbook, the second name will replace the first one.
It is not a syntax issue, but I strongly recommend thought is given to adding prefixes to range names. A cursory glance at some of my example Excel files will reveal that my list range names always begin with ‘LU_’ where ‘LU’ stands for ‘Look Up’. Similarly, I use ‘BC_’ for ‘Base Cell’ when working with the OFFSET function, etc.
By using these prefixes, I understand the purpose of the range name and so that names with a common purpose are grouped together in a list. This is not to say all range names should contain a prefix. ‘Tax_Rate’, for instance, makes sense on its own and adding a prefix would only detract from the name given, potentially confusing the end user.
Creating Range names quickly
There is a nifty shortcut for creating range names using existing names. Consider the following list:
Imagine you were to highlight cells B2:B8 in the above example and then use the shortcut CTRL + SHIFT + F3:
With the first check box (‘Top row’) checked, by clicking on ‘OK’ the range B3:B8 (ie not including B2) will be named ‘Phonetic_Alphabet’ (ie the underscore will be added automatically). Ranges across rows can be named in seconds similarly using ‘Left column’ similarly.
The reason this dialog box uses check boxes (rather than option buttons) is to allow users to select more than one at a time. For example:
Highlighting B2:F5 and using the keyboard shortcut CTRL + SHIFT + F3 once more should generate the Create Names dialog box as above with both ‘Top row’ and ‘Left column’ checked. This means that C3:C5 will be called ‘Jan’, C4:F4 will be called ‘COGS’ and so on. This would take considerably longer to perform manually.
This example also illustrates why spaces are illegal characters in range names (and for that matter, should not be added to formulae either). Space is the intersect operator in Excel. If you were to type the following formula:
Excel would return the value in cell D5 (the intersection of the two ranges, above), ie $4,183. This can be a powerful yet quick and simple analytical tool for key outputs.
Using range names quickly
One of the reasons I like using the CTRL + F3 shortcut is that it is part of the F3 ‘Names family of shortcuts’. We have just seen how CTRL + SHIFT + F3 can be useful – and so can F3 on its own.
Perhaps superseded by the fact that in Excel 2007 and later versions Excel will now prompt as you type formulae, F3 has been very useful in the past as the ‘Paste Names’ shortcut. For example, as you type a formula you can refer to a range name by simply typing F3 to get the Paste Names dialog box, viz.
Selecting one of the cells and clicking ‘OK’ inserts the range name.
However, look closer at the dialog box. The ‘Paste List’ button in the bottom left hand corner, if depressed, will paste the list and their definitions into a pre-selected range of cells in an Excel worksheet which can be invaluable for model auditing purposes.
Sometimes, formulae have been written before the range name was created. In some circumstances, it is possible to apply these names retrospectively using ‘Apply Names’ within the ‘Defined Names’ group of the ‘Formulas’ tab, viz.
Note that the keyboard shortcut ALT + I + N + A will work in all versions of Excel. Selecting the required range names in the resulting dialog box will see formulae on the active worksheet(s) updated accordingly:
Deleting range names
If I got paid just £1 for every time I have been asked how to delete range names I would probably have retired by now. This was chiefly attributable to the counter-intuitive menu in Excel 2003 and earlier versions:
From the resulting dialog box, you would then select the range name (unfortunately, only one at a time could be selected) and hit ‘Delete’, viz.
Excel 2007 onwards has made this much simpler. In this case, users are more likely to go to the ‘Name Manager’ rather than the confusing ‘Insert’ drop down menu:
The other marked improvement is that multiple names may be deleted simultaneously by using the CTRL or SHIFT buttons to make multiple selections before hitting the ‘Delete’ button.
Do be careful if you delete ranges in Excel that are used for name ranges. The range names will not be deleted (even though they will no longer appear in the Name Box). They will need to be deleted as described above in order to cause potential errors in formulae, etc.
By default, range names are referenced absolutely (ie contain the $ sign so that references remain static). However, imagine a scenario where you are modelling revenue and you wish to grow the prior period value by inflation (already given a range name, say cell C3 on Sheet1). Simply click on any cell (for example, I will use D17 arbitrarily), then define the new range name as follows:
Note the ‘Refers to:’ entry. Cell C17 (the cell to the left of D17) has been chosen without the dollar signs. This is a relative reference. Once we click on ‘OK’, the range name ‘Prior_Period’ will be defined as the cell immediately to the left of the active cell. We can then inflate values easily by copying the formula
across the row, etc.
This article discusses just the tip of the names iceberg. Experimenting can pay big dividends. The aim is not to go overboard, however, as a preponderance of names in a workbook may actually make formulae – and hence your model – more difficult to follow.
Liam Bastick is author of an Introduction to Financial Modelling, which provides a simple walkthrough of the common perils and pitfalls of financial modelling. Presently, this book is only available on Amazon Australia but if anyone would like to buy a copy directly contact Liam via this link. AccountingWEB readers receive a 10% discount plus p&p (including tracking and insurance at cost).