How to Work with Named Ranges in Excel 2013
Naming a range in an Excel spreadsheet can be helpful because you can refer to the range by a friendly name, rather than by the cell addresses when you construct Excel formulas and functions. That way, you don't have to remember the exact cell addresses; you can build formulas based on meaning.
For example, instead of remembering that the number of employees is stored in cell B3, you could name cell B3 Employees. Then, in a formula that uses B3's value, such as
you could use the name instead:
The main reason for naming a range is to refer to it in a formula. Here's how to get started with naming a range.
Get more information and insight about Microsoft's popular spreadsheet program in the For Dummies Excel 2013 eLearning course. Just visit Excel 2013, fill out a simple registration, and click the Try It! button. Visit Pick your course to have a look at all the Dummies online offerings.
You can name a range by using several different methods.
You can create a named range from a selection, or you can enter a cell name in the Name box to the left of the Formula bar. Another option for naming a range in Excel: choosing the Define Name command.
The Create from Selection command is a handy way to name all your ranges at once.
When you have a series of columns or rows that you want to use as names for cell ranges, you select the cell range, and click Create from Selection on the Formulas tab. In the dialog box, choose the source for your range names.
Check out this video for an example of how to name several ranges at once using the column labels in the top row of a selection.
If you just want to give one cell a more intuitive name, you can select the cell, type the name in the Name box, and press Enter.
The Define Name button on the Formulas tab is another way to name a cell or a cell range.
Click a specific cell, click the Formulas tab, and then click the Define Name option.
The New Name dialog box opens. In the Name text box, type whatever you'd like to call the cell or cell range, and then select a scope from the Scope drop-down list. Click OK to create the name. The dialog box closes.
If you ever need to see a list of your named ranges and what cells those names refer to, open the Name Manager dialog box.
Simply click Name Manager on the Formulas tab, and you see all your named ranges in the dialog box. Using the buttons at the top, you can edit or delete named ranges.
You can substitute the range name for the cell addresses in any situation where using a range would be appropriate.
This video shows you how using range names (instead of cell references) makes creating a formulas more intuitive.
Range names that refer to multiple cells may produce an error in a formula where a multicelled range would not be an appropriate argument. For example, say the range B4 to B8 is named Sales. Typing = Sales in the Formula bar would result in an error because no math operation is specified. However, typing =SUM(Sales) would work just fine, because =SUM(Sales) is equivalent to =SUM(B4:B8).