EXAMPLE INCISIVE.ONLINE SPREADSHEET...

Here is an example of the Incisive.Online spreadsheet analysis service results for a spreadsheet. In this example, we will use a spreadsheet that is available at the Minnesota Pollution Control Agency's website: Air Emissions Risk Analysis (AERA) — Risk Assessment Screening Spreadsheet (RASS) and Q/CHI Spreadsheet page. On this page, there is a spreadsheet (zipped xls) available for download here. A copy of the spreadsheet that we analyzed is available at: aq9-23.xls

In order to analyze this spreadsheet, it was submitted at: Incisive.Online spreadsheet analisys page. The following five reports were produced.

• aq9-23_RiskAssessmentReport.pdf
• aq9-23_RiskSummaryReport.pdf
• aq9-23_CriticalErrorReport.pdf
• aq9-23_HeatMap.xls
• aq9-23_StructureMap.xls

The Risk Assessment Report has six main sections:

• The Workbook Statistics section shows 81,327 formulas, but only 604 unique. This shows that the spreadsheet would be easier to review with a tool like Incisive Validate, which facilitates the process of reviewing formulas by grouping like formulas together. So, instead of reviewing 81,327 formulas, a reviewer could verify only 604 formulas and be done. With 20 sheets, this is a fairly big spreadsheet, but one that does not utilize names. It also shows one referenced workbook.
• The Sheet Statistics section shows the names of the sheets along with thier protected and hidden statuses as well as their formula counts. One sheet in this example is not protected: "updates". That name makes it seem reasonable that it would not be protected.
• The External Workbooks section shows a single externally referenced workbook. The name and formula reference count of this workbook increases the suspicion that this reference is an error. I would not expect a published spreadsheet to have a reference to a "Beta" page.
• The Excel Function Use section shows the counts of how many times an Excel function was used, and whether that use was in a non-array or array formula. Glancing at this list shows extensive use of IF as well as some VLOOKUP and HLOOKUP use. The spreadsheet would benefit from the lookup validation tests in Xcellerator.
• The Named Ranges section shows no surprises. Although this workbook does not utilize named ranges, sometimes workbooks will have names will have #REF! or external workbook references in them.
• Finally, the Unique Formulas section shows every unique (R1C1) formula in the workbook. Reviewing these formulas can provide insight into the structure of the spreadsheet. For example, the first two formulas listed each have 2528 occurrences. These are probably in neighboring columns. With a tool like Incisive Validate, you could review this formula once, and approve all 2528 uses. This is a huge time saver in reviewing workbooks. Also of interest is the last entries in this section. There are many formulas that are only used once. Although these are often legitimate formulas, they are sometimes errors that have been created during updates to the spreadsheet. In both Incisive Validate and Xcellerator, there are navigation tools that help you find these unique formulas that only occur once.

The Risk Summary Report has two main sections:

• The Workbook Statistics section shows 81,327 formulas, but only 604 unique. This shows that the spreadsheet would be easier to review with a tool like Incisive Validate, which facilitates the process of reviewing formulas by grouping like formulas together. So, instead of reviewing 81,327 formulas, a reviewer could verify only 604 formulas and be done. With 20 sheets, this is a fairly big spreadsheet, but one that does not utilize names. It also shows one referenced workbook.
• The Xcellerator Scan Results shows the number of results that each test found in a scan of Xcellerator. Xcellerator is a desktop tool that is available for purchase in the Incisive Store. Each submitted spreadsheet is scanned with an online version of Xcellerator running most of the available tests. The tests run are listed in this section with the tests with zero results listed last. In this example, I would first investigate the first four results - the formula error test results. These include Excel type "#" errors, Inconsistent formulas, Data in formula ranges, as well as lookup validation errors. After investigating and correcting these errors, the rest of the results can be reviewed as well. For locations of some of these test results, see the Critical Error Report.

The Critical Error Report, Heat Map, and Structure Map

• The Critical Error Report shows the location of some of the results identified in the Xcellerator Scan and summarized in the Risk Summary Report. In this case, it points to some cells of immediate concern: Inconsistent formula results at 'Risk Drivers Summary'!E271 and 'Risk Drivers Summary'!G271. Investigation into these cells with Xcellerator shows an error has been found.
• Even without Xcellerator these results can be further investigated using the Heat Map provided by the analysis service. This file has color coded results in a copy of your spreadsheet. When this analysis is done with Xcellerator, the test results can be filtered an navigated to using the built in navigation tools into Xcellerator. You can review your spreadsheet's errors by reading the comments inserted into each cell with a result. In addition to this, the Structure Map provided by the online analysis service can be used to view the structure of the spreadsheet. Boxes are drawn when possible around formulas that are the same (have the same R1C1).