Establish a Control Total

Establish a control total

When cleaning data, it is important you establish some sort of control total so that you can tell whether you have accidentally changed the underlying data while cleaning it up.

This could be anything. Perhaps you know the total of one of the columns. If you are trying to analyse a sales report you are likely to know what the total is. Jot this down somewhere so that you can check back at a later stage and make sure you are working with the correct data.

To follow along in your Excel Download File go to 1.1 Control Totals

If you know you will be removing some data, you still need to establish a control total. This is where the concept of reporting by exclusion is important.

Report by Exclusion

Another important concept is reporting by exclusion. With this concept, even if you are only interested in one item (e.g. product A), you should still create reports for all the products (e.g. product A, B and C) and then EXCLUDE the ones you don’t want.

This way you can test your control total and make sure you haven’t accidently mixed up products.

This is best explained by a video example

To follow along in your Excel Download File go to 1.2 Report by Exclusion