Stacked Column Bar Chart Alternatives – Find the Missing Trends

Stacked bar or column charts are used a lot by the media and corporate world.  I believe they are popular because they display a lot of information in one chart, and are relatively easy to create in Excel.  However, I'm not a big fan.  I'm going to explain why I consider them to be “bad charts”, and present some alternatives.

Bad Charts

I consider the stacked column chart above to be a “bad chart” because it doesn't do a good job of displaying the trends in the data.  There are a total of six data series displayed here; the five regions plus the total.  But we are really only able to see trends in two of the six series.  We can see trends in the North America and Total (height of entire column) series because the baseline for these series is flat.  The baseline is basically the x-axis at $0.  This is very important when creating a chart that is displaying a trend.  Our eye is able to distinguish the baseline as the starting point for each column, and then compare the top point of each column moving from left to right.  This works great for the North America and Total series, but it is very difficult to see the trends for the other four regions.

Uneven Baseline

The image above shows the difficulty in quickly seeing the trend in sales for Asia because the baseline is uneven.  I emphasize “quickly” because we want our chart to clearly and easily communicate a story or trend.  It would probably be easier to just look at the actual numbers to see a trend versus trying to determine if the bars are increasing or decreasing in size over time.  And these bars for Asia get hidden when you consider all the other regions that are also stacked above and below it.  It's an absolute mess! 🙂

Here is a line chart that shows the actual trend for Asia.  It's much easier to quickly see that sales are declining over the last three quarters of the year.  This would probably draw attention to the performance of the region, and spark some action to be taken to correct the problem.

When to Use Stacked Charts

Stacked charts can work if there are drastic changes in your data over time, and you want to only display the trend of one or possibly two series.  It's best to highlight these series in a color that stands out, and add some text to describe the trend.  You also want to move the series to the bottom of the chart so it sits on the baseline, making it easy for the reader to see the trend.

Alternative 2 below also makes use of the stacked chart by giving it a dynamic baseline to quickly analyze trends.

Solutions

Now that we have an understanding of when NOT to use stacked charts, we can start thinking about alternative methods that clearly and quickly tell the story.  The use of these will depend on what trends you find in your data, and how you want to convey the message.

Alternative #1: Panel Charts

Panel charts are a group of small charts organized together in a panel.  This is a good way to break out each region into its own chart.

Now we can start to see trends within each region.  These trends are not possible to see in the stacked chart because of the uneven baselines, and scale of the chart.  Sales in North America and Europe are much greater than Asia and Africa, so the trends get lost.  There are some important trends getting lost if you don't look at them closely.  You can highlight those trends in a panel chart as I did above.  We can see that sales in Asia are declining, while sales in Africa have almost doubled over the year.  The dollars are small compared to total sales, but there might be great opportunities being missed if this is only analyzed in a stacked chart.

If you really want to impress your boss, you could add zoom buttons to each panel using the Zoom on Charts Macro (available for free download).  This is a great feature to add to an interactive dashboard.

Alternative #2: Dynamic Baseline Stacked Column or Bar Chart

You might like this solution if you want to keep the stacked chart, or don't want to freak out your audience with something drastically different than they are used to seeing.  I call this one the dynamic baseline stack chart because it allows you to quickly choose the series to display at the bottom using a drop-down menu.  It is pretty easy to implement.  NO macros or VBA are required.  Just two different formulas and a drop-down form control.  The workbook that contains the form is available for free download below.  Instructions on how to create this chart are included in the file.

When the West region is at the top of the column stack it is difficult to see any trends.  But when the West is moved to the baseline (bottom) series, we see that there is a steady decline in the last half of the year.

There are also some correlating trends between South and West in the second half of the year.  This is easier to see when we move East to the top of the stack.  This might be another way to present your data depending on the message you want to convey to the readers.

Alternative #3: ???

I'd love to hear about some alternatives from you.  Make it as simple or complex as you like, there's no right or wrong answer.  Just remember that the goal is to clearly and quickly tell the story to the reader.

Thanks!

Download

Additional Resources

If you're looking to learn more about charting, I highly recommend the dashboard course from My Online Training Hub.  I have taken this course and it really helped me improve my charting skills.  Checkout my video review of the course to learn more about it.