Excel 2013: the Inquire add-in
The latest version of Excel comes with some built-in checking and audit tools. Simon Hurst explains how one of them can help most users reduce the risk of spreadsheet errors.
Admittedly, many of these high-profile debacles are more to do with faulty commercial logic or data input errors than to “simple” spreadsheet errors such as someone overwriting a formula with a fixed figure. However, there is little doubt that these sort of mechanical issues do cause a great deal of wasted time at best and career-threatening errors at worst. Third party spreadsheet checking and audit tools have been available for some time but in my experience, which is mainly with smaller organisations, they are not in anything like general use.
The Office Professional Plus version of Excel 2013 now includes some testing and auditing features of its own. The Discovery and Risk Assessment module is aimed at managing spreadsheets within larger organisations, while the Inquire add-in is aimed at all spreadsheet users. This article will look in more detail at the Inquire add-in.
The Inquire add-in is not installed by default and you will need to go to Excel options, Add-Ins, Manage, COM Add-ins to install it. Once installed, it appears as an additional ribbon tab:
Workbook Analysis Report
The Workbook Analysis Report provides a guide to a range of spreadsheet issues. The details of each issue can be displayed on screen or exported to another spreadsheet. To display details in the Results window you just click on the Item in the left-hand list. Here is a list of formulas containing numeric constants:
Including constants in formulae (such as typing in *.2 to work out VAT for example) is a common cause of problems in spreadsheets, so being able to work through every single such formula and identify whether it is a potential problem is a very significant aid to making a spreadsheet less error prone.
In many cases there will be innocent explanations such as the use of constants as optional arguments in functions, including the use of 0 or 1 in VLOOKUP() or MATCH() to define an exact or approximate lookup, or zeros in MAX() and MIN() functions to identify positives and negatives.
If you wanted to provide documentary evidence that each instance had been reviewed, you can select the check box of each required item and export the details to a separate Excel file. Each item will be reported on a separate sheet with additional information and a ‘Reviewer Comments’ column:
The existing formula auditing tools include the ability to display arrows in a spreadsheet indicating the precedent/dependent relationship of cells to other cells, including those on other sheets or in other workbooks.
Inquire includes a more sophisticated set of tools to show relationships at the cell, worksheet or workbook level, including analysing the links to workbooks that link to the current workbook and showing where files are missing and links broken. Below is a Workbook Relationship diagram as an example. Where relationships are complex, you will need to do some work to rearrange the diagram so you can see relationships more clearly: just remember Dr. Egon Spengler’s advice and try and avoid crossing the lines:
Judging by the number of times I have been asked whether you can compare two files in Excel, this option should be a very popular feature. You can select two workbooks and Excel will prepare a comprehensive analysis of the changes between them, showing cell by cell details of data and formula changes as well as changes to the formatting of cells. Each workbook is displayed in a separate window so you can easily compare and review the actual changes in detail:
Like the Workbook Analysis Report, you can export the results summary to a separate workbook.
This option is similar to the Workbook Analysis Report but, as the name suggests, is designed to be more immediate. Rather than providing a list or report of cells meeting certain conditions, it displays a list of cell and formula types allowing the user to select a particular type and see any of the currently displayed cells highlighted if they fall into that category. Here we are once again looking for cells “With numeric constants” and we can see such cells highlighted within the displayed sheet:
Inquire also includes tools to delete rows and columns that are beyond the used area of the active sheet or all the sheets in the workbook. This can help reduce the size of a workbook, though does need to be used with care, particularly if you are using conditional formatting. There is also a Workbook Passwords command to help you manage the passwords for files that you might need to open for comparison and analysis.
There will always be a question of how comprehensive any set of spreadsheet testing tools can be and how far they can be trusted to be accurate and complete. You will also need to understand enough about spreadsheet design and the sorts of things that can go wrong to use the tools effectively.
Given the range of tools in the Inquire add-in, however, you might feel that not only is it worth investigating the available tools in detail, but you might also want to run all of your important spreadsheets through them immediately, and use them for any spreadsheet troubleshooting that you need to undertake.
You might also be interested in
AccountingWEB’s Head of Insight has been with the site since 1999 and likes to spend his time studying accountants’ technology habits. When not nerding out, you can find him exploring obscure indie music and searching for the perfect organic sourdough loaf from his base in Brighton, UK.