Quick, clean, and to the point

Use a throwaway chart to sanity check data

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.


Related shortcuts

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.