More tips on the spreadsheet review process

More tips on the spreadsheet review process

Adding Colour to your maps

When your spreadsheets get very big, the maps can be a daunting experience and can become hypnotic on the eye. Rows and rows of similar characters can lead to the human mind missing anomalies.

Colour Maps 1

There is an easy way to add colour to the maps to make the inconsistencies easier to spot. To do this you need to make use of the conditional formatting feature in Excel. This feature allows you to format a cell in a different way depending on the contents of a cell.

As shown below, it is a lot easier to see where the key cells are.

Colour Maps 2

Prioritising the priorities

Our experience has shown that the test reports are extremely useful in prioritising the cells to be reviewed. There are however some tests that almost always show up problems and we generally look at these test results first.

If you look in the options tab, the tests we look at first in order are:

  • Unused input values and calculations,
  • the dependents rule,
  • blank cells referenced,
  • numeric values in formulae, and
  • the various lookup tests and NPV tests.

Once we have been through the cells identified inĀ  these tests, we review the other test results, and then complete the leftover formulas from the map.

 

Purchase Spreadsheet Professional

Back to: Auditing an Excel spreadsheet > More Tips and Tricks for Auditing an Excel Spreadsheet