As in earlier releases, Excel 2007 lets you reference a formula by name. For example, say you use the worksheet shown in Figure A to calculate the average monthly sales for each of your products by Region. You would like your users to be able to calculate the Average Monthly Sales for each product anytime of the year, without having to create their own formulas.

Figure A

To set this up, follow these steps:

  1. Click in cell B2.
  2. On the Formula tab, click Define Name in the Define Name Group.
  3. Click Define Name.
  4. Click in the Name box and type AverageMonthlySales (Figure B).
  5. Click in the Refers To box and enter =AVERAGE(B4:B15).
  6. Click OK.

Figure B

To enter the named formula in row 2 of the worksheet, follow these steps:

  1. Select cells B2:K2.
  2. Click the Formula tab and then click Use In Formula in the Define Name Group (Figure C).

Figure C

  1. Click AverageMonthlySales.
  2. Press Ctrl + Enter (Figure D).

Figure D

You can define and add other operations to the Use In Formula menu ( such as Sum, MIN, or Max) that allow users to analyze their worksheets without having to write their own formulas.

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.