Former Data Schooler Nicco Cirone posted a challenge to the team at The Information Lab Monday to create this alternative to a Marimekko chart.

Click the image for a larger version

Nicco got the idea from this post by Jon Peltier about the problems with Marimekko charts. Give it a shot. It’s a bit tricky and one you will surely learn something from. There are several very subtle tricks in here. Try to match mine pixel for pixel.

If you want to use the same version of Superstore Sales that I used, you can download it here. If you think you have it and want to check to see if you got it right, you can view the final solution on my Tableau Public profile here.

Good luck!

I love Jon Peltier’s Tech Blog.  He always has great tips for getting things done in Excel and his recent post “Easy Two-Panel Column Chart in Excel” is no exception.  In this post Jon walks you through how to create this chart in Excel:

Seems pretty straight forward, right?  Well not exactly.  I wouldn’t exactly call this technique easy.  In Jon’s post, I quickly counted about 13 steps (I think there are more; I lost count as I recreated the chart), but there are many clicks for each of these steps.  It took me a bit over five minutes to create it the first time, but this included flipping back and forth between Excel and the instructions.  I suspect it would take two minutes or less once you use this technique a few times.  Again, let me emphasize that this is a great technique for Excel.

One big drawback for this technique though is that you are forced to hard code the scales.  What happens if your data changes?  You’d need to go back into each axis and reset them.  Kind of a pain if you ask me.  I’m sure I’d forget at some point too.

Also, I don’t like how the axis scales are not on the same side of the chart.

How many clicks it would take to create the same chart in Tableau?  Step-by-step, here are the instructions (with click counts in parentheses):

1. Drag the Month dimension onto the Column shelf (1 click)

2. Hold the CTRL key and choose both the Primary and Secondary measures, then drag them onto the Rows shelf (3 clicks)

Two steps/Four clicks and we’re already 95% of the way done.

3. Drag the Measure Names dimension onto the Color shelf (1 click)

That’s it!  Five clicks and we’ve created a two-panel column chart in Tableau.  I timed myself and it took five seconds to create this chart.  Of course, you need to spend the money on a copy of Tableau.

In summary, let’s assume:

1. It takes two minutes to get the chart exactly as you want it in Excel (but you still have to fix the scales every time you change the data)
2. It take 30 seconds on Tableau (though I can’t see how it could take that long and you DON”T need to update the scales each time)
3. This gives you a 75% time reduction in creating just this one chart

Think about all of the time you would save if you did EVERY chart in Tableau.  This makes the ROI story for Tableau pretty simple if you ask me.

This past week, I was watching a presentation on Q3 performance and up pop a bunch of charts that were clearly created in Excel with smoothed lines.  I hadn’t seen smoothed line charts in quite a while, so I was taken aback.  I almost, but thankfully didn’t, stand up and call out the junk.

It was incredibly clear to me that the smoothed lines were distorting the data, not much, but distorting it nonetheless. And I have a problem with THAT!

Let’s first take a look at some examples to see how badly the data can be distorted.  The first chart is obviously the smoothed lines.  Nice and pretty, I agree.  It makes me feel like I’m going up a chairlift then skiing down the slopes of Keystone, Colorado.

I added the gridlines, though I would never do this if I were presenting this for real, so that you can see where the points truly intersect.  It should be abundantly clear now that the line is trying to connect points that don’t exist.  Look between July & August 2009 or between August & September 2008.  In both of these instances, and many more across the chart, the lines go beyond where they should in an attempt to make the chart nice and smooth.

If I were to look at this quickly, I might think that my sales increased from July to August 2009, but in fact, there was a slight decrease.  In order for the line to connect smoothly to September 2009, the line has to go around August 2009.  Think about all of the people that don’t used zero-based axes.  Imagine how distorted their data could look.

Contrast the smoothed line chart to this standard line chart.

You now easily see that sales decreased from July to August 2009.  It’d be tough to interpret anything from this chart between the months because the lines clearly connect month to month.  The smoothed lines lead you to believe that there is more data being connected.

Now, let’s look at how the smoothed and straight lines look on the same chart.  For illustrative purposes, we’re only looking at 2008.  Now that dip after August really stands out. 

Jon Peltier of the Peltier Tech Blog sums it up best in his post about the charts to choose and avoid in Excel 2010:

Smoothed lines are abused. If you are plotting measured data, the only valid connecting curve between points is a straight line (or a line which is fitted to a function that comes from a physical model of the data). A smoothed curve implies that the data goes places where it has not been measured. Smoothed lines without points are even worse, because the person trying to interpret the chart doesn’t even know what points on the smoothed curve belong there.

My advise?  NEVER use smoothed lines.  The ONLY possible outcome is misinterpretation.

Let me wrap up with what I find to be a bit of a funny line from Microsoft’s help for creating smoothed lines:

When you use this procedure to soften the jagged edges of a line chart, your data is not affected.

This is very true.  Your data is not affected, it’s merely misrepresented.  Semantics?