Building formulas faster in Microsoft Excel :

Don't just AutoSum—AutoAverage, AutoCount, and more!

Previous versions of Excel included the AutoSum button, which allowed you to calculate the sum of a range of cells with the click of a button. Now, clicking the same button also gives you the opportunity to automatically average, count, or get the maximum or minimum value in a range. Just select a range of values, then click the arrow on the AutoSum button, and click the operation you want—Sum,Average, Count, Max or Min.

Enter function arguments with ease—ScreenTips will help!

Now, when you start typing a formula that includes a function, Excel provides on the spot help by displaying a screen tip that shows the arguments necessary for the function. Each time you enter an argument, the next argument in the function is highlighted for you. Additionally, if you click on the function name in the ScreenTip, a Help topic with more information and examples for using that function is displayed.

Let Excel show you where the errors are

Like a spelling checker, Excel 2002 provides an error checker. You can start it manually by clicking Error checking on the Tools menu. It will check formulas for error values, for numbers stored as text, for formulas referring to empty cells, and more.

When background error checking is enabled (as it is by default), you don't even have to start the error checker manually. Excel indicates a formula that doesn't follow prescribed standards by placing green triangles in cells with possible errors. If you select the cell, the Error Checking Options smart tag is displayed, and you can click the arrow on it to get more information or see a list of options such as Help on this error, Show Calculation Steps, and Ignore Error.

Excel also contains a formula auditing toolbar that, among other options, lets you graphically trace precedent cells (cells with formulas that refer to the cell you're auditing) and dependent cells (cells that the cell you're auditing refer to). In the following illustration, blue indicators point to precedent or dependent cells.

May we suggest a function?

Type a natural language question in the Insert Function dialog box, and Excel lists possible functions to suit your needs. For example, type "how do I calculate monthly payments on a loan," and Excel suggests using the PMT function (which calculates the payment for a loan) or the NPER function (which returns the number of periods for an investment).

Cut and paste function examples from online Help

Online Help now offers you even more help! Look up a function, and check out the example at the bottom of the Help pane. You can even cut and paste the example, which includes formulas and data directly from Help onto your worksheet and watch it calculate to get a better idea of how the function works. The following illustration shows what such an example looks like.

See results in cells without scrolling

The Watch Window is an individual window that allows you to see what's going on in cells without having to scroll to the cells themselves. For example, let's say you're entering data on row 162 that may affect the result of a formula in cell A10. Rather than scrolling back to A10 to see the result of the formula, you can "watch" the value of the formula change from within the window, no matter where you are on the worksheet. You can even watch values on other sheets or in other workbooks. Additionally, you can click the cell reference in the Watch Window to move the selection to the cell you're watching. To watch a cell, just right-click the cell and click Add Watch.

Evaluate formulas one expression at a time

Even when a formula appears to be entered correctly, the order of the functions or operations may not be correct in order to get the results you want. The Evaluate Formula feature is handy for calculating the different parts of a formula especially nested formulas in the order that the formula evaluates them so that you can zero in on the specific part of the formula that may not be working the way you expect.

Evaluating a formula is something like using a debugging tool in a programming environment. It allows you to step in or step out of each section in the formula, and evaluate the section you want.

So, for example, let's say you have the following formula in cell D5:

=IF(AVERAGE(B2:B5)<50,SUM(C2:C5),0)

This formula says that if the average of the values in cells B2 through B5 is less than 50, then sum the values in the cells C2 through C5; otherwise, return 0.

You can use Evaluate Formula to see the formula evaluated one expression at a time. For example, when you first open the Evaluate Formula dialog box, you see the formula with the first expression underlined like this:

When you click Evaluate, the underlined expression is evaluated and the result shown like this (assuming that the values in B2:B5 are 51, 49,39, and 68):

Click Evaluate again and see that because 51.75 is more than 50, that expression evaluates to FALSE:

And since the expression evaluates to FALSE, the entire expression evaluates to zero.

To evaluate a formula, point to Formula Auditing on the Tools menu, and then clickEvaluate Formula.