As shown elsewhere, the tools and reports in ExcelAnalyzer make it much easier to find spreadsheet errors. Below more about running the ExcelAnalyzer Reports. If you don’t have it yet, get a free trial copy of ExcelAnalyzer.
Running the detailed Excel Formula Analysis on all sheets
Open the spreadsheet you want to review and click on the ExcelAnalyzer ribbon and choose the Formula Analysis button shown below.
You will be asked to choose which sheets to review. You can can choose 1, many or all. Generally we would suggest that you select all unless you have a very specific requirement.
Once you click Start it will run through and generate the reports.
You will see a main report called 1.Formulas and then next to each original sheet (untouched) will be another sheet with _1, _2 etc which is the sheet with all the spreadsheet error areas found.
Note that sometimes the sheet tab colour is different. This is part of the process of identifying errors. Have a look Excel Error Messages lesson to understand how these work.
Running the Excel workbook summary on a spreadsheet
Based on our recommended way of auditing an Excel spreadsheet, we recommend that you run this report immediately as well. A number of hard to find spreadsheet errors are visible in these reports. As shown below, click on the ExcelAnalyzer ribbon, click on the Workbook summary button and click the Start Button.
This will generate 3 new sheets that identify a number of potential errors in the spreadsheet and show the flow of formula across sheets.
You now have all the reports available to successful perform an audit review on an Excel spreadsheet.
Removing the ExcelAnalyzer reports
When you are done, you can remove all these reports without affecting the underlying spreadsheet model.
So you can:
- Switch on the spreadsheet error finding reports
- Find and fix the spreadsheet errors
- Remove the reports and continue using your spreadsheet.