Formulas are the engines driving most of Excel’s functions. And as with any other engine, one malfunctioning part can cause the whole system to go wrong. This month, we help you troubleshoot your formulas using several tools built into Excel.

Common Typos

Here are some common problems to watch for when you are manually typing a formula (rather than using a function to set up all the parts for you).

Missing parentheses. It’s easy to leave out an opening or closing parenthesis in formulas. In simple formulas, Excel will notice when you insert a left parenthesis without a right one and insert the missing part. If Excel can’t figure out what’s missing, however, it produces an error message. Sometimes, the message proposes an edited formula; sometimes Excel doesn’t even try to guess what you intended.

Improper range punctuation. When you’re telling Excel to act on a range of cells (such as everything between E5 and E15), you must separate the cell references with a colon. The formula =SUM(E5:E15) adds up the whole range, but =SUM(E5, E15) adds only the two cells listed.

Typing numbers with formatting. Leave things such as dollar signs out of formulas. Excel wants to see a plain number. Use cell formatting to give the formula’s result the look you need.

Excel’s Instant Help

If you make an error when typing a formula, Excel flags it with a triangle icon in the cell’s corner. When you click the cell, you’ll see an exclamation point beside it. Click this icon to get help from Excel. The first line of the pop-up list describes the error (such as Formula Omits Adjacent Cells). With some errors, the second line offers Excel’s proposed solution (such as Update Formula To Include Cells). For some other errors, Excel can only offer to highlight the core problem for you.

Click Help On This Error to get a summary of what the message means. Or you can choose to Ignore Error or Edit In Formula Bar to fix it your way. Keep in mind that if you click the option to ignore an error, it won’t be flagged in later checks until you do a reset.

If you’ve never seen an error icon, you’re either an exceptional formula writer or the feature isn’t turned on. To make sure it’s on, click the Office button, choose Excel Options, and click Formulas. In the Error Checking section, make sure Enable Background Error Checking is checkmarked.

Hunt For Errors

You can ask Excel to scour a worksheet for errors just as you use Microsoft Word’s spell checker to find typos. Go to the Formulas tab’s Formula Auditing section and click the Error Checking button (it’s marked with an exclamation point). As Excel encounters formulas with errors, it presents a dialog box that includes the same options you’d see when a cell is flagged with the icon described above. By clicking Previous and Next, you can move through all the problematic cells Excel finds.

To reset any ignored errors so they show up during this review, click Options in the Error Checking dialog box. In the Error Checking section, click Reset Ignored Errors.

Analyze Complex Formulas

Finally, here’s one more tool Excel offers for spotting troubled formulas. The Evaluate Formula button on the Formulas tab (marked with an Fx label) walks through complex formulas one step at a time, letting you watch what’s happening and in what order. It’s a great way to make sense of dense formulas that include multiple sets of parentheses holding numerous calculations that take place in a very specific sequence.

Click a cell and the Evaluate Formula button to see a dialog box that breaks down the calculations. The window underlines the next calculation about to occur. When you click Evaluate, you’ll see the calculation’s result. That part of the formula turns to an italicized value, which is then used in the rest of the calculations. Keep clicking Evaluate to move through each step in the formula. Do this a few times, and you’re guaranteed to have new insight into how formulas work.