Creating named ranges, and their benefits.
Where/when to use the technique
Named ranges help make formulas easier to audit, give us quick ways to select key data, and can be useful for identifying ranges used for PivotTables, charts and more.
Benefits of naming ranges
- Gives us a quick way to select individual or groups of important cells
- Allows us to quickly identify which cells are used in a formula, PivotTable, or chart
- Can make our formulas easier to read
Naming individual ranges
- Select the cell
- Type the name (for example “Tax_Rate”) in the Name box
- You can now select “Tax_Rate” from the name box and be taken immediately to that cell
- You can also use “Tax_Rate” in formulas (i.e. = A2*Tax_Rate)
Naming groups of cells as a range
- Select the cells
- Type the name (for example “Data_Table”) in the Name box
- You can now select “Data_Table” from the name box and be taken immediately to that cell
- You can also use “Data_Table” in formulas (i.e. =VLOOKUP(Tax_Rate,Data_Table,2,FALSE)
Modifying named ranges
- To modify or delete a named range, go to the Formulas tab and choose “Define Name”
- Select your named range and click Edit or Delete
Note for Windows Users
- If you are coming from the Windows Excel world and are looking for the Name Manager on the Formulas tab, this doesn’t exist for Mac Excel. The closest feature in Mac Excel is the Define Name dialog.
Lesson notes are only available for subscribers.