I wanted to take a moment to share that some Microsoft Excel / Office 365 licenses include an “Inquire” add-in from Microsoft which you can optionally enable. Once enabled, the add-in creates an “Inquire” ribbon tab and can analyze open Excel workbooks in some of the following ways:
This can be extremely useful when you’re trying to hunt for things like errors in formula arguments, invalid named ranges, or links to external workbooks. The analysis tools in Inquire also allow you to Export selected findings, which is useful if you need to document issues that you or someone else needs to address.
In addition, Inquire can create diagrams of relationships between Worksheets and linked Workbooks, which is really helpful if you’re trying to visualize dependencies in a workbook or series of workbooks. For example, here are the dependencies between worksheets in a workbook:
There are other tools in here like a Workbook Comparison tool that can be helpful if you’re troubleshooting changes between versions of a report, for instance:
More information about Microsoft Inquire can be found here:
Hope this is helpful for anyone who has to wrangle with complex Excel files and all the things that can go wrong in them!