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:
- Select the cell where you want to enter the formula or function.
- Enter the formula or function up to the point where you want to reference a range.
- Press [F3] to display the Paste Name dialog.
- Select North.
- Click OK and Excel inserts a reference to the North range.
- 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.