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:
If we named the A2:B45 range on the Party Codes worksheet as “PCODES”, we could instead use:
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)
- Highlight the Excel range or cell you wish to name.
- Go to the NAME Box where you normally see the cell address such as A1:
- 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 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.
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.
- Highlight your range as normal.
- Press Ctrl + Shift + F3.
- 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.
- Your name can’t have spaces. This means I couldn’t use “Political parties.” However, I could use “Political_parties.”
- 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.
- Names can’t be longer than 253 characters.
- 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.
- Click the Formulas tab.
- Click Name Manager.
- 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 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.
You Might Also Like These Tutorials
Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2021-04-18