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?
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.
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.