Creative and advanced chart design in Excel

posted May 24, 2012, 5:49 AM by roberto mensa   [ updated Jan 12, 2014, 1:30 PM by Krisztina Szabó ]
A big thanks to my friend Kris ...
She wrote this beautiful and amusing article collecting some our charts.

UPDATE: you can find 120+ creative and advanced charts with templates in our Excel Charts Gallery !

For leaving a comment :
Google+
Linkedin Excel Hero Group



Charts: haute-couture vs. pret-a-porter

In the past few month we created several charts and nowadays continuously have better and better chart ideas, so we decided to publish a collection of them. The context I’d like to use in this article is my hobby: fashion :-)

In fashion haute-couture means elegant, excellent, “high dressmaking”. These collections sewn with extreme attention to detail and finished by the most experienced and capable seamstresses, often using time-consuming, hand-executed techniques. Usually haute-couture is regarded as an innovative, reformer branch of fashion, but many times declared as unwearable. These trends will be used as a basis of pret-a-porter collections.

Jean Paul Gaultier spring 2010 collection


Jean Paul Gaultier Autumn Winter 2008/2009

Pret-a-porter means “ready to wear” - it is often more practical and informal than haute-couture. Standard patterns, factory equipment, and faster construction techniques are used to create these collections.
On the analogy of fashion, we would like to show you some haute-couture charts. These are just ideas. Some of them could be suitable for practical use, some are not. Exactly as in haute-couture… It is told to find a good chart one must throw away 10 - we show you 13+1 here so surely some of them will be applicable for real life purposes!
To make the decision easier, we write some sentences about the chart why it is special and use a subjective fancy-factor representing what we think about the chart.
We also mention the alternative of these haute-couture charts - please note, some of them are brand new, belike these will be the first charts chosen to be pret-a-porter.
The base chart and the tricks could help creating something similar - you can start fiddling with Excel or you can download some files. The level categorization could help where to start :-)

levels:
Easy
Advanced
Extreme
Savant-only


Springs

Similar to Gaultier Autumn Winter, isn’t it?



Why special: Use it to visualize time series. The height of the springs represents the value. You can fix the number of curves within the spring or the heights of curves - each gives a bit different picture.
Fancy-factor: 4
Alternative: Column
Base chart:xy Scatter
Level:Advanced (static version) / Extreme (dynamic version)
Tricks:We created two versions. The first one is static, all the data series for x and y values defined one by one as named formulas. The logic behind the named formulas is easy to understand, but you need to add new names if you have more data. The advantage is that you have separated series so it is possible to use different format for each of them. The dynamic version uses 2D array to make the series dynamic. If you add a new year with data, it will immediately appear on the chart. Disadvantage is that you can not format the years differently - but you can use gradient colouring to simulate something similar.
We use an invisible data series to make easier positioning the data labels. 
 Download:Springs.xlsx or at the bottom of the page by clicking on the small arrow on the right-hand side

Density dots

Size and population density of some Italian cities




Why special: Visualize the area and density of population of cities.
Fancy-factor: 5
Alternative: Brand new!
Base chart:xy Scatter
Level:Advanced
Tricks:We created two versions. The first one is static, all the data series for x and y values defined one by one as named formulas. The logic behind the named formulas is easy to understand, but you need to add new names if you have more data. The advantage is that you have separated series so it is possible to use different format for each of them.
Download: https://sites.google.com/site/e90e50fx/home/tangled-chart-with-excel

Gomitoli (tangled)

Simply WOW


Why special: Visualize the area and density of population of cities.
Fancy-factor: 5+
Alternative: Brand new!
Base chart:xy Scatter
Level:Advanced
Tricks:To have a circle-shape you need to use a SIN and COS value as x and y series. In this case we created a random array but with constant numbers, so there is no re-calculation as it was in the case above. We calculate SIN and COS of the same angle data, so the result will obviously a circle.
Download: https://sites.google.com/site/e90e50fx/home/tangled-chart-with-excel

Percentage

With a bonus optical illusion - do you see the little dots?



