Debug calculated values in the Excel Workbook
When you are working on the complex spreadsheet models, you constantly have one of these questions:
- How specific cell impacts to other cells?
- How selected cell depends from input of your model?
- Find indirect dependencies produced by reference functions (OFFSET, INDIRECT, INDEX, etc.).
- Numbers in your spreadsheet model are wrong.
- Complex formula returns unexpected value.
- Why output cell contains error value?
- Review all errors in workbook in single list.
- Export list of dependencies to worksheet or text file for future use or printing.
- and more ...
Evaluate Formula command will save you a lot of working hours!
It shows value of the every part of the formula expression. And you can expand it to trace precedent and dependent values.
Command is present on the Ribbon:
And in the Right Click context menu:
You may say, that there is Trace Precedents and Trace Dependents commands in Excel. It is possible to use them.
xlCompare shows actual dependencies in your model. It finds hidden dependents and precedents, produced by OFFSET, INDIRECT and other lookup functions. Which are not visible with another tracing tools and not publish in Excel.
Evaluate Formula command has simple and clear interface - it displays dependents and precedents in the tree-like recursive form. This way allows quick and native navigation by expanding levels of the tree.
Just try it once and you'll use it every time.