Area chart with levels - Excel-Croissant

posted Feb 1, 2015, 11:32 AM by Gábor Madács   [ updated Feb 1, 2015, 11:53 AM by Krisztina Szabó ]

Freddy: [...] Oh, I uh, you know I don't mean to embarrass you, but I'm rather a brilliant surgeon. Perhaps I can help you with that hump.
Igor: What hump?


by The FrankensTeam

The inspiration for this post came from Visualizing MBTA data project by Mike Barry and Brian Card where we found a great interactive chart under chapter How People and Trains Affect Each Other.
Here is a static screenshot about the chart:

http://mbtaviz.github.io/#interaction


Quote from the original study: "The gray bands show the total number of entries into the all stations per minute over time for each day of the week."

Each row represents one day, where the darker grey means more people. This graphic could be a good choice to visualize small and large data together, especially if you need to compare more data series.

To replicate the chart in Excel is very easy. The chart type is area chart with more series - as many as the number of grey belts you want. You simply need to shift the original data by a chosen number called limit (according to the number of grey belts). For example imagine that your original data is scaled from 0 to 2000. Draw the original chart (the lightest-grey) and choose 500 as limit - so you will have 4 belts. Cut the chart to 4 pieces: below 500, from 500 to 1000, from 1000 to 1500 and above 1500. Now put the 4 pieces on top of each other.
This is what you see in the orange frame below:



In Excel you can realize it by shifting the original data down by 0, 500, 1000, 1500 and put the 4 series to the chart. Simply create a data table and minus these numbers from the original data. Create the area chart with 4 series and set the axis limit accordingly: minimum 0 maximum 500.
Note: This way the slope of the area-parts will be correct. If we changed the data above 500 to 500 and below 0 to 0, the slope would not be correct because the lines would start from incorrect point on the horizontal axis.

We created an example using traffic data about the famous Hungarian “Line 1” which is the second oldest underground railway in the world (opened in 1896) and listed as UNESCO World Heritage.

Data (col. C) and chart series (E-H) of Station 8 (Bajza utca - Hősök tere):


and chart of Station 8:
(enlargement of the above orange frame)


As you can see, this kind of chart clearly displays the details for the whole range of values. The color emphasized 'wrapping' allows the easy visual identification of the higher value sections.

The final chart showing all the stations together looks like this:


We simply copied the charts to one sheet, and put on oversized chart with gridlines, legend and horizontal scale to the background.
You can download our file.

Now you are wondering why Croissant is in the title? Gábor named the chart because its layers are similar to this bakery product (in Hungarian we name it “kifli”)

Kris published a tutorial in Hungarian too.
You may also be interested in:

Leave a comment




ċ
kifli.xlsb
(146k)
Gábor Madács,
Feb 1, 2015, 11:32 AM
Comments