Why special: Visualize a percentage using 100 squares. Could be a great tool to compare percentages.
Fancy-factor: 5
Alternative: Brand new
Base chart:xy Scatter
Level: Easy
Tricks: It is really easy! No magic! The simple is the best! We use named formulas to create the arrays for x and y values for two series. The first series is the gray one - this is static, determines 100 data points by coordinates. The second one is the yellow one to represent the percentage.
If you reverse the x and y named ranges, the grey dots will be on the right-hand side.
Download:https://sites.google.com/site/e90e50fx/home/percentage-chart-with-excel

Twisted column and row

You can colour as rainbow :-)
Or the second in blue - as Marge Simpson’s hair :-)


Why special: Not a usual column-chart, seems like the wind is playing with the grass. There is just a solid bend - it should not confuse the data comparison.
Fancy-factor: 4
Alternative: Column
Base chart:xy Scatter line or only makers
Level:Advanced
Tricks: We struggled a little bit with the non-contiguous series. In Excel 2007 it is problematic to create a chart using named formula when you do not have data for some data points. But Roberto always finds tricky solutions :-) Only gradient coloring is not possible in this case in Excel 2007.
You can create some very funny effect using markers only!
Download:Twisted column.xlsx, Twisted rows.xlsx or at the bottom of the page by clicking on the small arrow on the right-hand side.

Cherry

It is a real business chart developed to analyse the product structure of the company by growth, profitability and sales volume.


Why special: In this chart we show 3 data for each product. Two percentage data (for example growth and profitability margin) measured on x and y axis represented by the dot, plus a size (for example sales volume) represented by the length of the line. This latter does not measured on a scale, it is there to make comparable the importance of products regarding the sales volume. So line is an alternative of the size of the bubble in a bubble chart. It could be useful when the bubbles are overlapped, but important to show the 3rd data.
Fancy-factor: 4
Alternative: Bubble
Base chart:xy Scatter
Level:Advanced
Tricks: There are no axes just invisible series with special data label (linked to cell) to represent the percentage intervals. The 3rd data (for example volume) has to be re-scaled to be in line with the data measured on the vertical axis.
Each product is one data series, so in this case a bit of manual work is needed to add the series to the chart.
Download:Cherry.xlsx or at the bottom of the page by clicking on the small arrow on the right-hand side.

Iceberg

Designed for an infographic-CV to represent the development of skills during time/positions.


Why special: This chart type gives more focus to the new elements. Could be useful when you need something eye-catching, not a simple, well-known area chart.
Fancy-factor: 4
Alternative: Area
Base chart: Area + xy Scatter combined
Level:Easy
Tricks:Simply use an additional invisible series to “lift” the series up.
The vertical lines are drawn using xy Scatter line - you can draw from the bottom of the chart - it would be easier. And as in some other charts, we added an invisible data series (xy Scatter) to make easier positioning the labels to the middle of the ranges.
Download: infographic_CV_kris.xlsx or at the bottom of the page by clicking on the small arrow on the right-hand side.

The Lisa Simpson chart

After Marge Simpson, it is a must!



Why special: Just for fun :-)
Fancy-factor: 4
Alternative: Pie
Base chart:Radar
Level: Extreme
Tricks: Because of the format there is significant rounding in the data to have the slices start and end according to the zig-zag.
Download:Lisa_Simpson.xlsx or at the bottom of the page by clicking on the small arrow on the right-hand side.

Rectangular pie and doughnut

Squaring the circle!




Why special: Very impressive format, however could be misleading because only the angle represents data, the area of the slices could not be compared.
Fancy-factor: 5
Alternative: Pie/Doughnut
Base chart:Radar
Level: Savant only
Tricks:trigonometric and magic!
You can change the angle of the slices and the size of the hole.
The chart consists of as many series as many data you have. One series displays one data, but it is cummulative data, so the series overlap each other - what you see as slice is always the difference between two series.
Download:https://sites.google.com/site/e90e50fx/home/square-pie-doughnut-chart-with-excel


Ferris wheel

Does not look like an Excel chart, does it? BTW, isn’t it a bit similar to Gaultier spring 2010?


