Jun 102012

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010

This is part 2 of a 2 part post. Continued From Defined Names: Naming Conventions (Part 1)


When implementing a naming convention, one has to consider a number of issues pertaining to the logic of what is being named and the environment in which the naming convention applies.

As Defined Names are usually presented in an alphabetical list, it makes sense to create a naming convention that takes this into consideration and makes it easy to group names together logically. For this reason, I like to use a military naming technique which often means creating names in reverse.

Let’s say we have 4 ranges that refer to dates and we want to name them all then we may wish to group them together as they all refer to types of dates.

These ranges are on the Trades sheet and are scoped to the Workbook.

Order Date
Trade Date
Fixing Date
Settlement Date

We could name these cells:


However, if we have a number of other Named Ranges defined on this worksheet, these names will be interspersed with other names not relating to dates. If on the other hand, we name them in reverse, then they will all be grouped together e.g.


On the other hand, if you were creating a number of Named Ranges that related to Orders such as the Order Number, Order Time, Order Taker then you may wish to group all the names referring to Orders together and likewise do the same with Trades, and Settlements. It really depends on how you wish the names to be grouped.

Avoiding Common Roots

This point is less relevant in Excel 2007 and above simply because it is now possible to rename a Defined Name. In Excel 2003, if you wished to rename a Defined Name, you had to add a new one, then delete the old one and use the Excel Find and Replace method to replace all uses of the old name with the new one. However, even in Excel 2007 and above, you may wish to be able to use the Find and Replace method with absolute confidence that only the Defined Name that you wish to replace will be replaced and nothing else.

Excel’s Find and Replace has an option to Match Case and Match Entire Contents of Cells. Unfortunately there is no option to match entire words! If you are replacing components of formulas, then the option to Match Entire Contents of Cells is not very helpful.

Now let’s image we have two Named Ranges, one called AA_TRA_Currency (i.e. the trade currency) and another called AA_TRA_CurrencyReporting (i.e. the reporting currency of the trade) and one called TRA_CurrencyDefault (i.e. the default currency of the trade). We decide that we need to substitute the use of AA_TRA_Currency in a number of formulas with TRA_CurrencyDefault. We thus select the range, or the entire sheet if needs be and use the Find and Replace method to find all occurrences of AA_TRA_Currency and replace them with TRA_CurrencyDefault. However, Excel will also find TRA_Currency in all occurrences of AA_TRA_CurrencyReporting and replace them with TRA_CurrencyDefault, resulting in TRA_CurrencyDefaultReporting. This is obviously not what you want and will result in a #NAME error.

Hence the danger of using a name which is the root of another name. In Excel 2007 and above, in order to check for this, you can simply open the Name Manager and filter all the workbook names. Then scroll through the list. As all the names will be in alphabetical order, it will be very quick to see where this issue occurs. Simply rename the culprits. In Excel 2003, it is almost as easy to find the culprits by going to the Define Name dialog. But it is not as easy to fix them.

Note: In Excel 2003, if you wish to filter out all the worksheet names so you only have workbook names, then you can add a new blank sheet and open the Define Names dialog. As the worksheet will have no names defined, the list should consist only of names scoped to the workbook.


When I wish to make differentiations between similar names I often use Suffixes. i.e. append a short code to the end of the name as a differentiator.

An example may be that we have two types of currency code throughout our workbook. We wish to record the Transactional Currency as well as the Reporting Currency.

Lets say we have a worksheet for transactions and two columns, one for Transactional Currency and one for Reporting Currency. I would name the columns as follows:

Transactional Currency: AA_TRA_DateTrade
Reporting Currency: AA_TRA_CurrencyREP

I usually put the suffixes in capitals to make them stand out. You can chose anything for the suffices as long as you are consistent throughout the workbook or suite of workbooks if you are working with more than one.

Standardised Worksheet Names

Usually, when I am creating an Excel application or model, I like build consistency between most of the worksheets. I say most here as sometimes it is just not possible to do it across every worksheet. But as far as possible, I like to have a common structure.

In these cases, it is very helpful to define some standard worksheet names that will appear on each of these worksheets. The benefits of this are as follows:

  1. You can create common vba code that can use these names when you call the code on that worksheet. Thus, this code can run on any worksheet without modification.
  2. You can have generic formulas that can be copied from worksheet to worksheet and will always give the correct result as they will refer to the worksheet names that are consistent across all these worksheets.
  3. You can create consistent conditional formatting and data-validation based on formulas that will work when you copy them between worksheets.
  4. It helps to maintain overall consistency in your model which makes it much easier to manage and maintain.

When I create Standardised Worksheet Names, I modify my naming convention slightly so I can easily differentiate these names. Lets say every worksheet has a Title Row and Summary Row as a Is Valid column. I would name these as follows:

Title Row: BBB_Title
Summary Row: BBB_Summary
Is Valid: BBB_IsValid

Note the extra B in the name i.e. a prefix of BBB_. This will then push all the Standardised Worksheet Names together below the normal worksheet names.

Row and Column Suffixes

Most of my Named Ranges refer to single cells, whole rows or whole columns. Some people like to ad a suffix to indicate this. However, I think this is unnecessary and adds extra superfluous information to the name as it is very easy to see what a name refers to in all versions of Excel.

Use of Numbers in Names

Often one needs to add a series of names that are best differentiated by a sequence number.

Lets say we have three grouping levels Group 1, Group 2 and Group 3 and we wish to create three named columns, one for each. If these were worksheet names, we could just call them:


However, sometimes, the number of columns named like this may exceed 9. In this case, we would have:



The problem with this is that Excel will sort Group10 after Group1 and before Group2 which can be extremely annoying. For this reason, I usually make all numbers into 2 digit numbers and prefix the numbers 1-9 with a 0 e.g:



This way, the names will always be in sequence. It is unlikely that you would exceed 99 as this would likely point to an inappropriate structure for your model. But is it not uncommon to exceed 9 in which case, a 2 digit number is sufficient.

Furthermore, don’t just look at what you need today. Always plan for changes in the future.

  6 Responses to “Defined Names: Naming Conventions (Part 2)”

  1. Have you come across this scenario. I have a worksheet with the range name Database so that it works with the ShowDataForm method. All good with the English version of Excel however when I send this to my contact in Peru who uses the Spanish version of Excel 2003, the range name Database is auto translated into the non complient Base de datos. So the macro that calls ShowDataForm fails because there is no longer a Database range name.

    Seems that I am going to have to create a custom form instead of using the built in function ShowDataForm or get my South American friends to change to the English version of Excel.

  2. as an alternative to your “BBB” situation:
    you can define names that refer to a given range of the current sheet

    what you need to do is remove the sheet reference of a workbook name but leave the !
    I know, sounds weird

    so “=!$A$1” will always refer to cell A1 of the current sheet
    “=!$A1” will always refer to the leftmost cell of the current row (assuming you’re in row 1 when you defined it)

    this seems to be totally undocumented, so I’m not sure if this is standard functionality or an Excel glitch, and it may disappear in future versions

  3. While am creatin dynamic excel with sheet name ‘CH092240’ it is creating as ‘_Ch092240’. May i know why the underscore(_) is adding before the mentioned sheet name.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>