Software

A quick tip for entering names into an expression

Show users this quick tip for entering range names into formulas and functions. It's easy to use and reduces errors from typos.

Ranges have a number of advantages and one of the most important is that they're just easier to remember than cell references. Most users will find it easier to enter =SUM(Sales2011) than =SUM(E3:E12). In addition, formulas that use names are easier to decipher and maintain. There's little question what =SUM(Sales2011) is evaluating and why.

Entering names manually is Okay, but there's an easier way: press [F3]. Doing so displays the Paste Name dialog. You choose a name, click OK, and Excel enters the name for you. Initially, it might sound like more work, but let users decide. Let's run through a quick example using the sheet below. Specifically, let's sum the values in the North range, B6:E6, as follows:

  1. Select the cell where you want to enter the formula or function.
  2. Enter the formula or function up to the point where you want to reference a range.
  3. Press [F3] to display the Paste Name dialog.
  4. Select North.
  5. Click OK and Excel inserts a reference to the North range.
  6. Complete the expression by entering a closing parentheses, and press [Enter!].

Admittedly, this is a simple example, but it's a technique you should show your users. It'll help you both. Most users will find this method easy to use and it helps users avoid typos, which will mean fewer calls to you.

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.

9 comments
sanjmeh
sanjmeh

I am on citrix so the function keys dont work. Could you suggest a menu option that imitates the F3 button action? Also i have difficulty in editing ranges, e.g. If one range name is to be deleted or renamed, i cant do that very quickly. It takes lots of efforts. Can anyone help me in this too?

mconour
mconour

I'm with Tolmr. How do we get that data?

gerdami
gerdami

Is there any shortcut to display local names ?

kirk.bare
kirk.bare

If you already know your range name Excel will automatically give you a suggestion list. In the Sales2011 example, once you type '=Sum(S' excel will suggest a number of values, ranges, functions, etc. In the test I just did (a new worksheet) Sales2011 was the first option. Just press [TAB] and it enters it into the cell.

tolmr
tolmr

Dear Susan, What are the contents in A1 to B3 for? Perhaps it is intuitive for you, but there is no explanation for how the smaller cell contents relate to the bigger one (A5 to E10). Please simply for those of us new to ranges... Thank you for this and many other tips

thilina.pr
thilina.pr

is the calculation mode set to automatic? what is the version of excel that you are using?

ssharkins
ssharkins

The labels in column A identify each range. For instance, B6:E6 is North and I'm using the F3 shortcut to enter North into a SUM() function in F6. A1 to B3 aren't part of the discussion, you can totally ignore those cells.

dogknees
dogknees

This works in all version from at least '97 onwards. Calculation setting makes no difference.