Excel Hacks, 2nd Edition

Chapter 3. Naming Hacks

Hacks 44–49

Probably the biggest advantage to using named ranges is that formulas become a lot easier to read and understand, not only to you but also to others who need to work with your spreadsheets. Using named ranges (easily one of Excel’s most useful features), you can reference a range of cells and give it a specific name. From that point on, you can reference the range via the name rather than its range address. Although named ranges are powerful, you can go beyond the standard range names in many ways.

Address Data by Name

Although cell numbers are at the foundation of everything Excel does, it’s much easier to remember names, such as Item Number and Quantity, than it is to remember cell numbers, such as A1:A100. Excel makes this easy.

Excel uses the same technique for defining named cells and named ranges: the Name box at the left end of the Formula bar. To name a cell, select it, type the name you want into the Name box, as shown in Figure 3-1, and press Enter. To name a range of cells, select the range, type the name you want for that range in the Name box, and press Enter.

The drop-down list to the right of the Name box enables you to find your named ranges and cells again. (See “Identify Named Ranges on a Worksheet” [Identify Named Ranges on a Worksheet] at the end of this chapter for more ways to locate ranges.) If you happen to select a range precisely, ...