Stacked column flow chart with Excel

posted May 12, 2014, 8:39 AM by r   [ updated May 22, 2014, 6:21 AM ]
Freddy: This is the moment. Well, dear, are you ready?
Inga: Yes, Doctor.
Freddy: Elevate me.
Inga: Now, right here?
Freddy: Yes, yes, raise the platform.
Inga: Oh, the platform, oh that, yeah, yes.


by The FrankensTeam


I had seen a very interesting chart in an Italian daily paper. It shows the distribution of income in Italy in different years. The data is % so basically each year forms a 100% stacked column with small gaps between the parts. But this chart is more than a simple stacked column chart!
First of all, all the columns are sorted, then the parts are connected, forming a stripe-flow. These stripe-flows make clearly visible how the rank position changes year by year. It's similar to a composition of stacked bar and line chart.

I immediately wanted to create the Excel version of it!


You can see in 3 year the "platform is raised", so the stacked columns start from different point. In the original article there was no explanation about the purpose of this "platform raise", so we think this was added just for aestethic reasons. Anyways, we incorporated it into our version:



We downloaded the usage of the top 7 mobile operating systems from 2010 to 2014 to create a chart with more data series.
Here is the "platform raise" version:



...and the 0-baseline version:



In our file the data table could be found on Data sheet, below the table you can set the "platform raise" numbers so, the chart will be updated accordingly:

Source of the data: http://gs.statcounter.com/


How does it work?

The chart is built up to be able to work dynamically with a 8x8 data table, so if you add new years, those will immediately appear on the chart.

You can change the order of the colored stripes by changing the order of the series in the Select Data Source dialogue box. The topmost series in the dialogue box will be in the background, so all the other lines are above it.



The formula technique behind the chart is similar to what we developed for Cosmographs: draw lines on an xy scatter chart. Each colored stripe comes from one data series and built from 100 back and forth lines. All the necessary calculations are on Support sheet: there you can find a huge table full with numbers starting from cell B17. This table contains the data for all the lines for all series. Using OFFSET function in named formula we select which 100 rows build a particular series.
On the chart you can see additional series to draw the white lines which separate the years, and also 3 more series for the category, year and data labels.

This amazing sorted stripe-flow technique is a very powerful tool to visualize elections: It makes easy to follow how the % and the position of each party changes by time. We are looking forward to see the result of the European elections ... maybe someone can use this chart to present the proportions of the main forces?

You can download our Excel file to learn this new chart type.

UPDATES

Version 02

This version is not limited to percentages with max 100% in total. You can use any values in the data table.
You can download our Excel file



You may also be interested:
Life Expectancy by Nathan Yau's chart with Excel
Edward Tufte's Slopegraphs in Excel

Cosmograph in Excel - World migration with bilateral flow chart
Talent traffic chart with chord diagram in Excel


Leave a comment



Comments