Excel Quintile Chart

One of my Excel Dashboard course members, Mark Maher, emailed me wanting to know if a chart that Stephen Few had mocked up in Adobe Illustrator could be done in Excel.

The answer is yes (see below), but it’s not native to Excel and it took a bit of jiggery-pokery to get it to look like Stephen’s.

Before we dive into building this in Excel I should say that the reason behind this chart is to statistically represent how a school performs compared to Similar schools and All schools, hence the two columns.

Of course that’s not the only use. You might have cut off points for tertiles , quartiles or deciles. Feel free to adapt the chart to suit the interval of your choice.

Tricks Behind the Quintile Chart

This chart uses a layering technique. The small grey dots and the larger coloured dots are actually separate series in the chart.

The coloured dots are simply sitting on top of the grey dots so you can’t see them.

As a result the data feeding the chart consists of two parts. The underlying grey dot data and the coloured dot data:

When I click on the grey dot for the 3^rd quintile ‘All’ column you can also see in the image below that there is a grey dot underneath the yellow dot (because there are 4 small circles to indicate it is selected).

Plus if you look at the data table you can see the data in cells C5:D5 have the marching ants around them to indicated they are the source for that series, or quintile.

On the other hand, if I select the yellow dot you can see cell C10 is the source for the series, and because there is no value for ‘All’, the grey dot is visible.

Note: you might have also noticed that the chart labels are not part of the chart. I’ve put them in cells beside the chart because the actual axis labels for a scatter chart will be the quintile values of 20%, 40% and so on.

How to Build a Quintile Chart

I’ve recorded a short video on the process as there are quite a few steps.

For best viewing quality: press play then 1. click the cog and select 720p HD, and 2. click the icon on the bottom right of the video to view in full screen.

Other Uses

Make it a traffic light chart with just 3 dots.

Use this layering technique in other charts to turn on/off data displayed in your charts.

Want More?

If you produce reports in Excel and want to learn more charting tricks like this you might like to check out my Excel Dashboard course.

I only open it periodically throughout the year so if it's closed be sure to sign up for the early bird list to be notified when it's open again.

Thanks

Thank you to Mark Maher for asking this great question.