Why special: Special format to make comparable the male-female data in the age-groups. The percentage is represented by the height of the slices.
Fancy-factor: 5
Alternative: Pie/Doughnut or Column
Base chart:Radar + Doughnut + Pie combined
Level:Advanced
Tricks: The slices are built up in a radar chart. We put zero values to separate the age-groups and make more visible the white lines. These white lines are the borders of a pie-chart. The blue arch with ages is a doughnut chart.
On the picture you see only half of the chart. It is a full circle in Excel, but the bottom part is empty, we use the bottom half of the pie to cover the unnecessary gridlines of the radar chart. idea from here:
http://m.blog.hu/in/infographics/file/11dec/who_is_using_google_plus.png
Download:This chart started a flow of some similar charts you can see here:
https://sites.google.com/site/e90e50fx/home/infographic-chart-with-excel
and here:
http://www.excelhero.com/blog/2012/05/excel-formulas-can-make-a-graph.html

Pie+ring

Two more for CVs




Why special: Great graphic-representation instead of boring text in curriculum vitae. The first one symbolizes the importance of work with a larger circle - could come out positive on a job interview!
Fancy-factor: 5
Alternative: Pie/Doughnut
Base chart:Doughnut charts combined
Level:Easy
Tricks:All curves are part of a full ring - the missing part coloured invisible. You can use percentage data for each curve, but half all the percentages and add an additional 50% to represent the invisible half-circle.
Download:infographic_CV_kris.xlsx or at the bottom of the page by clicking on the small arrow on the right-hand side.

Racetrack

Inspired by the Olympic Games


Why special: The racetrack could come out great in business environment. The points where the curve part becomes straight could represent a limit. The series which does not exceed this limit, regarded as failed (year 2007 in the example)
Fancy-factor: 5
Alternative: Bar
Base chart:Bubble
Level:Extreme
Tricks: This chart is built from named formulas based on trigonometric functions.
There are four data series: two for the curve part yellow/gray and two for the straight part yellow/gray. Gray dots form the track - they could be found under the yellows too. On this chart the highest data is always the “winner” - it goes till the end of the track, while the others measured comparing to this maximum.
Additional series make easier positioning the data labels.
 Download: https://sites.google.com/site/e90e50fx/home/racetrack-chart-with-excel

Necklace

Started with fashion - finish with fashion!
Why special: Simply beautiful.
Fancy-factor: 4
Alternative: Pie
Base chart:Bubble
Level:Extreme
Tricks:The chart is built from named formulas based on trigonometric functions.
There are two data series: one for the perls and one for the gray dots. The gray dots form ¾ of the circle, so they are below the pearls too. The highest data always forms ¾ circle, the rest is measured proportionally to this maximum.
An invisible series makes easier positioning the data labels.
Download: https://sites.google.com/site/e90e50fx/home/necklace-chart-with-excel

+1 The-Chart-Which-Must-Not-Be-Named

It has a name... but... I do not want to publish.


Why special: No comment. :-)
Fancy-factor: 4+
Alternative: Column
Base chart:xy Scatter line
Level:Easy
Tricks:xy Scatter chart is a huge playground to create fancy, funny charts!
Download:name_inpronunciabile.xlsx or at the bottom of the page by clicking on the small arrow on the right-hand side.

Epilogue

Please do not regard these charts as a best practice or recommendation. These are just ideas - as in haute couture. You can use if it gives a little “salt” to your work, but always respect the data and your target audience - never forget the basic rules: data derives chart choice.

As it was told by the famous French fashion designer, Coco Chanel:
“Simplicity is the keynote of all true elegance.”

by Frankens Team
Ĉ
roberto mensa,
May 24, 2012, 5:49 AM
Ĉ
Krisztina Szabó,
Jul 2, 2013, 2:36 PM
Ĉ
roberto mensa,
May 24, 2012, 5:49 AM
Ĉ
roberto mensa,
May 24, 2012, 5:49 AM
Ĉ
roberto mensa,
May 24, 2012, 5:49 AM
Ĉ
roberto mensa,
May 24, 2012, 5:49 AM
Ĉ
roberto mensa,
May 24, 2012, 5:49 AM
Ĉ
roberto mensa,
May 24, 2012, 5:49 AM
Comments