Establishing a Naming Convention

Nate is designing a large workbook that has dozens of worksheets. He needs to create many names in the workbook for things like ranges, tables, formulas, and constants. Nate knows he won't be the only one to maintain this workbook down the road, so he's trying to develop an understandable naming convention for the names. This makes him wonder what the best practice is in this area.

The problem with a phrase such as "best practice" is that it makes people think there is a single, best approach when that may not be the case. It certainly is not the case when it comes to determining a naming convention like Nate desires. In fact, there are several different approaches you can take to naming, as will become apparent.

Every naming convention involves the use of a prefix of some sort that indicates the type of element to which the name applies. For instance, you might prefix formulas with the letter "f" or the letters "fo" and tables with "t" or "tbl." It doesn't matter if you use one, two, or three characters; there are pros and cons to each approach. The idea is that your prefix should be understandable and unambiguous.

Your prefix is typically followed by a word or two that details the purpose of the item. This is where some folks have formalized notation. Since Excel names cannot contain spaces, notation typically differs in how capital letters and underscores are used. Here is an example of what is often referred to as Hungarian Notation in names:

rngStartDates

Note that the prefix is all lowercase and the purpose words each start with a capital letter. The purpose words should be clear, meaningful, and concise. Sometimes you may find it more understandable to separate the prefix from the purpose words with an underscore:

rng_StartDates

The use of underscores in this manner is sometimes referred to as Snake Case, though technically Snake Case doesn't do prefixes or any capitalization:

start_dates

You can discover more about the formal names, such as Hungarian Notation and Snake Case, at this site:

https://www.encyclopedia-excel.com/vba-naming-conventions-how-to-write-good-variable-names-vba-tutorial

Note that this site is about naming variables in VBA, but the concepts are just as applicable to naming objects and elements in an Excel workbook. Personally, I find that underscores between the prefix and the purpose words are advantageous if your prefix is only a single character or two. For instance, if you are using a single letter for tables, such as "t" or "T," then I find the second and third names here clearer than the first:

tSeasonAdjustments
t_SeasonAdjustments
T_SeasonAdjustments

The underscore may be overkill, however, if you use three lowercase characters for the prefix:

tblSeasonAdjustments

This boils down to personal preference and what you find easiest to implement and understand. With or without an underscore, you could use the following as prefixes:

• rng (range)
• tbl (table)
• col (column)
• row (row)
• fn (function)
• frm (formula)
• cnst (constant)
• lst (list)
• cht (chart)

Besides a prefix and purpose words, you may also want to include an indication of where the object or item is located. For instance, if a constant that defines the target margin is located on a worksheet named Details, then you might name the constant in this manner:

cnst_TargetMargin_Details

The structure and purpose of each name is, again, entirely up to you and what makes sense. It can be especially important to include a worksheet name in a name if the worksheet is normally hidden.

When you define names, there are several places that this can be done, depending on what is being named. The Name Manager is the key place if you are naming ranges, constants, and formulas. However, tables are named using the Table Name box on the Table Design tab of the ribbon, charts are named using the Name Box, and other objects may be named in other places.

I should also mention that if there really is a "best practice" in naming conventions, that practice would be consistency. Once you settle on an approach to naming things, you need to consistently apply that approach. If you fail to do so, then you really don't have a convention, and your names will be a mess.

Finally, once you decide on a naming convention, you'll want to document it somewhere. If you are developing a naming convention that will be used in a single workbook, then you should consider documenting it in a worksheet within that workbook. (You can even hide the worksheet if you think it might otherwise detract from the use of the workbook.) If you are developing a convention that you will use in multiple workbooks, then create a separate document that shows your convention and explains why you made the decisions that you did. Any documentation you put together will be invaluable for people who may need to maintain your workbooks in the future.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10055) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.