Excel: Using Inquire

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:

image

Check the box next to Inquire, and select OK. A tab in the ribbon will appear for it, with the following icons:

image

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.

image

Save the workbook, and click the Workbook Analysis icon A report window will appear with a detailed analysis of everything in the sheet:

image

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?

image

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.

 

image

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:

image

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:

image

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:

image

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:

image 

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.

image

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.

Resources: For more information on using Inquire in Excel, see the Microsoft support article here or my colleague Devin Knight’s post on it here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s