Quick Intro to Excel Named Ranges

The earlier tutorial I wrote on VLOOKUP prompted more questions. I referred to naming cell ranges. Sure enough, people asked me for more details. Using Excel’s Named Range feature can make creating formulas or finding worksheet data easier. In Excel, we’re used to seeing alphanumeric cell references such as A2:B45. The problem is we can’t identify with what’s in that cell range. But, providing a name such as “PCODES” or “Presidents” provides more context.

Formula Example with Names

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

=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 (Manual)

  1. Highlight the Excel range or cell you wish to name.
  2. Go to the NAME Box where you normally see the 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.
Example of PCODE named range.
Example of named range called PCODE

From now on, you can use this PCODE range name in any formulas. 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). As I start to type the name, Excel shows it in the drop-down menu with a special icon.

Registered named range shows in drop-down.
Named Range shows with different 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.

Keyboard Shortcut Method

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

  1. Highlight your range as normal.
  2. Press Ctrl + Shift + F3.
Create Names from Selection dialog with Top Row checked.
Create Names from Selection dialog
  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 three.

  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 253 characters.
  4. Names can apply to a single worksheet or workbook.

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

Finding and Deleting Named Ranges

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

  1. Click the Formulas tab.
  2. Click Name Manager.
Selecting Name Manager on toolbar.
Name Manager
  1. A dialog appears listing the Name and cell references for each one. You can also delete items from here as well.
Name Manager dialog with 2 entries.
List showing existing named ranges

This dialog also allows you to edit a Name or add 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.

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.

Excel 2019 All-in-One For Dummies
Harvey, Greg (Author); English (Publication Language); 816 Pages - 10/26/2018 (Publication Date) - For Dummies (Publisher)
$18.99

Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-04-18