Friday, August 24, 2007

Excel Stacked Bar vs Stacked Line Charts Interface Error

Excel makes an incorrect decision when graphing stacked line charts. To see why, see the first chart. This shows a stacked bar of 4 categories over time. Simple insights:

  • blue appears first
  • then purple shows up
  • at the end, blue falls away while yellow and teal show up

One problem with this graph type is if you are showing a histogram with many data points (say weekly over several years), the bars start to clutter the area and the graph starts to look ugly, for example:

A stacked line would be much better.... so let's just change the graph type to stacked line.

What's wrong with the below?

This is the same data, yet Teal appears to be the dominating trend, even though in reality it doesn't show up until point 13. This is because Excel orders the stacked lines in the order they appear. In other words, behind the teal line is a blue line, a pink line, and a yellow line for different lengths. There is no way to reverse the appearance order (ie no "Order ->Send to Back" for data series). If you reverse the order of the series themselves, the result is this:

Fundamentally you want Blue on the bottom, followed by Pink, Yellow, then blue. The stack order conceptually should be by appearance, size, and/or causation.

This is either a mistake, or a conscious design error.

Excel Data File


Juan C. said...

I'm missing something...

You "Make these programs your bitches. Stop using the mouse, and look down with disdain on your peers who don’t know as many keyboard shortcuts as you. Crush your excel models, have them driven before you, and hear the lamentations of your workbooks", so I guess the answer is not as simple as double clicking on the any of the lines (yes, I use the mouse, so look with disdain), and choose the tab "Series Order", then change the order of the series?

I downloaded your file, and in 20 seconds had the series in the order you describe: blue, pink, yellow, teal.

Of course is NOT an Excel design error. The tool shouldn't be making any assumptions about the stacking orders, as for different uses, you'll need different orders.


Anonymous said...

Ok here's what i need. I've been racking my brain for about 4 hours now and cant seem to figure this shit out.
I need to create a clustered column chart, but each bar needs to be stacked so i can compare multiple year revenues across different regions along with revenue targets as stacked, so i can visually show if we missed or topped off our targets. shit, does this even make sense?

Consultant Ninja said...

I think that's what you want. Good luck; the worst thing they can do is fire.

Consultant Ninja said...

you. The worst thing they can do is fire you.

Consultant Ninja said...

Juan C (1 year later) misses the point. It's trivial to make the series blue, pink, yellow, and teal as you describe; but what results is the last chart on the post; a chart that violates basic gestalt and data visualization principles. When building a stacked chart, the series with the largest magnitudes should (almost always) go on the bottom.

More fundamentaly, he doesn't get that Excel is making an assumption about the right stacking order, because it has a default stacking order - that IS an assumption.

Bleh, this is why I hate friday night red-eye flights. What sick manager asks for an update and then says, "don't worry, you can send me the slides on Saturday morning?"

Anonymous said...

Thank You Consulting Ninja. You are the shit, that stacked/histogram bar graph link is exactly what i was looking for.

Sam said...

Have you heard of "River Charts" is this just another form of a stacked line chart?

Fariel said...

HI! I need to make a stacked histogram...with power generation tariff costs separately identified as different stacks by fuel type...any ideas?



Friday, August 24, 2007