Challenge – Show Market Share Changes

I’ve been lax about posting lately. The two conferences in September and October have really knocked me off track. It’s not from lack of topics: here’s an article I’ve been sitting on for two months. Back in September, Chandoo presented a difficult problem: Visualization Challenge – How to show market share changes? Specifically, how to show the change in market share among five competitors, for two products.

Chandoo came up with a pair of stacked charts, with the stacked columns staggered laterally.

staggered stacked column chartstaggered stacked column chart

Perhaps the staggering and the connecting lines make it a bit easier to discern the edges of the different colored bars than in an unstaggered stacked column chart. But in general the charts have some deficiencies (these are my versions of Chandoo’s charts, by the way). First, the stacking of bars make it more difficult to judge the magnitude of the segments between top and bottom, and having two charts forces the eye to move too much to see all the data. The legends take up a large amount of space, though of course duplicate legends are overkill.

Here is Chandoo’s sample data, if anyone wants to play along.

chart data

I approached this challenge by first investigating some different chart types. The stacked column chart is less cluttered looking than the staggered charts above, but they don’t really improve on the spatial difference between the two brands, and the large space needed by the legends (we could delete one of the legends, of course).

stacked column chartstacked column chart

How about a Clustered-Stacked Column Chart? Below is the data arrangement and resulting chart. I’ve colored the data range using the scheme that Excel uses to highlight data when a chart series is selected: the blue range contains Y values, the purple  contains category labels, and green denotes series names. The two columns of category label data produces the two-layer labels (see Chart with a Dual Category Axis). To preserve the lateral spacing, the purple cells with the hatching pattern contain spaces. The rest of the cells in this table are blank.

clustered-stacked column chart

The separation of brands and the excess real estate required for the legend(s) are both improved, and it sure shows off a clever data arrangement. But the values and trends are no easier to compare than in the other chart types above.

Since we’re interested in trends, a line chart may help with comparisons. Labeling the series directly is less obtrusive than the legends, and more effective at identifying the data. The charts still look somewhat cluttered, and the slopes are all rather too shallow, flattening out the trends.

line chartline chart

Perhaps clustering the data instead of stacking it can provide a better perspective (see Stacked vs. Clustered). The trends are visible even though the charts have bars instead of lines. The legends are less obtrusive; that’s not an intrinsic property of clustered charts, but rather a consequence of having fewer series. The separation of the two brands still makes comparisons difficult.

clustered stacked column chartclustered stacked column chart

Maybe we can combine the good features of the line and clustered column charts. Enter the panel chart. The two charts below show the values and trends clearly, without a lot of clutter.

panel chartpanel chart

We still have the separation of the brands in the two panel charts above, but the openness of the panel chart allows us to display both brands in the same chart. This is pretty effective, perhaps the best of all the types I’ve reviewed.

Does anyone have any better ideas? Are there better approaches that I’ve neglected?

compound panel chart

In the next few days, I plan to write a set of instructions for building this chart. In addition, I thought this data would be a good vehicle to show a little exercise in data exploration, using pivot tables and pivot charts. I’ll comment on this post when I’ve added each of these pages, so if you subscribe below (comments are always welcome, but not needed to subscribe) you’ll receive a notification when I’ve updated.

Update 16 November 2008

I’ve written a couple of follow-up posts. In Explore Your Data With Pivot Tables I used this data to show how pivot tables can be used to investigate the data quickly and easily. Then in a second follow-up I showed How to Build a Simple Panel Chart.


