Use these handy tips to debug your Excel formulas

Creating complicated Excel formulas can be tricky. The recalculation key is a quick way to test the results of your formulas as you go. Read more to learn this and two other handy Excel tips.

Having been an Excel support engineer at Microsoft, I’ve come across some pretty interesting tips and tricks to help users work more efficiently with Excel. One such tip is to use the [F9] key as a debugging tool for Excel formulas. Testing a complicated formula can be a daunting task, but the [F9], or Worksheet Recalculation, key is a quick and easy way to test your formulas as you create them. In this article, I have outlined how to use this [F9] debugging tool, plus I’ve included two other tips to help you use Excel more efficiently.

A simple sum formula
To illustrate the handiness of debugging formulas with the [F9] key, I'm going to create a simple spreadsheet with four values in the first column (see Figure A).

Figure A

Now let’s assume that I want to sum the cells A1 through A3 and use the [F9] key to check my formula. To do this, I enter a simple SUM formula in cell B1, but instead of hitting [Enter] right away, I highlight the cell references as shown in Figure B.

Figure B

Make sure that you highlight the cell reference accurately. If not, you will be prompted with the error message shown in Figure C. To correct this problem, simply click OK to clear the error message and check your highlighted text.

Figure C

See cell values instead of cell references
Pressing the [F9] key now will change the cell references (A1:A3) to the actual cell values (see Figure D). This allows you to ensure that your cell references are correct before activating the formula.

Figure D
The cell references have changed to actual cell values.

If you press the [Enter] key while the cell values are being displayed, those values become a part of the formula. This will cause you to lose the cell references that you previously had (A1:A3). You need to press the [Esc] key to cancel your test to retain the formula.
Formulas at your fingertips
Here’s a tip for quickly getting to your most used formulas. When you highlight cell references within the formula, the cell’s Name Box (outlined in red in Figure A) will automatically change to a drop-down list of the most recently used formulas (see Figure E).

Figure E
Click on the formula you wish to use.

Make your formulas appear on the spreadsheet
An easy way for you to view a spreadsheet’s formulas is to press [Ctrl]tilde (~). This will display a cell’s formula instead of the formula’s value (see Figure F).

Figure F

Get valuable tips on creating tables and formatting pages, as well as links to Word resources and much more. All delivered straight to your inbox every weekday. Best of all, it's absolutely free. Sign up for the Excel TechMail today!

Editor's Picks