Assigning a Range Name in Excel

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

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.

How to Assign a Name

  1. Highlight the Excel range you wish to name.
  2. Go to the NAME Box where you normally see the cell address such as A1:
  3. Highlighting Excel range

  4. Type in the NAME you wish to use for the highlighted range. In our example, we would overwrite the A1 with PCODES. The names are case insensitive.

From now on, you can use this range name in any formulas. For examples, 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).

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

Rules for Excel Names

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

  1. Your range name can’t have spaces. This means I couldn’t use “Political parties”. However, I could use “Political_parties”.
  2. Your range name can’t be a cell address. I couldn’t use a NAME of “B45” or R45C2 since these are valid Excel cell reference.
  3. Names can’t be longer than 253 characters.

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 Names

If you want to see all the defined names for an Excel workbook, you can find a listing.

  1. Click the Formulas tab
  2. Click Name Manager

Excel name manager

A dialog appears listing the Name and cell references for each one. You can also delete items from here as well.

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 left before voting starts.

As you can see Excel’s name feature is flexible and can make formulas easier to construct 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.