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:

=IF(A1="Trumpexcel","Right","Wrong")

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

1. Suppose you have the same formula as above in cell C1

=IF(A1="Trumpexcel","Right","Wrong")

2. Select the cell that has the formula (C1 in this case)
3. Click on Formula Tab
4. Click on Evaluate Formula (Keyboard Shortcut – Alt + TUF). This will open the Evaluate Formula Dialogue Box
5. Press Evaluate One by One to sift through the steps and their results
   

Other Excel tutorials you may find useful:

• !REF# Error in Excel – How to Fix the Reference Error!
• Use IFERROR with VLOOKUP to Get Rid of #N/A Errors
• Excel IFERROR Function
• #NAME Error in Excel – What Causes it and How to Fix it!