Quick, clean, and to the point

This video comes from our video training for Excel Charts.

Use a throwaway chart to sanity check data

In most cases, you'll want to take time to build clean, uncluttered charts that are easy to read. However, a quick, messy chart is a great way to "sanity check" unfamiliar data. A chart can show you patterns and outliers almost instantly. It can even show when data has been faked. Best of all, you get all of this with almost no effort.
Video Transcript 

In this video, we'll look at how to use a throwaway chart to explore data.

In most cases, you'll want to take time to build clean, uncluttered charts that are easy to read.

However, a quick disposable chart is a great way to check and explore data.

For example, in this worksheet, I have some sample sales data from a Microsoft tutorial on pivot tables. I don't really know much about this data, but I can learn a lot quickly with a chart.

I'll start by selecting all of the amounts, and use the keyboard shortcut Alt + F1 to build a chart.

The result is a messy column chart, but there's a lot of good information here.

First, I can see there are over 700 transactions total. 

Without category labels to work with, the horizontal axis is simply a count of values or rows.

Looking at the vertical axis, I can see that most transactions are $500 or lower.

The largest transactions are between $2,000 and $2,500.

I can hover over any column to see the exact value.

And, I know all this in less than a minute.

What else can we see?

Well, looking more closely at the bars, I can guess that this probably isn't real data.

I say this because I can see distinct clustering at certain levels.

Most likely, this data was created by mocking up a limited number of transactions, and then duplicating those transactions across a larger date range.

Now, let me delete the chart, and introduce a big data entry error.

On a random line, I'll add an extra digit to a unit price.

Now I'll chart the data again.

This time, the chart clearly shows the outlier.

Even if I know nothing about the data, I can guess this is a mistake.

So, the next time you need to quickly understand unfamiliar data, try a disposable chart.

They aren't pretty, but you can learn a lot with almost no effort.

Now - if you have a really large set of data, or want a chart that summarizes data by date or category, try a Pivot Chart.

For more details, see the section on Pivot Charts.

Dave Bruns

Related shortcuts