The beauty of Excel is its simplicity: If you enter your data correctly, it works. However, it can be ugly, especially when it returns a mess of formula errors, which sometimes are as understandable as Sanskrit.

We have gathered some tips that will help you root out the potential problems in formulas. If you can identify the issue quickly, then we've done our job. As for helping you fix it, that's for another post.

1. Formula Evaluation Tool (Excel 2000, 2002, 2003, 2007)
by David McQueary

If you’ve ever created a formula, you no doubt have come across a dreaded #N/A, #DIV/0 or other type of error. This can be frustrating, especially if the formula you entered is long and complex. Sometimes it is not easy to see what is causing the malfunction, and trying to read through the formula to spot the offender is not always a fruitful effort. Excel offers a Formula Evaluation tool, which assesses a formula step by step, showing each calculation and enabling you to view exactly where the error occurs. Here’s how:

Excel 2000, 2002, 2003:

1. Click Tools and move your mouse over Formula Auditing.

2. Select "Evaluate Formula."

3. Click the Evaluate button in the bottom left corner to evaluate the formula step by step.

Excel 2007:

1. Click Formulas and select "Evaluate Formula" from the "Formula Auditing" group.

2. Click the Evaluate button in the bottom left corner to evaluate the formula step by step.

***
2. The F9 Key (Excel 2000, 2002, 2003, 2007)
By Ronnie Merritt

Do you have a formula that is not working as expected? If you want to see where it is failing, you can use the F9 key to convert any portion of a formula to its actual value.

To demonstrate this, type any numbers into cells A1 and A2 in an Excel worksheet. In cell A3, type the following formula:

=A1*A2

Press Enter to enter the formula. The result should be the product of the values you typed into cells A1 and A2. Now select cell A3 and click into the Formula Bar, which is located above the column letters. This is the area that shows you what a cell actually contains, so it should be displaying the formula. Using your mouse, select A1 in the Formula Bar, then press the F9 key on your keyboard. The Formula Bar will show the value that was entered into cell A1.

This method can be particularly useful for troubleshooting large and complicated formulas, because it simplifies the task of viewing the result of each portion of the formula. Just remember to use the Esc key after using F9 in the Formula Bar. Otherwise, those converted values will be saved.

***
3. Trace Precedents and Dependents (Excel 2000, 2002, 2003, 2007)
by Stephanie Hausman & Jeff Bowman

Formula auditing helps you locate the cells that make up the formula and any other cells that may be affected by the formula. Two useful features in formula auditing are the Trace Precedents and Trace Dependents arrows.

You can use these features by following the steps below for your version of Excel.

Excel 2000, 2002, 2003:

1. Click View and move your mouse over Toolbars.

2. Make sure the Formula Auditing toolbar is enabled.

3. Select the cell you would like to analyze.

4. Select "Trace Precedents" or "Trace Dependents" to to view which cells a formula is based on, or which other cells are based on the selected cell.

5. When finished, select the "Remove Arrows" option to remove all traces.

Excel 2007:

1. Click the Formulas tab of the Ribbon.

2. Select the cell that you would like to analyze.

3. From the "Formula Auditing" group in the Ribbon, select "Trace Precedents" or "Trace Dependents" to view

You do not have flash or javascript support.

Average (0 votes)

Reply