Lost in the sauce of all things Power released as part of Excel 2013, Microsoft snuck in an app that is pretty obscure, so I thought I’d shine some light on it. What’s the app? Inquire. Ok, be honest have you heard of this yet? Microsoft bought Inquire which was previously known as SpreadsheetIQ before Microsoft bought Prodiance and their product on June 6, 2011.

In order to see and use the Inquire, you will need to add it from the list of Com components shipped but not turned on in Excel. This is the same process that you need to follow to add PowerPivot. Check out my previous post blog and follow the same steps you needed to add PowerPivot to add Inquire, which will add yet another tab to Excel. Why would you want to add Inquire? Well, it contains some neat tools to help you analyze what is going on in Excel worksheets. If you are unaware of these tools or are not sure how to use them, you might want to consider taking an excel course online to gain a deeper understanding of the tools and functions available in Microsoft Excel. In addition to helping you improve your speed while doing major data analytics projects, it may help you increase your accuracy as well.

How accurate is your data?

Since more and more data is being stored in spreadsheets people have been starting to study how accurate the data is. In a recent study at the University of Hawaii they have found that spreadsheets are wrong most of the time. It is not surprising that there are tools, like Inquire, being created to help accuracy improve. One of the ways Inquire helps improve data quality is to expose the internals of Excel so they are easier to review, which will make it easier to see what all is included within in Excel. These tools make it easier to analyze the contents all in one place. However, if you are extracting data from a website in a JSON file, you may not be able to use the various data analysis tools as Excel may not support such file formats. In such cases, you may need to look for an online JSON to XML converter, which may help you use extracted website data in Excel.

Workbook Analysis

When you have selected this feature, Inquire will go through your excel spreadsheet and review the contents. The summary contains a lot of useful information. In addition to including things like how many cells create formulas, as well as see how many cells contain errors. The summary also shows Very Hidden Sheets, which is something I didn’t even know you could create via VBA. Data Connections, Validation Criteria, Dependents, Duplicate formulas etc are some of the many items that Inquire includes in the summary. If you accidently put a minus sign in front of a cell and didn’t see notice, Inquire will list the item under Negative Formulas. All of the numeric items you have stored as text are listed too. If you run the detail report, you will see the workbook sheet name and every cell address and formula where these numeric text fields are located, which will be extremely handy when trying to correct this problem. All the information is stored in a report, which naturally is generated in an excel spreadsheet for later review.

Understanding the connections

Inquire’s Workbook Relationship report shows a picture of how workbooks are related together.

The cell relationship does a similar diagram on the individual cell level. If you have two spreadsheets and you want to know what the difference is between the two spreadsheets on a field by field basis, Inquire will show the differences when you select the Compare files button. If you just want to do some cleanup, there is a button for that too. Removing excess formatting can help improve the performance of the spreadsheet by eliminating duplicate and extraneous formatting. Lastly, inquire contains the ability for you to manage your passwords contained throughout the spreadsheet in one place.

With more and more data being stored in excel, either to create PowerBI analysis, Tabular Prototyping or just because excel is your data store of necessity for now, its nice to see Microsoft adding tools to help ensure that you can beat the odds and produce an excel spreadsheet where you can have greater confidence in the data stored within it.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur