3 reasons to use named ranges in Excel
What is a named range?
Cells are generally named for a column and row that intersect, like A1 or C3. A range of cells is indicated by the colon between the beginning and ending cell of the range, like A1:C3, which would include all nine cells in between. A named range substitutes a user-specified name, like “Scores,” for score numbers typed in cells A1 through C3.
Creating a named range
Select one or more cells, and then click in the Name Box. It’s located left of the formula bar at the top of the worksheet, below the Ribbon. If you’re not sure you’re in the right place, hover and the Name Box tip will pop up.
1. Data Validation
Using a named range to create a dropdown list in a cell makes data entry easier and cleaner.
Without a named range, the list of acceptable choices must be in the same worksheet as the target cell. Using a named range allows you to put that list anywhere in your workbook.
Making a tab called “lists,” which holds all the named ranges used in selection lists, is helpful.
- From the Data tab, in the Data Tools group, choose the Data Validation button.
- Choose List selection in the Allow: field.
- In the Source field type an “=” and the name of your range as illustrated.
The easiest mistake to make here is to forget the equals sign.
2. Formulas
A named range is the best choice when using an Absolute Cell Reference ($A$1) to refer your formula to the exact same cell (no matter where it’s copied).
In a VLookUp, for example, forgetting to set your Table Array to an absolute cell reference range can yield unpredictable results.
Simply setting a name range for your table array makes worrying about an absolute cell reference unnecessary. Even naming single cells can be helpful in creating a self-documenting formula.
To see a great example of this, look at the Installed Templates (called Sample Templates in Office 2010). Unprotect the worksheet, and click in the Payment formula.
3. Bookmarks
If you have elaborate workbooks, which contain target figures like net profit, average cost and gross income, you can highlight the result cells and name them for what they represent. Then, click in the name box from anywhere in the workbook, select the name, and it will take you there.
