Software

A quick alternative to manually entering range names in Excel formulas and functions

Always let Excel do the work, if you can. Use the Paste Name dialog box to let Excel enter range names into formulas and functions.

It's common to reference range names, instead of their respective cell addresses, in formulas and functions. The names are self-documenting and more efficient, and it's easier to remember a range name than a cell address.

The truth is, once you've assigned a range name, you don't even have to type the name. Simply press [F3] and choose the appropriate name from the Paste Name dialog box. Excel will enter the name for you. For instance, the sheet shown below contains several ranges: North, South, East, West, Total2008, and Total2009. Now, let's suppose you want a grand total for the years 2008 and 2009. Probably the most efficient formula is to add Total2008 (B6) and Total2009 (C6), as follows:

  1. Select the cell where you want to display the grand total.
  2. Start the formula by entering the equal sign ( = ).

  3. At this point, you could type a cell address or a range name — but don't. Instead, press [F3] to display the Paste Name dialog box.
  4. Choose Total2008.

  5. Click OK and Excel will enter the name into your formula.

  6. Enter an operator —  in this case, a plus sign ( + ).
  7. Press [F3] again, choose the next name, Total2009, and click OK.

  8. Press Enter.

Granted, for a simple expression or function, typing the name is almost as easy as using the Paste Name dialog box. On the other hand, if you can't remember the name or the name is long or otherwise complicated by forced conventions, using the Paste Name dialog box is easy and efficient, and it eliminates errors.

About

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