worksheet function - Troubleshoot formulas in Excel

Troubleshooting formulas can be difficult, and already to find a direction to start may not be easy.

• What can you do when your formula gives an error message?


• What if the formula has an unexpected result?

Although all formulas are different, are there common steps to solve those issues?

Answer

Evaluate formula

When your formula doesn't work as expected, your first step could be to evaluate it (Formulas tab - formula auditing group - evaluate formula)

Here you can follow calculations step by step by pressing "Evaluate" button.

Official help

Reduce complexity / range

If evaluating your formula doesn't solve the issue, mainly because the formula is too complex, refers to a huge number of data and you can't easily overview it, then try to reduce formula / data

• if you have a long formula, try to split it to smaller ones, placing them in separate cells to see which part fails.
  
  
  
  • if all the small parts work correctly, then have the complete formula referring to the sub results
  
  
  
  


• if your formula refers to a big range, try to limit the cells referred to, concentrating tricky parts in that referred range. This way evaluating the formula will give you better overview.

Array formulas

Array formulas can be great, however they can go wrong after edit.
If you see { } around a formula in Excel, then it's an array formula, after editing it, press CTRL+Shift+Enter to keep it as an array formula.

Asking for help

If you still can't solve it, then maybe it's a good idea to ask for help.

If you decide to ask here, please make sure your question includes:

• the formula your have (the shortest one which produces the error)


• some sample data to be able to test your formula (you can convert your spreadsheet data to markdown e.g. here


• current and desired results

---

You can also find helpful tips for building formulas in this answer.