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.

4 comments
dhays
dhays

For a sheet with changing ranges, range names would be impractical. So I guess this comment actually has nothing to do with her post. One has to input the range manually either by typing the cell range or highlighting it.

donlp
donlp

Very handy, could be a great time saver.

FirstHal
FirstHal

I teach ICT and it's these sort of tips that make a learner's life so much easier. How about a set of crib cards?

williams
williams

There are ways of dealing with so-called dynamic ranges (ones that change their size) by using the OFFSET function along with the COUNT function. Basically the name is created by OFFSET and defined using COUNT. It is very handy for graphing data that is being continuously added to. Too long to go into here but perhaps a future topic for TechRepublic

Editor's Picks