Quietly, Microsoft released a new feature with Excel 2013 Professional called Inquire. Inquire allows you to do all kinds of things at an excel level, making it much easier to analyze workbooks for formulas, compare dependencies across the environment from both the workbook and worksheet level. In this post, I’d like to walk through the 8 icons in the Inquire tab in the ribbon, and what each of them do. First, to enable the inquire tab, go to File->Options, and select Add-Ins, then COM Add-Ins:
Check the box next to Inquire, and select OK. A tab in the ribbon will appear for it, with the following icons:
Workbook Analysis – the workbook analysis icon investigates everything about the current workbook: connections, hidden sheets, formulas, even links to other workbooks. To demonstrate, I’ve created a very simple workbook that connects to another workbook on my machine, and has some fruit and numbers on it.
Save the workbook, and click the Workbook Analysis icon A report window will appear with a detailed analysis of everything in the sheet:
You can see we can break down formulas into their various categories, unique, numeric, datetime, nested IF’s, etc. This is very useful for trying to break down some complex enterprise workbooks, especially ones used for financial analysis.
Workbook Relationship – The workbook relationship tool analyzes the workbook for any dependencies to other workbooks across the environment. It is smart enough to trace through the entire relationship until it reaches the last child of the chain. For example, let’s say I created a link to another workbook in my environment, say Exotic fruits, with a reference that returns the value in that workbook. This is really handy for big messy corporate environments that can have links pointing to other documents all over the enterprise. Dashboards that are self-created by end users, perhaps?
Worksheet Relationship – The worksheet relationship analyzer takes the workbook relationship to a finer level of granularity, by specifying the actual worksheet that contains the relationship, starting at the current workbook level. This can be used to gain an even deeper insight into where the relationships exist. Also, if you notice at the bottom of the screenshot I also have the option to print this, so I can provide it to others in the organization if I need to publicize the relationships.
Cell Relationship – Cell relationship takes the last 2 to the finest level of detail possible. Due to this, I can specify how deep I want to go on the configuration screen to prevent the tool from going crazy in an enterprise environment. But if I do want to analyze down to the cell level, I can do that. The one caveat to this tool is that I need to select the cell I want to start with, that has at least the initial hyperlink. Running the tool against my same two workbooks yields this screenshot:
Compare Files – the compare files tool lets you compare two workbooks against each other and evaluate every difference between them. The immediate thing that jumps to mind for me is all of the copies of data dictionaries that get generated during BI implementations. This could be very useful for comparing them against each other to find the most complete copy. Simply click the icon, then select the two that you want to compare:
This then gives a complete view of every difference down to the cell level. Additionally, at the bottom it breaks down the history for the sheet, and you can select options if you want to compare down to a formula, structural, or naming level:
Clean Excess Cell Formatting – Over time, workbooks get bigger and bigger as cells are formatted in the workbook. The workbook becomes bigger and bigger, until eventually it takes 10 minutes or more to open up the workbook. Clean excess cell formatting lets you remove all of this additional formatting in the workbook, reducing the size to a manageable chunk that can still be opened and worked with. When you click on the icon, the tool will simply ask you if you want to apply the cleaner to all sheets or active sheets and then do its scrub:
Workbook Passwords – The workbook passwords screen lets you manage passwords for workbooks you want to analyze with the inquire tool. This way you can import or add all of the passwords for the workbooks and the tool will bring those workbooks in for you.
Overall, Inquire is a very nice feature in Excel 2013 Professional and I can see myself starting to use it during assessments and client engagements to get a better feel for environments.