Auditing an Excel spreadsheet

Should certain sheets look the same- do they?

This is a preview lesson

Purchase this course, or sign in if you’re already enrolled, to take this lesson.

With spreadsheets it is common to have sheets that are, or should be, the same structurally. That is, the only differences should be the inputs. The formulas should be the same in all cases. It is easy to check that sheets look the same and if not, this will in most cases indicate genuine spreadsheet errors.

First decide which sheets should look alike

Before you start looking at the spreadsheet, decide, based on what you know about the spreadsheet, which sheets should look the same. Then using ExcelAnalyzer Formulas report see if they are.

On the Formula report, if sheets have exactly the same formulas they will be colour coded the same. So below, the blue cells indicate that those 2 sheets are exactly the same formula wise. The question is should they be?

sheets look the same

Perhaps more important is looking for the sheets that should be the same and are not. Below there are 3 sheets that are the same. But I actually expect the 4th sheet to also be the same as they should all have the same formula with different inputs. So this is a high risk of spreadsheet errors and we need to check why it is like this.

sheets look the same

ExcelAnalyzer has a built in tool to tell us where the differences are. You can choose to compare 2 sheets to see where the differences are. As shown below in (1) and (2), we have chosen one of the sheets that is the same and the one that isn’t. The formulas will be listed as shown, but the formulas that are different will be highlighted in an orange colour.

sheets look the same

If you go and investigate these cells on the sheet, you may find something like this where, for some reason on this sheet it is multiplying the previous years details instead of the current year like the other sheets do (easy to see when using the Tracing Formulas tool on the whole row).

If you expect sheets to be structurally the same, and they are not, you may be able to find some spreadsheet errors very quickly.

YouTube find the sheets that are different

0 of 26 lessons complete (0%)
    Your Cart
    Your cart is emptyReturn to Shop