Formula Debugging In Excel – With One Key!

When I first started writing formulas (or even formulas now!), there have been times when the cell returned a mysterious error or an unexpected result. Luckily there’s a quick and easy way to debug formulas.

My Formula Is Not Working!

For the example, assume we want to check if a certain date is between a specific date range.

Broken date check formula

In the example above, I would expect cell B5 to return “This Year” as clearly the date in B4 lies within the start and end range. However, if I hit enter, it returns “Not This Year”. Clearly wrong!

Returning incorrect formula

Debugging And Analyzing A Excel Formula

We want to know why the formula is not activating “This Year” where we are expecting it to:

  1. Select the cell with the formula and click on the formula bar (or press F2)
  2. Select a part of the formula
Highlight formula

Note: A quick method of selecting a section of the formula is by clicking on the parameter label.  For example, if you click on “logical1” here, it would highlight the first logic test of the AND function.  This is especially useful for debugging long IF functions.

  1. Press F9 (for eg: the first test in the AND formula)
  2. This will only evaluate the selected part and replaces it with the result
  3. To undo this change, either hit Escape (which will exit the formula bar) or Ctrl + Z (which will restore the formula to the previous state). If you hit ENTER, this will store the result in your formula. Probably not what you want to do!
  4. Using this debugging method, it allows you to step through the formula and narrow down the error to specific calculations or cells.
Formula logic checked

As you can see, I’ve got my “>” the wrong way round and it needs to be flipped to less or equal “<=”.

Final result

The Evaluate Formula Debugging Tool

Excel also has an in-built formula audit tool called the “Evaluate Formula” tool. You can find it under the “Formulas” ribbon. The shortcut in the latest Excel is Alt, M, V.

Evaluate Formula Tool

That brings up the Evaluate Formula dialogue box. If you click on “Evaluate” it steps through the calculation like how Excel would do it in the background.

Evaluate Formula Dialog Box

This should help you debug any complex formulas as well!

Frequently Asked Questions

What Is The Shortcut For Evaluating A Formula In Excel?

If you’re opening an Excel file for the first time and are presented with a long formula, you can quickly step through it by pressing the F9 key.

See above for a step-by-step on how to use the F9 key to debug and analyze the formula.

You may also like to read:

Leave a Comment