Getting the formatting in your chart right can make the difference between clear, concise information and just a pretty picture.
Take a look at this stacked column chart using some of the built in Excel chart formats:
What's your initial reaction?
Here are some of mine:
- Striking use of colour
- It looks nice
But then the analyst in me starts to try to make sense of the chart and glean information. Here’s what I find:
- I have to flit between the legend and the columns to figure out which colour represents each region. Then I have to try to remember them while I analyse the chart.
- I wonder if the chart is displaying sales units or monetary value
- I have to turn my head to read the horizontal axis labels
- I can see the trend for the South series in blue as it starts on a level base but the other series are all affected by the height of the blue series.
- It’s difficult to tell how the other series performed
- It’s difficult to compare the series against one another
- I’m having to work hard to get any useful information from this chart.
- I’m getting bored…. I might just admire the colours
- Ouch! The yellow, orange and blue on the dark background hurt my eyes if I try to focus on them
- Actually Chart, you is UGLY!
If you produce charts that initially look nice, but make the chart recipient work hard to get any meaning from them then your report recipient’s initial reaction will be similar to mine. And unless they are really motivated to understand the data they’re likely to end up at the ‘getting bored and admiring the colours’ stage pretty quick.
The worst thing is you may never know because they probably won’t tell you. It’s our job to make meaning from the data and communicate that message in the quickest way.
Let’s take a look at our Ugly Chart’s makeover:
Before and After
I hope from this you can see the importance design and formatting have on your charts. I teach these fundamental data visualisation rules and more in my Excel Dashboards course. Why not take a few minutes to check it out.
Interactive Excel Formulas
Glenn Case
Interesting article. I especially like the way you reformated the horizontal axis. I have a couple comments:
1) While I appreciate the simplicity of the new colors, since there appear to be several shades of gray this could be confusing if the lines on the chart crossed one another.
2) I alway like to see grid lines, both horizontal and vertical, on a chart. Otherwise, it is very difficult to read with any accuracy. If all you are after is trend information, then fewer or no gridlines is okay. I find that using gridlines in a subdued color makes it easy on the eyes, plus easy to read accurately.
Thanks for the article.
Mynda Treacy
Cheers, Glenn.
I agree you could differentiate the colour a bit more if the lines are crossing over.
Like you say, if the trend is what is relevant/of interest then no gridlines are fine, but if the detail is what’s important then I will label the points so that I’m not making my chart reader work hard to guess at values based on the gridlines.
Mynda
Jef
Great idea. Good source of information. Simplicity is good. Thanks for sharing.
Mynda Treacy
Thanks, Jef
Paul Fogel
Mynda,
How did you create the X-axis?
Mynda Treacy
Hi Paul,
It’s a nested axis that references 2 columns. I teach that technique in my Dashboard course.
Kind regards,
Mynda
Jon Acampora
Another great example Mynda! I agree, the colors of the original chart remind me of a sunset and I would probably start day dreaming if this was presented in a meeting.
The major problem with the stacked column chart is the uneven baselines for each series. Like you said, it really makes it difficult to see any trends. It is much easier to see the trends in your After chart. If the lines of each series were crossing, I would recommend creating 4 separate panel charts (one for each series) and stacking them. This would allow the reader to really focus on each trend.
Sometimes we try to fit too much data into one chart for the purposes of a PowerPoint presentation, and it distracts from the important messages. Thanks again!
Mynda Treacy
Hi Jon,
You nailed it with the Sunset! Good points about the Panel Charts. They are usually my preference too. Thanks for sharing.
Mynda
Mike McEldowney
Your newsletters are so informative. I love it when I see a new email in my inbox. You have a way of presenting the information so that it is easily understood, easy to remember and easily put to good use. I used to think pretty was effective, but you have shown me the “error of my ways”! Thank you for all the help you have given to the Excel community.
Mynda Treacy
🙂 Thanks for your kind words, Mike. Great to know you’re designing charts with ‘more’ nowadays.
Chandeep Chhabra
Hi Mynda,
Really cool explanation. I agree and follow to whatever you have said, each day each time.
Thanks
Chandeep
Mynda Treacy
Cheers, Chandeep 🙂
MF
Hi Mynda,
This is so true in workplace. I am sure that when both (before and after) charts are presented to audience, they tend to like the former at 1st glance; and then they realize that they get nothing out of it. That’s the temptation for the chart maker to make sure “fancy” chart instead. 😛
btw, I think the chart (after) can be improved further by adding the grand total line on top. I know it involves more efforts though.
Cheers,
Mynda Treacy
Thanks, MF. We have to be strong to not give in to the temptation of the fancy chart’s beauty and always look to provide substance 🙂
MF
Can’t agree more!
winston snyder
@Mynda,
While also enticing the reader to study and undertnad the chart as well as remember and take something of value away from the chart.
More from Robert Kosara chart junk considered useful
Mynda Treacy
Thanks for sharing the link, Winston. Interesting read.
From my point of view info graphics and chart junk are the domain of newspapers and the like where entertainment is the goal, whereas for the business analyst using graphics that obscure the data and other ‘chart junk’ end up hindering interpretation and further understanding of the data.
Mynda