Identify Errors Using Excel Formula Debugging (2 Methods)

Imagine you have created a super long formula in Excel. While you are proud of yourself, there is a minor glitch. It is returning an error or an unexpected value.

Now you have two options:

  • Either recreate it again or go through it manually one step at a time, OR
  • Use the 2 formula debugging techniques I am about to share. Keep reading!

Excel Formula Debugging Method #1 – F9 Key

Here is how this works:

  1. Go to the cell which has the formula
  2. Enter into an edit mode (press F2)
  3. Select the entire formula and press F9 – This will give you the result of the entire formula
    • You can also select a sub-part of the formula and use this same technique

For example, suppose you have the text TrumpExcel in Cell A1, and in cell A3 you have the following formula:


Excel Formula Debugging Method 1

Excel Formula Debugging Method #2 – Evaluate Formula Step by Step

  1. Suppose you have the same formula as above in cell C1
  1. Select the cell that has the formula (C1 in this case)
  2. Click on Formula Tab
  3. Click on Evaluate Formula (Keyboard Shortcut – Alt + TUF). This will open the Evaluate Formula Dialogue Box
  4. Press Evaluate One by One to sift through the steps and their results
    Excel Formula Debugging - Evaluate dialogue box

Other Excel tutorials you may find useful:

Excel Ebook Subscribe


Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

2 thoughts on “Identify Errors Using Excel Formula Debugging (2 Methods)”

  1. Is there any addins developed to debug big formulas?..By using evaluate option i cannot do back step right?

    • Hello Karthik.. Thanks for commenting.. I usually go with the F9 key debugging method. It has the benefit as you can debug in parts and can also go back (by hitting Control + Z). I am not aware of any add-in for formula debugging.

Comments are closed.