This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010
I have covered the basics of Defined Names in a series of three Posts named Defined Names 101. However, in order to use them effectively, you really need to use a strict naming convention for the following reasons:
- It makes it easier to manage large numbers of names.
- It separates the names you define from the names that Excel creates automatically.
- It makes it easy to differentiate between names scoped to the workbook and names scoped to the worksheet.
- It makes it easy to use the auto matching in Excel 2007 and above when typing names directly into formulas.
- It makes it easy to find names as they are logically ordered.
- It makes it safe to use Excel’s Find & Replace to make global changes to your workbook.
In general, a name should be clear and descriptive and should avoid using excessive abbreviations. The days when we need to abbreviate names have long gone; a fully descriptive name is far easier to read and understand. From Excel 2007, all names lists (including the Name box) can be expanded to show the full name without truncation so there is no reason to over-abbreviate. However, if you do abbreviate, make sure that you use generally accepted abbreviations or ones that are easy and clear to understand by someone other than the spreadsheet developer.
Use of Capitals
I recommend that all names have at least one capital letter. This is because Excel does not differentiate between lowercase and uppercase but will convert the characters of any name you type in a formula to match the case that the name has been defined with. The following table will illustrate this.
|Defined Name||Typed||Converted By Excel|
The benefit is this: when you type a name in lowercase, if Excel recognises it, the name will be automatically changed to match the capitalisation of the Defined Name. This helps to validate the name as you type. If you misspell it or type in a name that does not exist, Excel will not match it and the letters will not be capitalised.
However, I do not recommend using all capitals for names as Excel functions are all capitalised and it helps to differentiate names from functions if names are not fully capitalised. In addition, it makes it easier to use the preferred method of Word Separators as detailed below.
Use of Word Separators
When creating descriptive names that contain more than one word, a separator should be used. Excel does not accept spaces so there are three options available:
- First letter capitalisation: Join the words together but Capitalise the first letter of each word e.g. TotalRevenue. This is clean and easy to read and is my preferred method. It does not add unnecessary extra length and is clear to read.
- Use of an underscore (_). Separating words with an underscore works well as shown by these examples: total_revenue, Total_Revenue or TOTAL_REVENUE. However, the underscore does add unnecessary length to the name and also makes it hard to differentiate the Name Prefix (see next section).
- Use of a period (.). Separating words with a period is clean as shown by these examples: total.revenue, Total.Revenue or TOTAL.REVENUE. However, periods are used in many places in Excel especially in numbers, web addresses and general text so I prefer to avoid them in Defined Names. Furthermore, if you type a name that is similar to a range address eg CU1.A1 then Excel will convert that period to a colon (:) and the name will be invalid.
Use of Name Prefix
When defining names, I always recommend using a standard prefix that separates your names from other Excel names or functions. Names are usually listed alphabetically so first you need to decide if you want your names to appear at the top or the bottom of any display list.
When I create my own models or Excel Applications that will not have any other user’s names added, I like to force my names to the top of every list. If, on the other hand, I am building a template that others will extend and to which they will add their own names, then I want these names to come at the bottom of any display list to keep them well out of the way.
Prior to Excel 2007, managing names was quite onerous and it was not possible to rename a name. You had to create a new one, delete the old one and then do a global replace of the old name with the new name. Excel 2007 made this far easier with the Name Manager. However, you still don’t want to be renaming a large number of names regularly so it is important to get it right early on. Also, you want to be able to use the global replace method safely if needs be and ensuring that your name is unique throughout all the text in your workbook is of paramount important.
Prefixs – Part 1 (Order & Scope Prefix)
To force names to the top of a list, you may be tempted to prefix them with an underscore (_). Although I have shown this in a previous post illustration, it is not what I recommend simply because it is the first choice that people use to push something to the top of a list and also, because an underscore is harder to type as it requires the shift key. Furthermore, I like to differentiate between my Workbook names and my Worksheet names so I need something more than a simply underscore.
Thus, I usually prefix all workbook names with “AA_” and all worksheet names with “BB_”. Why capitals? Well, remember that names are not case sensitive. As explained above, we can use Excel’s automatic case matching to help validate the name visually.
“AA_” pretty much guarantees that your names will always appear at the top of every list. Remember that workbook names appear on every sheet but worksheet names only show when the worksheet is active. Thus, it makes sense to force the workbook names to the top of the list, followed by the worksheet names. I use “BB_” for the worksheet names. This way, the worksheet names come down below the workbook names.
Why “AA” and “BB” and not just “A” or “B”? I like to create a name that is unique. If I am typing into a formula and I type in “a” then Excel will match all the names and functions that start with an “a” or and “A”. Thus, my names will still get mixed up with Excel’s function names which I don’t want. By using “AA”, I can quickly type it in to a formula and Excel will filter out everything that is not one of my names. How many functions begin with “AA”. The same can be said for “BB”. This greatly increases the usability of Excel’s auto-match.
Why the “_” after the AA or BB. Firstly, it acts as a separator making it easier to differentiate the prefix from the actual descriptive name. Secondly, it is possible that in your workbook, you will have words that contain “aa” or “bb”. I want the ability to make global replacements to my names and I need to ensure that there is no way, Excel will replace any text other than my Defined Name. By using a prefix like “AA_” it is highly unlikely that anything other than one of my names will match that text string. Thus, I can safely use Excel’s Find & Replace method to make global changes to my formulas.
So now we can have a workbook name like: AA_TotalRevenue
Prefix – Part 2 (Worksheet Name Prefix)
Workbook names, although scoped to the workbook, still, in most situations, refer to a cell or range on a specific sheet. In order to be able to understand and manage my names easily and logically, I like to know which worksheet they refers to. I have a coding convention for worksheets as well (more on that here).
In this example we will assume a small workbook with only five worksheets with a simple set of financials, e.g. Defaults, IncomeStatement, BalanceSheet, CashFlow and Summary. I would use a simple code like “DEF”, “INC”, “BAL”, “CAS” and “SUM” to refer to the key sheets.
Thus, for any workbook names that refer to the Defaults worksheet, the name would begin “AA_DEF_”. For the IncomeStatment worksheet, it would be “AA_INC_”. Thus, I know from the name that it is a workbook name referencing the IncomeStatement worksheet and when I am scrolling through a list or directly typing into a formula, I can instantly locate all the workbook names referring the Income Statement worksheet.
Suppose I have a row on the IncomeStatement worksheet representing “Total Revenue”. I would create a workbook name as follows:
From this name, I know it is a workbook name on the IncomeStatement worksheet representing the “Total Revenue”.
This simple naming convention for workbook names gives all the benefits listed above.
Although it is possible to create a Named Range scoped to one worksheet that actually references another, it is very rare and not generally recommended. Thus, in most cases, a name scoped to a worksheet, will also refer to a cell or range on that worksheet. So in these cases the worksheet name prefix can be skipped.
Suppose we have a worksheet called IncomeStatement with a row that represents the Total Revenue. The name would be:
As this name will only show up in the Name box, Go To dialog and Paste Names dialog if the IncomeStatement worksheet is active we do not need to include a worksheet prefix. Furthermore, we actually don’t want to include a worksheet prefix as one of the key benefits of worksheet names is that many worksheets can have the same names which would not be possible if they had a worksheet name prefix.
This is part 1 of a 2 part post. Continued in Defined Names: Naming Conventions (Part 2)