One overlooked feature of Excel is the ability to create named ranges. A named range is a workbook object that allows you to refer to a cell or a range of cells with a descriptive name rather than a cell reference. When you change the cells that a named range refers to, the formulas that use it are automatically updated. In this tutorial, I’ll show how to name a range in Excel.
Formula Example with Names
To show the difference, in our Excel 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 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.
Keyboard Shortcut Method
If you’re a keyboard maven, you may prefer to save some steps and 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 four.
- 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: if you delete a NAME 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 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.
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.