Named ranges are a useful but often overlooked feature of Microsoft Excel. Named ranges can make formulas easier to understand (and debug), make complex spreadsheets easier to create, and simplify macros.
A named range is simply a range (either a single cell or a range of cells) that you assign a name to. You can then use this name in place of the usual cell references in formulas, macros, and to define the source for graphs or data validation.
Using a range name like TaxRate instead of a standard cell reference like Sheet2! $ C $ 11, can make spreadsheet easier to understand and debug / audit.
Using Named Ranges in Excel
For example, consider a simple order form. Our file includes a fillable order form with a drop-down list for choosing a shipping method, as well as a second sheet with a shipping cost and tax rate table.
– /
In version 1 (no named ranges), formulas use regular A1-style cell references (shown in the formula bar below).
Version 2 uses named ranges to make formulas easier to understand. Named ranges also make it easier to enter formulas because Excel will display a list of names, including function names, from which you can choose when you start typing a name into a formula. Double-click a name in the picklist to add it to the formula.
Opening the Name Manager window on the Formulas tab displays a list of the range names and cell ranges to which they refer.
But these ranges have other advantages as well. In our sample files, the shipping method is selected using the dropdown (data validation) in cell B13 on Sheet1. The selected method is then used to find the shipping cost on Sheet 2.
Without named ranges, the dropdown choices must be entered manually because data validation will prevent you from selecting the original list on another sheet. Therefore, all options must be entered twice: once in the drop-down list and again on Sheet2. Also, the two lists must match.
If a mistake is made in one of the entries in any list, then the shipping cost formula will give the # N / A error when choosing the wrong choice. Naming the list on Sheet 2 as ShippingMethods fixes both problems.
You can reference the named range when defining data validation for the dropdown, for example, simply by typing = ShippingMethods in the source field. This allows you to use a list of options found on another sheet.
And if the dropdown refers to the actual cells used in the search (for the shipping cost formula), then the dropdown options will always match the search list, avoiding # N / A errors.
Create a named range in Excel
To create a named range, simply select the cell or range of cells you want to name, then click in the name field (where the address of the selected cell usually appears, to the left of the Formula Bar), enter the name you want to use, and press Enter.
You can also create a named range by clicking the New button in the Name Manager window. The “New Name” window opens, where you can enter a new name.
By default, a named range is set to whatever range is selected when you click the New button, but you can edit the range before or after saving the new name.
Note that range names cannot contain spaces, but can include underscores and periods. Typically, names should begin with a letter and then only contain letters, numbers, periods, or underscores.
Names are not case sensitive, but using a headword string such as TaxRate or December2018Sales makes names easier to read and recognize. You cannot use a range name that mimics a valid cell reference such as Dog26.
You can edit the names of ranges or change the ranges to which they belong using the Name Manager window.
Also note that each named range has a specific scope. Typically, the default scope is Workbook, which means that the range name can be referenced from anywhere in the workbook. However, it is also possible to have two or more ranges with the same name on separate sheets, but in the same book.
For example, you might have a sales data file with separate sheets for January, February, March, and so on.Each sheet might have a cell (named range) named MonthlySales, but usually each of these names would only be scoped to the sheet containing this is.
So the formula = ROUND (MonthlySales, 0) will give February sales rounded to the nearest whole dollar if the formula is on a February sheet, but March sales if on a March sheet, etc.
To avoid confusion in workbooks with multiple ranges on separate sheets of the same name, or simply in complex workbooks with tens or hundreds of named ranges, it can be helpful to include the sheet name as part of each range name.
This also makes each range name unique so that all names can have a Workbook scope. For example, January_Month Sale, February_Month Sale, Budget_Date, Order_Date, etc.
Two caveats regarding the scope of a named range: (1) you cannot edit the scope of a named range after it has been created, and (2) you can only scope a new named range if you create it using the ” New ”in the Name Manager window.
If you create a new name for a range by entering it in the Name field, the default scope will be the workbook (unless another range with the same name exists) or the sheet on which the name is created. Therefore, to create a new named range that is scoped to a specific sheet, use the New button in the Name Manager.
Finally, for those who write macros, range names can be easily referenced in VBA code by simply placing the range name in parentheses. For example, instead of ThisWorkbook.Sheets (1) .Cells (2,3), you can simply use [SalesTotal] if the name refers to that cell.
Start using named ranges in your Excel spreadsheets and you will quickly see the benefits! Enjoy!
–