4 Charts Same Data

Introduction

UPDATE2: Mynda has very kindly worked through my revised file and provided some more feedback, for which I am grateful. Please note her comments on the Then v Now chart … I have provided the correct version of the file now with the properly prepared chart. Apologies for dragging you here and wasting your time with my inefficiencies! The file you want is v3 now and I have deleted v1 and v2 to save my confusion if no one else’s!!

UPDATE 1: Mynda has very kindly evaluated this page and my attached file. Consequently, I have updated the page and the file. In addition to making corrections and adding explanations, I have added a Then v Now chart to my file. I think one of the interesting things about this discussion is the data that Mynda chose to explore since the major category is so dominant that it is, in a sense, an outlier.

I am very grateful to Mynda Treacy at www.myonlinetraininghub.com for giving me the inspiration for this page. Take a look at this entry in Mynda’s blog to see what she did: http://www.myonlinetraininghub.com/4-ways-to-chart-the-same-data#comment-22584. That will give you some idea behind the motivation for this page.

Mynda starts her entry with these words:

“Often when you’re charting data it can be tricky to find the best chart for the job.

If you get it wrong at best it can be a waste of everyone’s time and at worst it can be misleading.

That’s why you need to know the motivation for the chart up front. What is the question that the chart is supposed to answer?

Of course sometimes you will be analysing data to look for patterns and a story to tell. In either case, knowing which chart displays what type of information is important.

Here we’re going to look at 4 different ways to present the same data and the different questions each chart answers.”

Then Mynda demonstrates how she used panel charts, sparklines, Line Charts and Bar Charts all based on the same table of data. You will see these two examples in that entry:

Sparklines

What I did and why I did it

What I felt as I was reading through Mynda’s page was that her graphs were very good: everyone reading what she did should be happy. However, I felt the data she was working on didn’t do justice to the graphs: everything was dominated by the White category.

To try to overcome the weakness of one overbearing data series I created a rate of change panel, I changed the axes of the sparklines and I created a new graphic: by applying conditional formatting to a transposed version of the table of data Mynda was working with.

Rates of Change Panel

Given the nature of the data being presented here I thought that the original graphs were not so meaningful for all but the White category. I think the rates of change data and graphs provide a very useful insight. I can see that the years go from 2004 to 2013 but there seems to be a year missing!

Index Based Small Multiples

In the new version of my file, I have added small multiples to illustrate the data in the form of index numbers to respond to a comment Mynda made. The comment was valid but I saw it as an addition to my rate of change graph not as a replacement of it.

 

I think I should add that indices can start at year x, 2004 in this case, then move up and down over time. Alternatively, they could start at, say 2013 and be revised backwards. Alternatively, you could use the base year of, say 2010 and express years before and after that as appropriate. Many possibilities.

Sparklines with Automatic Axes

There is nothing wrong with the sparklines that Mynda has presented except that the White category dominates everything again.  Therefore, I changed the vertical axis on all sparklines to make them automatic. I know that this is not perfect except that at least we can see the bars and behaviour now in all categories.

I did say my version of the spark lines was not perfect but Mynda pointed out that it essentially invalidated the data: she was right. Here is the revised spark lines table and in this case I have started all series at zero: what this does is equalise the categories to overcome the weakness of presenting them with White being so dominant. If, of course, you don’t like what I did, fair enough; but here it is anyway!

Conditionally Formatted Table

What I did here was to apply conditional formatting to the original table of data … I formatted data bars with different colours, all starting at zero and using different colours for each category. I also asked Excel to hide the values so all we see is or are the data bars. This table, not the one shown above, is now colourful and easy on the eye!

However, I thought that by transposing, rotating, the table, we would get even better conditional formatting results and you can see whether I was right by looking at the revised version of the table, above.

Open my version of the file, see the link below and select any of the data and take a look at my conditional formatting options.

I think the rotated table and formatting is a great improvement both on the raw data and the first version of the conditionally formatted table on this tab.

Mynda didn’t like this table saying that if you have to explain a chart it can’t be any good. Well, I got the idea for this the other day when I came across someone’s presentation of the major components of a balance sheet. Essentially, the used conditional formatting to show a common size balance sheet under the major headings of fixed assets, current assets, equity, long term liabilities and current liabilities. It works well. For that reason, I didn’t share Mynda’s concerns. However, I have presented another version of that table in which is show SEVERAL ways of using conditional formatting to highlight the data and the trends. I wouldn’t necessarily recommend using all of them at the same time, of course.

NEW: Then v Now Chart

chandoo,org has the strap line, to make you awesome in Excel. It is a very good site with over 1,000 pages of Excel goodies. One thing I found there a few months ago is the Then v Now Chart and I have added that to the list of possible modes of presentation to consider. I know, some of you might not like it and others, like me, might love it. Take a look anyway!

Click and hold the drop down box to reveal all of the categories and select one to show in the chart. This is a very clever chart, in my opinion!

Conclusions

This page is meant as an enhancement of Mynda’s page and not a criticism of it in any way and I hope readers of both bogs will find value in both Mynda’s page and my own.

Download my version of Mynda’s file here:

updated version excel_panel_chart_dw_v3

Duncan Williamson