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 #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

Add a Comment

Your email address will not be published. Required fields are marked *