Excel Hacks

Chapter 3. Naming Hacks

Hacks #39-44

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.

Figure 3-1. Naming a cell MyFavoriteCell

The drop-down list at the right side of the Name box enables you to find your named ranges and cells again. (See [Hack #44] at the end of this chapter for more ways to locate ranges.) If you happen to select a range precisely, ...