Stacked Thermometer Chart

January 13th, 2011 by datapig Leave a reply »

Wendi wrote me an email the other day asking:

"Datapig you are best Excel persons in the world ... how to make thermometer chart with a stacked column?"

My first international lady fan!  Could it be true?  Could I be offically too sexy for just the Continental U.S.?

While I ponder that thought, here is Wendi's answer.…

.

In a thermometer chart, you have a performance indicator overlaying a goal of some sort. This allows the reader to quickly get a glance of the variance between actual performance and goal.

.

.

Wendi wants to add another layer of analysis by making the performance indicator a stacked column. This lets her audience see not only the variance between cumulative performance and goal, but also the magnitude impact of each component that makes up the performance.

..

..

Here are the steps I took to get to this stacked thermometer chart.

.

Step 1:

Create a data set that details all component performance numbers along with a cumulative goal.

..

.

Step 2:

Create a stacked column chart from the data. You'll get something that looks like this.

.

..

.

Step 3:

Switch the chart axis to look at data by columns.

.

At this point, you chart will look something like this:

..

.

Step 4:

Now you will need to place the data points for each component part (in this example, North, South, East, West) on the Secondary Axis. Go to the Layout Tab on the Ribbon and use the Chart Elements dropdown (all the way to the left) to select the first component part.

.

Once it has been selected, right click and choose Format Data Series.

.

On the dialog box, select Series Options and click 'Secondary Axis' .

..

Now repeat this step for the rest of your component parts (in this example, South, East and West).

..

.

Step 5:

Once you have all your component parts on the Secondary Axis, go ahead and delete axis on the right.

Both your primary and secondary axes are on the same scale so here is no reason to manage two axes.

Simply select the axis on the right and hit delete.

..

At this point, your chart should look like this.

..

.

Step 6:

The final step is to make the Cumulative Goal wider than the other data points.

Right click on the cumulative goal series and select 'Format Data Series'.

On the, select Series Options and adjust the 'Gap Width' until the Cumulative Goal is wider than the component parts.

.

At this point you chart should look similar to this:

..

.

Step 7:

Format and change colors.

.

.

There you go Wendi – only 7 steps.

Now send me some cookies or something.

Advertisement

6 comments

  1. Michael Myett says:

    I was playing around with thermometer charts after reading this blog post and wound up thinking about what I thought was an interesting question. Is there any way to apply conditional formating to a data label? I have the data label referencing a cell that contains a formula which calculates the percentage of the goal reached and would like to conditionally format it based on the result in the referenced cell to show up based on that value and place several instances of this data label along the chart with different conditional formating. Is there a way to do this?

  2. Tony says:

    Mike – this is a good walk through of how to create the multiple series within the column. I tend to think the direction is not the best route to show this data. I would rather see separate columns for each segment with another series added to show how the cumulative amount measures to the goal. When I look at your stacked series within the chart, it’s very difficult to see how each compares to the other – the same argument for stacked bars, which are seldom a good design approach. Cookies are on the way…

  3. AlexJ says:

    RE: “Datapig you are best Excel persons in the world …”.

    “Persons” ??

    Did you (you guys?) finally get that VBA cloning algorithm debugged?

  4. Josh says:

    Great post! It made me want to give it a shot in Xcelsius as well…

    Xcelsius example: http://www.data-ink.com/stacked_thermometer.swf

    Source Files: http://www.data-ink.com/downloads/stacked_thermometer.zip

  5. Neil says:

    Mike,
    How would a thermometer chart (if at all it were possible), show that the actuals went over the target?

  6. tim debell says:

    Thanks! It took a couple times but pulled this together to a great finished product!

Leave a Reply