Excel Annoyances

NAMED RANGE ANNOYANCES

CREATE NAMED RANGES

The Annoyance:

I’m a volunteer track coach at the local high school, and I record my runners’ times, by event, in an Excel worksheet (shown in Figure 3-17). I can create formulas that use the cell addresses for each runner’s times, but I’d have to look in the worksheet to see which runner’s times I was working with in a particular formula. I’d rather create a shortcut reference I can use to refer to each runner’s times, especially if I could use those labels as names in my formulas. Is this what people mean when they talk about “named ranges?” If so, how can I create named ranges from the existing worksheet?

The Fix:

To create named ranges from existing data labels, first select the entire range of cells, including the labels you want to use as the names of your ranges, which is the range A3:G22 in the worksheet shown in Figure 3-17. (In this case, the ranges represent each runner’s times, so you wouldn’t select the race distances in row 2.) After you select the data range, choose Insert → Name → Create to display the Create Names dialog box shown in Figure 3-18. Check the box that identifies the location of the rows or columns to which you want to give names (“Left column” in the current example) and click OK to create the names. From now on, instead of referring to row numbers or column letters in formulas and cell references, you can use the label names.

Figure 3-20. Existing data labels are sufficient to create named ranges.

Figure 3-21. The ...