Leading the way in Microsoft Office Development

Step 1 - (Tools > Formula Auditing > Evaluate Formula)

This feature was introduced in Excel 2002.

Formulas can quickly become very complicated, especially if you are using nested worksheet functions.

This allows you to break up your long formulas into smaller ones and to evaluate them separately.

Very useful when you have cell references and nested worksheet functions.

This helps you debug complex formulas by stepping you through each calculation and displaying the results.

This dialog box displays the formula and the values that are supplied as arguments.

If your formula does not return the correct value then you can use the Evaluate Formula dialog box to step into the formula.

It does not allow you to change the formula only to step though it. If you want to change the formula you must close the Evaluate Formula dialog box.

Step 2 - Stepping into your Formulas

The formula in cell B2 contains a cell reference as well as two nested worksheet functions.

The actual formula "=UPPER(LEFT(C2,FIND(" ", C2)))" is displayed in cell B3.

This is a relatively simple formula compared to the formulas that you could create.

This formula can be broken up into individual steps:

1) The FIND() function is used to locate the character position of the first space in the text "Better Solutions".

2) The LEFT() function is then used to obtain all the text that is to the left of this space. In this case the first word "Better".

3) The UPPER() function is then used to convert all the characters to uppercase resulting in the final value which is the text "BETTER".

Evaluate - Replaces any calculated arguments with their actual values. Shows result of an underlined expression or the value represented by an underlined cell reference.

Step In - Allows you to examine the formula represented by the underlined cell reference. This is not available when the cell being referenced is in another workbook. Allows you to view the actual contents of any referenced cells before applying it to the formula by displaying it in a separate box.

Step Out - Applies the argument to the function and combines the process.

Selecting "Step In" evaluates the cell C2.

This separates that particular piece of the formula and evaluates it separately in the box underneath.

Selecting "Step Out" will substitute the cell reference for the actual value in the formula.

Every time you press "Evaluate" another nested level of the formula will be evaluated and substituted.

Eventually the Evaluation box will display the actual result of the formula and the "Evaluate" button will change to a "Restart" button allowing you to step through the formula again.

When all arguments have been calculated, the final result of the formula is displayed.

You can press the "Restart" button to step through the formula again.

Step 3 - Formula Range Finder

This lets you visually redefine the cells that formulas refer to and can save you a lot of time.

When a cell formula refers to other cells these cells are surrounded with blue or green borders.

Each range of cells is colour coded so it is easy to distinguish which cells contribute to which parts of the formula.

The dimensions of a range can be changed by grabbing and moving the frame or the frame handle at the bottom of the colour coded frame.

When you edit a cell the formula arguments are displayed in coloured cells to help to identify the cells that are referenced.

These can be dragged to different cells, amending the formula automatically.

When you double click the cell now it will contain arrows to all the precedent cells in the current worksheet ??

Step 4 - Things to Remember

You can click the Evaluate button as many times as necessary to replace all the calculated arguments in your nested formulas

It is possible to obtain a graphical representation of your formulas by adding trace arrows to your worksheet.