You can name parts of a spreadsheet and then use the name in formulas and other Excel features. Using a naming convention make things much easier to follow and adapt in the long run. I will share some suggestions for naming ranges.

To be accurate range names are formula names. The common term however is a range name. Excel calls them Defined Names. See below for the Formulas ribbon tab section devoted to Defined Names.

You can name a cell or a range. Once you’ve created the name, you can use it with.

• Formulas and functions
• Macros
• Conditional Formatting
• Data Validation
• Hyperlinks
• And other Excel features

Range Name Rules

There are a few strict rules that apply to range names. Excel enforces these rules.
They:

1. Cannot start with a number.
2. Cannot contain a space. They can contain the _ underscore character or the full stop.
3. Cannot be a cell reference. Qtr1 is a cell reference. Qtr_1 or Qtr.1 are not cell references.

Range Name Suggestions

These are my suggestions. You are responsible for keeping a consistent naming convention.

1. Use at least one capital letter. Excel uses the capitalisation if it recognises a name in a formula. If you enter a name in lowercase and if it stays in lowercase after you press Enter it means the name doesn’t exist, or you typed it wrong. This is useful for debugging formulas if you use a lot of range names.
2. Don’t use ALL CAPITAL letters. It may be confused with a function name or a column reference. Both GST and TAX are column references.
3. Capitalise the first letter of any word. This makes it easier to read the name. eg TaxRate vs Taxrate.  This is sometimes called camel case.
4. Use prefixes and suffixes. When working with names using a system of consistent prefixes and suffixes can make using the names easier. E.g. actAmount, budAmount and fcastAmount. For actuals, budget and forecast amounts.

Prefixes

Using a consistent prefix has the added benefit of listing the names together in the Name Manager dialog – discussed below. This also applies to the Formula Bar when you start typing the name.

Custom functions

I have written many blog posts on Excel’s new custom functions. A few are listed below.

Avoid the #DIV/0! Error in Excel

Custom Function for Age in Excel

Custom Function to Reverse a String in Excel

Custom functions require a range name. I recommend using the fn prefix in lowercase and all uppercase for the function name. Some examples fnDIVIDE. fnREVERSE and fnSHEETNAME.

Some people use the prefix fx. Whichever you use, be consistent.

Hyperlinks

Hyperlinks in Excel are easy to break. A change of a sheet name can cause a hyperlink to break. Using range names for hyperlinks makes them less likely to be broken. I use the prefix hl or link for my hyperlinks. A previous blog post explained how to create a nearly unbreakable hyperlink.

Excel – Unbreakable Sheet Hyperlink [VIDEO]

F3 Function Key

The F3 function key is dedicated to range names. Pressing F3 opens the Paste Name dialog.

Here are two other F3 key shortcuts.

Ctrl + F3 – opens the Name Manager dialog.

Ctrl + Shift + F3 – opens the Create Name from Selection dialog.

Name Manager

This dialog displays all the names. You can add and delete names from this dialog.

Formatted Tables

As well as range names there is another naming system within Excel. It involves formatted tables. Formatted table names are like range names. I use the prefix tbl for all my formatted table names. This differentiates them from range names. They are both listed in the Name Manager and display in the Formula Bar.

Range Name List

You may have noticed the Paste List button on the Paste Name dialog. Clicking the Paste List button will insert a listing of the names with their formulas in the current cell. This is a useful documentation feature. It is not dynamic, if you make changes you will need to redo the list.