How to Manage Range Names in Excel
Range names in Excel 2007, 2010 and 2013 assign titles to groups of cells. Naming a range won’t affect the data itself, but lends a hand in clarity -- consider reading the formula “Sum(Sales)” compared to “Sum(A1:B15).” The Name Box next to the Formula Bar displays the name of the currently selected range and provides a shortcut to create new names. To see and manage all names in the current workbook, open the Name Manager from the Formulas tab.
Select a range of cells and type a name in the Name Box, located left of the Formula Bar, to create a range name that works across the entire workbook.
Open the “Formulas” tab and click “Create from Selection” in the Defined Names section to automatically generate a range name based on existing data. Choose the location of the name you want to use, such as “Top Row” to create a name based on a column title.
Press “Name Manager” to see a list of existing names and modify them.
Select a name and press “Delete” to erase it, without affecting the data within the range. Press “Edit” to modify an existing range.
Change the name of a range in the Edit Name dialogue box or change the included cells by editing the Refers To line. The Comment section does not affect any data or display, but provides a note space to help keep track of ranges.
Click “Filter” in the Name Manager to filter the list of names, such as to focus on names with errors or those that apply only to specific worksheets. Filtering out names does not disable them; it merely hides them from the list. To show all names, choose “Clear Filter.”
Tips & Warnings
- To create a range that works for only a single sheet, rather than an entire workbook, press “Define Name” on the Formulas tab and change the scope from “Workbook” to a sheet’s name.
- Reference a named range by inserting the name after an equals sign in the formula, such as “=Numbers.” As a shortcut, click “Use in Formula” on the Formulas tab to see a list of all named ranges and click one to insert it.
- To reference a range in another workbook, use the notation “workbook.xlsx!Name.”
- If you change the name of a range referenced in a separate workbook, the reference stops working. Either update the name in the reference by hand or click “Edit Links” on the Data tab.