Finding inconsistent formula in an Excel Spreadsheet

Finding inconsistent formula in an Excel Spreadsheet

Maps are without doubt one of the most important feature of Spreadsheet Professional.

First let’s explain what we mean by a map.

In the spreadsheet below, just by looking at the image, can you tell me where the errors are?

find-inconsistent-formula-maps

Realistically this is impossible. The best you can do is try and recreate the spreadsheet and see where the numbers are different. Even if we gave you the spreadsheet, you would need to look into every single cell to find the errors.

Where do you start? Top left? In the numbers section? You may only get to the error when you are exhausted and loosing concentration!

A map shows you the spreadsheet in a different way and highlights the inconsistent formula. Below the map of the above spreadsheet.

A simple explanation (more covered in video clip) is that in the matching cell, Spreadsheet Professional has found a :

  • (L)abel,
  • a (N)umber, or
  • a Unique Formula (F).

It also identifies copies of unique formula from:

  • left to right (<),
  • from above (^), and
  • from both above and left (+).

find-inconsistent-formula-maps

Note that you will now immediately notice that in row 20 there seems to be many different formula versions. Similarly in row 25. Whereas row 24 has one formula copied all the way across, row 25 and 27 seem to have new formulas that are not copied.

In this way you can quickly look at these potentially risky cells and check them first. Even better, if you just look at all the F cells you will cover the spreadsheet by looking at only 16% of the cell!

The time saving and improved effectiveness in finding errors is as much as a factor of 10 times.

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

In the video clip below you can see how to run the maps, what they mean and what to look for with regards inconsistencies and patterns.

 

Purchase Spreadsheet Professional

Back to: Auditing an Excel spreadsheet > Finding inconsistent Excel formula with Maps