If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!

Jorge suggested in a recent post to Use Charts to Validate Your Tables. He used a data set of the US census bureau that had some incorrect data in it. Creating a simple line chart on the data set immediately revealed that there is one extreme outlier in the data set.

 

 

 

 

 

 

As Jorge says, “Can you see [the error] now? Of course you can. Try not to see it…”.

Jorge’s method allows you to validate two data series. What if you have 10s or 100s of data series you quickly want to scan and validate?

Lets open the data set of the US census bureau and insert a Micro Line Chart:

 

 

 

 

 

 

 

 

You now can visually scan the 100s of data series in the sheet at one glance. The outlier in ‘Income Before Taxes’ immediately pops out.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

So this is a a great way to validate a data set , just by quickly scanning the shape. Let’s add a bit more science. In another post Jon showed that Control Charts can be used to identify outliers.

 

 

 

 

 

 

 

Some Control limits are calculated out of the data set, and if some values fall out of these control limits, we say that the process is “Out of Control”. If we assume that the data set is normal distributed (most data is) we can simply calculate them as:

Upper Control Limit =  Mean + Standard Deviation

Lower Control Limit =  Mean -  Standard Deviation

Micro Line Charts have a build in capability to calculate and visualize control charts, the so called normal bands. You define your control limits as Mean +-Standard Deviation:

 

 

 

 

 

 

 

 

Now rescanning the report with the micro control charts it gets more obvious that something with ‘Income Before Taxes’ is wrong: