Quick Links
By default, Microsoft Excel’s cells are named by their column-row references, like A1 or B2. However, you can assign a more specific name to a cell or range of cells to improve navigation, make formulas clearer, and save time in the long run.
Why You Should Always Name Ranges in Excel
You might be familiar withbookmarks in Microsoft Word, which are invisible way-points in specified locations of a document that you can jump to whenever you need to. Microsoft Excel’s alternative to this time-saving tool is somewhat unimaginatively called “names,” accessed through the name box in the top-left corner of your workbook.
Let’s say you have a huge worksheet containing lots of rows and columns of data, and you want to jump to a certain cell without scrolling for what seems like forever. The quickest way to do this is to type a cell reference in the name box and press Enter.
Likewise, if you have other active tabs in your workbook, you’re able to jump to any cell within any of the worksheets by typing the tab name, followed by an exclamation mark, and then cell reference. For example, typing:
and pressing Enter takes you to cell H11 in Sheet 2.
While this is all well and good, it’s of little use if you can’t remember which cells contain the information you want to jump to. This is why you should change the names of cells or ranges that you will either visit or use in formulas often.
In this example, I have named a range of cells “Monthly_Totals” that I review all the time. Because I’ve named the range, I can jump to it by clicking the down arrow in the name box, regardless of which worksheet I’m currently working in.
What’s more, I can use this named range in a formula. For example, typing:
adds all the values in this range and provides an overall total.
Select and rename a whole column or row to ensure formulas referencing the named range pick up any added data.
As a result of using a named range rather than direct cell references, I can guarantee that I’ve not accidentally selected the wrong cells. What’s more, because the referenced range has a descriptive name, when I look at the formula, it’s easy to remind myself of how the calculation is made. A third benefit of using named ranges in formulas is that they’re easier to troubleshoot if the figure doesn’t seem quite right.
Another good example of the usefulness of named ranges in formulas is when they form part of a logical test inthe IF function. In this example, by clicking the cell and looking in the formula bar, you’re able to immediately see that the criterion for whether the threshold is met involves the total income. On the other hand, using cell references here would make this less clear:
Finally, named ranges are helpful increating dynamic drop-down liststhrough Excel’s Data Validation tool. In this example, I’ve renamed the whole of column A “Employee_Names.” Now, when I click “Data Validation” in the Data tab on the ribbon to add a drop-down list of names to cell E1, I can specify the source of my list as this named column.
This means that any names I add to column A will be included in the drop-down list.
How to Name a Range in Excel
Naming a cell or a range of cells in Excel is straightforward. Simply select the cell or range you want to name, and replace the cell reference with the desired name in the name box. Then, press Enter.
When you use this method to name ranges manually, there are some rules you must follow for Excel to accept your changes:
you’re able to also force Excel to name a range based on data you already have in your worksheet. Select the range you want to name, and in the Formulas tab on the ribbon, click “Create From Selection.” Then, check the relevant option in the dialog box that appears. In this example, checking “Top Row” will result in the range being named “Employees,” because the column header is included in the selection.
How to Change a Named Range in Excel
Whether you want to change the name of a range or the cells included within a named range, the process is quick and easy.
In the Formulas tab on the ribbon, click “Name Manager.”
Then, double-click the named range you want to modify, or single-click it and select “Edit.”
Next, in the Edit Name dialog box, you can change the name of the range in the Name field, or adjust the referenced range in the Refers To field.
When you’re done, click “OK,” and close the Name Manager dialog box.
If you included the previous name or range in a formula, Data Validation condition, or another reference in your workbook, don’t worry—these will update automatically according to the changes you just made.
Things You Should Know About Naming Ranges in Excel
Before you go ahead and name some ranges in your workbook, here are some points to note:
Naming ranges is just one of many ways tospeed up your workflow in Excel. For example, you could also freeze panes to avoid overscrolling, use the Data Forecast tool to generate projections, and adjust the Quick Access Toolbar to personalize your Excel experience.
