Excel Named Range: Your Key to Formula Simplicity

Ever wondered how to make your formulas easier to read and manage? Excel named ranges are an easy solution. Using descriptive names instead of cell references makes your spreadsheets more intuitive. In this tutorial, I’ll show how to create named ranges in Excel.

What is a Named Range & Benefits?

In simple terms, a “named range” is an Excel feature that allows you to apply a descriptive name or nickname to one cell or a cell range. For example, you could assign a name to a cell containing a sales tax percentage. Or, you could assign a name to a range of cells that equate to different states. Using these names instead of cell references offers some benefits:

• They can make formulas more readable.
• The named ranges are easier to use once set up.
• Formulas are easier to maintain when named ranges are used.
• They can make navigation easier for large datasets.

Excel Formula Example with Named Range

To show the difference, in our Excel VLOOKUP tutorial, we used the following:

`=VLOOKUP(C2,'Party Codes'!\$A\$2:\$B\$45,2,FALSE)`

If we named the A2:B45 range on the Party Codes worksheet as “PCODES,” we could instead use:

`=VLOOKUP(C2,'Party Codes'!PCODES,2,FALSE)`

Besides being easier to read, we no longer have to worry about adding the \$ to indicate absolute references. And even though the name makes you think it involves multiple columns, you can name a single cell.

How to Create a Named Range in Excel

1. Highlight the Excel range or cell you wish to name.
2. Go to the NAME Box where you normally see a cell address, such as A1:
3. Type the NAME you wish to use for the highlighted selection. In our example, we overwrite the A1 cell reference with PCODE. The names are case-insensitive.

From now on, you can use this PCODE range name in any Excel formula. So, for example, if I created another NAME called “registered,” which included the number of registered voters for each party, I could use a formula such as =SUM(registered). Then, as I type the name, Excel shows it in the drop-down menu with a special icon.

You can also jump to this range by clicking the drop-down arrow to the right of the NAME box and selecting your item.

Named Range Shortcuts

If you’re a keyboard maven, you may prefer to save some steps and use the keyboard shortcut to create your named range.

1. Highlight your range as normal.
2. Press Ctrl + Shift + F3.
1. Press OK.

This will use the Top Row column label of President as the new named range.

Rules for Excel Named Ranges

Yes, most shortcuts usually have rules. Fortunately, there are only four.

1. Your name can’t have spaces. This means I couldn’t use “Political parties.” However, I could use “Political_parties.”
2. Your name can’t be a cell address. I couldn’t use a NAME of “B45” or R45C2 since these are valid Excel cell references.
3. Names can’t be longer than 255 characters.
4. Names can apply to a single worksheet or workbook.

Actually, there is another rule: if you delete a NAME referenced in a formula, it will break. No big surprise there.

Finding and Deleting Named Ranges

You can find a list in the Name Manager if you want to see all the defined names for an Excel workbook.

1. Click the Formulas tab.
2. Click Name Manager.
1. A dialog appears listing the Name and cell references for each one. You can also delete items from here as well.

This dialog also allows you to edit a Name or add a descriptive comment.

Another way to access Name Manager is with the keyboard shortcut Ctrl + F3.

Although we’ve shown names that include a range of cells, you can also use an Excel name for one cell. For example, you might create a name called “election_date” and use it in a formula that computes how many days are left before voting starts. In this scenario, the name is like a constant.

Troublshooting Named Ranges

Like many Excel features, sometimes you run into problems, but these can be easily remedied.

• Naming conflicts – you’ll get an error if you try to reuse a name that exists.  For example, in the above picture, I wouldn’t want to have2 named ranges called “population”.
• Scope confusion – when you create a range using Define Name, it can be set for either a worksheet or a workbook. Make sure you have the correct scope setting.
• Referencing Errors – if rows or columns are deleted, it can cause a #REF! error in your formulas. Your cell references no longer exist.
• Moving or Copying Cells -if you move or copy cells, Excel adjusts your cell references but doesn’t adjust named ranges.

As you can see, Excel’s named ranges are flexible and can make formulas easier to build and interpret. Names also make it easier for other people to learn your workbook. If you want to play around with this feature, you can use the sample Excel file we used for VLOOKUP. Create a name and then change the formula to see how they work.