In Excel 2013, amongst all the other amazing features that have been added since 2010 is a new add-in that can analyse your workbook for errors or inconsistencies.
The problem with this add-in, is that it has not been particularly well promoted and it is not in the standard add-in list that most of us go to turn on add-ins.
Firstly, note that this add-in is only available in the Professional Plus version of Excel. If you do have this version, then go to FILE, OPTIONS, ADD-INS. Go to the bottom of the screen and click on the drop down where you see EXCEL ADD-INS and click on COM ADD-INS and then click on GO.
Tick the box next to INQUIRE to activate it and click on OK.
You should now see a new tab on your Ribbon.
You will now access to a range of functions that can help you to examine your workbooks. If you don’t have this tool, you can still use some of the FORMULA AUDITING tools in the FORMULAS tab such as SHOW DEPENDENTS etc. but of course this is a more manual process and won’t give you the same level of detail that the new add-in can.
Make sure that the workbook you want to analyse has been saved. This will not work on an unsaved workbook. Click on the WORKBOOK ANALYSIS button and let it do its thing. After a few moments (this will vary depending on how big and complex your workbook is) you will see this window;
You will see an overall summary of the workbook contents and if you click on each element in the ITEMS window, you will see the details in the RESULTS window on the right hand side.
OK, you may not necessarily be interested in how many cells contain formulas etc. but there is some useful information in here such as external references, cells containing errors etc. As you click on each element, you will see exactly which cells on which sheet meet those criteria.
Click on EXCEL EXPORT to create a copy of the report.
As the name suggests, this will show you a diagrammatic view of how your workbook is connected to external data sources – other workbooks, databases etc.
Use the buttons at the bottom of the window to print the diagram, preview the diagram, or simply to refresh it.
If your workbook relationships are particularly complex you can rearrange the various workbooks and databases by clicking and dragging the icons in the window into an easier to read layout.
Similar to above, but this time looking at how the worksheets are connected rather than at workbook level.
Same buttons at the bottom of the window and the layout can be changed too just as before.
When you run this you will get a few options to select from in terms of searching for precedents and/or dependent cells. Pick whichever options suit your requirements best and click on OK.
And it does exactly what is says…looks at precedents/dependents of any selected cell (rather than every single cell in your workbook). As I mentioned earlier, this can be achieved using the FORMULA AUDITING tools but this is a more convenient way of seeing everything. As with all the other relationship diagrams, same options are available.
In Excel you have had the option to look at workbooks side by side for some time but again this was a very manual process scrolling through your workbooks carrying out visual checks to see any differences between workbook versions.
This option gives you an automatic check.
Click on COMPARE FILES and use the drop downs to select the correct workbooks to compare. Note that both workbooks must be open in order to run this. Click on COMPARE to run the comparison showing differences
This will produce a full screen report. Where there are variances the cells will be colour coded depending on the type of variance.
You can review each worksheet independently. Check the lower half of the screen to see the colour coding and the details about the differences.
There is also a small chart to show you how many of each variance type it has detected. Not the most essential bit of information but looks pretty in the exported report as a sort of dashboard.
COMPARE FILES also compares VBA code between workbooks if it is present, so quite a useful tool.
Clean Excess Cell Formatting
You well find that a workbook is slow to open, or may take a while to refresh every time you make a change. This could be for a variety of reasons, but one of those may well be excessive cell formatting. When you set up a worksheet it may be convenient to apply conditional formatting, or just simple formatting to entire rows or columns. This can make your workbook quite bloated. CLEAN EXCESS CELL FORMATTING looks at empty cells beyond the last cell that contains data and removes any formatting that may be applied.
When you run this you get the option to run on the active sheet or the entire workbook. Click on OK once you have made your choice. You may be prompted to save changes but otherwise no other windows to interact with.
This is used to store passwords for workbooks so that next time you open the workbook to run a comparison analysis you don’t have to enter the password. This probably has only limited appeal but is useful if you run any analysis or comparison when accessing the INQUIRE add-in via the START menu.
Click on START, go to the MICROSOFT OFFICE folder and open OFFICE 2013 TOOLS.
From there click on SPREADSHEET COMPARE 2013. This will open a new window. Use the COMPARE FILES button to select the two workbooks you want to run the comparison on. If passwords have been stored in the PASSWORD MANAGER then you won’t have to enter any passwords to open or access the file(s).
Click on OK once you have chosen your files and continue as above to review your files.
So next time you need to run a quick analysis of your workbook, or need to compare two files, (and you have Excel 2013 Professional Plus) remember to turn on your INQUIRE add-in and give it a run…much quicker than doing it all manually.