0

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?

2

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)

enter image description here

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.

  • 1
    .............nice answer.............. – Gary's Student Mar 17 '17 at 21:29
1

press

ctrl + `

to see all the formulaes available in the sheet,select a part of the formula and press

F9

to see the output of that part.works for me every time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.