Excel evaluate formula and error checking is one of my all-time favorite tools, it is incredibly useful.
Evaluate formula allows you to circle through individual calculation steps within a formula, shows you each component of your formula and eventually pinpoints the source of your error.
The evaluate formula tool is kind of similar to trace dependents and precedents tools in excel, but this allows you to go deep into your formulas and see the workings.
To evaluate a formula:
Select a cell containing a formula, go to Formulas tab, under Formula Auditing and click Evaluate Formula. This dialogue box pops up.
If you click Evaluate excel will work from the inside out and cycle through each individual calculations steps of the formulas till it arrives at the end result.
Evaluate formula is not just for error checking. It a great tool to break down complex or unfamiliar formulas. Formula Auditing tools could also replace them.
Error checking scans the worksheet for errors and provide a summary with options to trace the source, ignore the error, modify your options or link out to Microsoft support.
Options button in the lower left opens up your Excel options dialogue box where you have the ability to change or modify your error checking rules.
Show Calculations Steps: shows the full precedents or dependents parts and where the paths break down.
Help on this Error: links you straight to Microsoft Support websites which contains a great resource to talk about what that errors means and how to fix it.
The error can also be ignored or you can edit in the formula bar, if you know how to do that or you can proceed to the previous or next error in your worksheet.
Here is a visual explanation giving more insight about Excel evaluate formula and error checking. Checkout Microsoft support for more insights. Thanks, sharing is caring.