Use labels to quickly define Excel range names

Don't create range names manually -- just let Excel do it for you.

The only Excel task more common than entering data and formulas is probably assigning range names to use in formulas and functions. For instance, you could enter the function =SUM(B2:B5), or you could assign the name Qtr1 to to cells B2:B5 and reference the range name instead, =SUM(Qtr1). The range name is easier to remember when you're entering new functions and formulas. If you're working with more than one row or column with labels, Excel can quickly assign names to spreadsheet rows and columns using label text. To use this method of naming ranges, do the following:

  1. Select any cell in the range and press [Ctrl]+[Shift]+* to select the contiguous range. (There's a great keyboard shortcut you might not know!)
  2. Choose Name from the Insert menu and then choose Create. If you're using Excel 2007, click the Formulas tab and choose Create From Selection in the Defined Names group.
  3. Excel will display the Create Names dialog box; it does a good job of finding the label text. Usually, you won't have to change the settings, but you can.
  4. Click OK.
To quickly view the new range names, choose Name from the Insert menu and choose Define. In 2007, click Name Manager in the Defined Names group on the Formulas tab. Excel defines a range for each column and row in the worksheet, using the respective labels. That's much quicker than creating them manually, one by one!

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox