Spotting and remedying inconsistencies in Microsoft Excel can be a laborious process. At least it can without the technique to spot them quickly and accurately. Paolo Lenotti of Excel with Business is on hand to show you how you do it.

I am going to kick off the New Year by looking at why inconsistencies can creep into a Microsoft Excel spreadsheet and some techniques for spotting and fixing them.

Why inconsistencies occur in Microsoft Excel

Inconsistencies have a range of causes: data-entry errors; accidental changes made to the data after entry; inconsistently applied rounding or changes to units; or differences in the way numbers are formatted in different systems. The following is a simple technique which can help you spot mistakes or inconsistencies in a spreadsheet very quickly.

Consider the following two trial balances:

Look pretty similar, don’t they? But suppose you know that there’s at least one difference somewhere (e.g. because the Debits and Credits don’t tally) – what’s the fastest way of finding that discrepancy?

A quick way is to use a very simple trick using the = sign. To check for discrepancies between columns C and I (the two debit columns) we type =C6=I6 (in a column over to the right of the Worksheet – column M say) and copy this formula down the column.

This gives the value TRUE in cell M6, because the numbers in C6 and I6 ARE identical. For rows where the entries in column C and I are not identical, the value FALSE is produced.

Copying this formula down the column identifies any discrepancies:

If you have a huge amount of data, some conditional formatting on column M could help make the FALSE entries stand out more (e.g. showing the cell coloured red as we’ve done above).

It’s a good idea to include checks of this sort when you’re adding new data to a spreadsheet, especially if some of it has been entered manually.

Another application of the technique would be to test whether a set of numbers add up to the figure you expect. For instance you might know the total value of sales for last month, so add a simple check that the sum of the sales for each product equals that total.

AAT members have free access to this and many other Excel tips through their MyAAT account. 

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.