Peltier Tech Charts for Excel


  1. I like the separate panel charts. The combined version does allow you to see all the data in one place but makes it difficult to compare one brand at a time (which I assume is the prime objective). Also, the natural tendency would be to compare the two values within each panel, and this isn’t a good thing unless you’re comparing both the change among competitors per brand *and* the brands to each other (which I suspect isn’t the case here).

    What would be nice (beyond this specific example): A VBA solution to construct panel charts, so that the chart can be updated via an event. The example probably isn’t a good candidate for a VBA solution since it’s unlikely that you will be adding new competitors often, if at all. However, if the category is months, you’ll need to update the chart regularly.

  2. Colin –

    Separate charts may be as effective as the combined chart. As always, “it depends”. It depends on what the focus of the analysis is. I can think of reasons why either way would be preferable, but these depend on what is important to the analyst.

    I am working out the initial specs for a panel chart utility. It’s a nights and weekends project, so stay tuned but don’t hold your breath.

  3. the slopes are all rather too shallow, flattening out the trends.

    Well, who’s responsible for that? :-) But I agree, it’s still cluttered.

    When chandoo ran the challenge, I thought of doing it as step graphs with the area between filled with red and blue for increase and decrease. But I found the result unsatisfying.

  4. How about aligning all the data series in the bar chart:

  5. I’ve taken the liberty of reproducing Dave’s chart here, but I suggest readers follow his link.

    exploded bar chart

    This is an interesting approach. The two products for each competitor are lined up, so they can be compared. The problem is that comparisons between competitors are still difficult. The magnitude of the change loses resolution as well, because the change is split between top and bottom of each floating bar.

  6. I tried some bars:
    Just the bars:

    nixnut market share bar chart

    With extra nuts:

    nixnut market share bar chart

  7. Nixnut –

    Those are good. You could do the left hand charts either as bullets (as you have done) implying 2007 is a target or benchmark for 2008, or as regular clustered bars with 2007 and 2008 adjacent. Thanks for reminding us that sometimes using multiple charts is better than trying to cram too much into one chart.

    You should also post this comment on Chandoo’s blog post.

  8. Re Nixnut’s bars: The market share charts are nice. But showing a per cent change of a per cent (the bars on the right)? Is that meaningful analysis? I would show the change in number of points of share. Also, some indication of the total size of each market would be helpful. With no data, you could infer that the size of both markets is the same. I have no idea if that is true or not.

    Jon, I also like the panel charts but showing the value axis would be helpful.

  9. @Bob: Meaningful analysis… well, that’s in the eye, or rather mind, of the beholder I guess. It’s a measure of performance, so some people might be interested in it. The change in numbers of points of share is available in the second example. I didn’t think it useful to express this as a graph as well, since it’s already visible (implicitly) in the left chart. I’ve created another variant with a table on the left and two charts next to it: (in-cell graphs this time, created with Fabrice Rimlinger’s sparklinesforxl)
    The size of the markets wasn’t available and I didn’t think to make some up. It was simply outside the scope of this challenge as far as I’m concerned.

  10. @Jon, Nixnut, Derek and Dave:
    Thanks for posting such marvelous examples for presenting this data. I have learning quite a few techniques and enjoyed it.
    Btw, I have posted a summary of this challenge here:

    do check it out as it presents few more alternatives and enables discussion :)

  11. Hi Nixnut,

    Your chart is impressive but i dont know how to make this. Can you plase send me some tutorial or any website reference from where i could know how to prepare this visual.

  12. Hi Waseem –

    I wrote a couple of follow-up posts last week. In Explore Your Data With Pivot Tables I used this data to show how pivot tables can be used to investigate the data quickly and easily. Then in a second follow-up I showed How to Build a Simple Panel Chart.

  13. @waseem: Jon has, of course, a splendid tutorial that explains how to create the overlapping bars. See
    The charts on the right displaying the rate of change are stacked bars, with the first segment having no fill colour.

  14. Come to think of it, why on earth did I use stacked bars there? A totally unnecessary complication.
    For a better approach see:
    Pay attention to how the chart data is arranged.


  1. DSA Insights » Blog Archive » Graduation Rate and Federal Spending [Chart Reviews] says:

    […] from 1998 to 2008.  Another option to visualize this data is in a chart like the one shown here by Jon.  In the table below, I simply took the data from the pie charts and created a […]

Speak Your Mind


Peltier Tech Charts for Excel 3.0