Prioritise the cells to look at in a spreadsheet review

Prioritise the cells to look at in a spreadsheet review

The nature of spreadsheets is such that even using maps, the amount of work required to review the unique formulae can be considerable and time consuming.

Note: if you have stumbled onto this page it would be best if you started at the beginning

Spreadsheet Professional has a number of built in tests to help prioritise where you should start looking for errors if you are worried about the time it will take to review all the unique formula. Spreadsheet Professional will go through each cell and perform a number of tests.

Below is an indication of all the tests it can run. On other pages the meaning of each test is shown. Have a look below to see which ones may be of most interest to you and which ones you need more explanation on.

Spreadsheet Error Tests

This test will create a report that will summarise the results as shown below.

It also provides detailed results which comprise of a short paragraph explaining the concern, and then a listing of the cells that are at risk.

Example of the Error tests report
Example of the Error tests report

Below, the video clip explains how to run the reports and what to watch out for.

Of course this is not an exact listing of errors, and a manual procedure needs to be performed to understand the correctness of the cells identified, but at least you know where to focus first.

As you work through the list I recommend that you tick off the cells that you have looked at on a printed version of the map to avoid repeatedly looking at the same cells as its various risks are itemised..

Depending on the style of development, all the tests may not be applicable to your spreadsheet. In the options section of Spreadsheet Professional you can turn off certain of the tests (we highlight the ones we use regularly later in the course)

WARNING:

As this is an iterative process and requires that each test be performed on each cell, it can take time for the report to run.

Experiment on smaller spreadsheets. When you get to 30 and 40 MB models it is suggested that you consider running this particular report over night. That doesn’t mean you can’t work on the spreadsheet. We often run the maps as a stand alone and then run these tests on a separate computer. This way you can work on the maps while the computer works on the tests.

 

Purchase Spreadsheet Professional

Back to: Auditing an Excel spreadsheet > Prioritise which formula to look in for errors