Five tips for troubleshooting formulas in Excel

If you're worksheet-challenged (or even if you aren't), finding and fixing formula problems can be a big headache. These tips will help ease the pain.

If you're a numbers person, creating and sleuthing out problems in Excel may seem like a nice little puzzle that will keep you awake this afternoon. But if you're not a numbers person, formula problems can feel huge -- like you have a Grand Canyon-size hole in the knowledge you desperately need to complete your task successfully. If you're one of the latter group (as I am), these tips will help you identify the problem and solve it so you can get back to the stuff you actually enjoy doing.

1: Know the signs (by turning on Error Checking)

Excel will let you know when a cell has a formula error if you have error checking enabled for your worksheet. As you will find, there are good reasons not to leave error checking on all the time, especially in cases where you will be creating a large worksheet (or you're inputting data from another source), so the formulas won't be correct until all the data is in place. But for most general worksheet use, it's a good idea to have error checking enabled.

Turn on error checking by clicking the File tab, clicking Options, and then clicking Formulas in the left panel of the Options dialog box. Make sure that the Enable Background Error Checking check box is selected. You can also choose the color you want to use to point out formula errors. Now, click OK. Excel will show you where any errors are in your worksheet by displaying a small colored triangle in the upper-left corner of the cell where it found a problem. Select the cell to display the information icon. Then, click the icon to see a context menu of options you can use to correct the error (Figure A).

Figure A

Excel offers options for sleuthing out the problem.

2: Rule out common errors first

We've all done this (some of us, time and time again), but it's a good idea to take a close look at your formula to be sure you haven't made some simple mistake: Did you forget to start the equation with an equal sign? Other common problems that throw an error involve not using a colon to indicate a range of cells, forgetting to close your parentheses, and adding common formats as part of the formula (for example, entering $2,350 when you should enter 2300).

You can view and edit the formula in the Formula Bar by clicking the cell that contains it. Or you can show all formulas in the worksheet by clicking the Formulas tab and clicking Show Formulas in the Formula Auditing group. You can then make simple corrections by deleting incorrect entries and typing the correct formula.

3: Let Excel help

When you enter a formula with an obvious error, Excel will prompt you and propose a correction; you can click Yes to use Excel's suggestion or click Cancel to return to the formula and correct it yourself (Figure B). You can also use the options in the context menu that appears when you click the information icon to get help on the issue, ignore the error, step through the calculations involved in the formula, or change the formula options in play.

Figure B

Excel lets you know you entered the formula incorrectly and suggests a correction.

4: Do not pass 0, do not collect $200

Your Excel worksheet will not appreciate it if you try to divide by zero as part of your calculation. If your formula references another cell that happens to have a 0 value, you will see the error #DIV/0!, which means "Hey! You know better than this! No dividing by zero!" To correct the error, revisit your formula and determine which cell the incorrect reference should be pointing to. Or if necessary, correct the cell value so that Excel will calm down.

5: Use formula auditing tools

Excel also includes a great set of formula tools you can use to discover where the problem is in the formula you're trying to fix. These tools are especially helpful when you are working on a large or complex spreadsheet that includes a number of sophisticated formulas. Click the Formulas tab and you'll find the tools in the Formula Auditing group.

Trace Precedents and Trace Dependents show you (by placing arrows on the worksheet) which cells contribute to the formula or depend on the formula value. Choosing Error Checking causes Excel to do a quick check on your worksheet. (This is great for those times you've been tweaking things and want to make sure you haven't introduced any errors.) Evaluate Formula gives you the choice of analyzing the parts of the formula or stepping through it to see where the error occurs. All of these options help you see what other factors might be contributing to the error your formula is producing.

Bonus tip: Keep an eye on cells and formulas in the Watch Window

When you're working with a really large worksheet, it's possible that the formula you're working with draws values from cells that have scrolled off the screen. When you're trying to locate the source of a formula error and correct it, the Watch Window makes it easy for you to see what's happening to the cells and the formula when you make changes.

Display the Watch Window by clicking Formulas and clicking Watch Window in the Formula Auditing tools. You can add cells to the Watch Window by selecting the cells you want to watch and clicking Add Watch (Figure C). Verify the range in the Add Watch window and click Add. Excel displays the values and formulas so that as you update the content of the cells, the items you're tracking are always visible no matter where in the worksheet you're working.

Figure C

View cells and formulas by adding them to the Watch Window.