Go ahead admit it. You’ve made a mistake, at least one. I know I’ve made tons of them, especially in Excel, but then again, when you’ve been using Excel since 1984 like I have, you’ve probably made most of the mistakes – multiple times. Luckily I’ve learned a few handy tips on quickly debugging formulas. Here’s a few troubleshooting tips.
Calculate the inner results of a formula
Press F9 to calculate the selected formula segment, Esc to undo
This is an indispensable keystroke I use almost every time I’m creating a complex formula. I use it to test a segment of a formula that isn’t working as I it to. If you have a long or complex formula, select a segment of the formula in the formula bar. It must be a segment that can calculate on its own, including having the correct balance of left and right parenthesis. Then press F9 to see what that segment calculates to. Press Esc to go back to the formula.
For example, in this long text manipulation formula you can select any segment that can be calculated as a complete formula on its own. Here the ???? segment is selected. Press the F9 and it calculates just the selected segment as shown in the figure below. BE CAREFUL! At this point if you pressed Enter you would enter the formula with the calculated result instead of the formula. Press Esc to undo the F9 calculation. If you accidentally press Enter, just use Edit Undo to return to the original formula.
Show formulas in the cell as text
Press Ctrl + ~
If you want to print all the formulas in an area of the worksheet, press Ctrl + ~, widen the columns as necessary and print. Press Ctrl + ~ again to show the results of formulas in the worksheet. (Tilde, the ~ symbol, is to the left of the number 1 on the US keyboard.)
If you are using Excel 2013 you can also show a formula as text by referencing it with FORMULATEXT(ref). For example,
Will display as text the formula in cell B12. This makes it easy to create a tab in your workbook that contains difficult formulas in one column and a text explanation in an adjacent column.
Add a comment in cell
Adding a comment to a cell is a great way to add instructions, help, or documentation. If you want to add hidden documentation right in the formula, then use this trick with the N() function.
Color coded cell references
When you click inside a formula in the formula bar the formula segments each take on a unique color. Notice that the cells referenced by the formula have the same color as the segment referencing them. Those folks at Microsoft really are trying to help us.
Once you get that formula working correctly, you might want to use this little trick to add documentation right in the formula.