Target vs Actual

Target vs Actual Dumbbell Plot

Monday, April 1, 2024 by Jon Peltier Leave a Comment

Monday, April 1, 2024 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Many of the charts we create compare actual performance to a predefined target. This tutorial shows how to create a “dumbbell” chart that compares actual and target levels of performance, and clearly highlights which performers surpassed their targets and which fell short.

A dumbbell chart consists of pairs of dots connected by a line, resembling the dumbbells found in the weight room at the gym. The dumbbells are more commonly aligned horizontally to make labels easier to read, but they can also be aligned vertically.

Bar Chart

We could make a simple target-vs-actual bar chart with three columns of data: name, target, and actual. But this chart is rather dark with all of the ink that colors the bars, and I think the alternating bars are not easy to read.

In Target vs Actual Thermometer Chart, I showed an easier-to-read style of bar chart called a thermometer chart. In this article I’ll demonstrate a style of dot plot called a dumbbell plot. A dumbbell plot consists of pairs of dots connected by a horizontal line.

Dumbbell Plot

Our dumbbell plot needs more columns in our data range. In addition to the three columns used above (Name, Target, and Actual), we need Above, Below, and index columns for the color-coded dots, a Delta column with data for horizontal error bars to connect the dots, and a column with the labels we’ll apply to the dots. The formulas used to construct the data are shown below the data range.

Select B2:C14 and insert a bar chart. I’ve filled the bars with a light shade of gray so steps I’ll take next are easier to see (below left).

As happens every time we insert a bar chart (see Excel Plotted My Bar Chart Upside-Down and Why Are My Excel Bar Chart Categories Backwards?), the vertical axis labels are in the opposite order from how they appear in the worksheet. It’s actually easy to fix. Double-click on the vertical axis, select Categories in Reverse Order and Horizontal Axis Crosses at Maximum Category (below right).

Insert a bar chart, and turn it upside down

We need three sets of X values for the dots: Target in column C, Above (Actual greater than Target) in column E, and Below (Actual less than Target) in column F; the Y values for all three series of dots are in column G. We have to add the series one by one, starting with Target. Select C2:C14, hold Ctrl while selecting G2:G14 so both areas are selected, and copy (Ctrl+C). Select the chart and use Paste Special to add the data as new series in columns, with series name in the first row and category labels in the first column. A very small new bar chart series appears, the orange specs right next to the vertical axis; the legend entry is “index” (below left).

We need to change the orange bars to dots. Right-click the gray bars and choose Change Series Chart Type. Find the “index” series in the list, and in the Chart Type dropdown select the XY Scatter option with markers only. I’ve formatted these markers with a black outline and white center. Notice the secondary vertical axis along the right edge of the chart (below right).

Add the first set of data for the dots, then convert to XY Scatter

The series shouldn’t be called “index” since it’s the Target series, but that’s how Excel parses the data when we paste it into the chart. Select the series and see what is highlighted: the series name has the red border and light red fill. You can simply drag the highlight from cell G2 to C2 and the series name will change.

Chart data range with wrong series name cell highlighted

Here is the highlight in its appropriate position.

Chart data range with correct series name cell highlighted

You could also edit the SERIES formula of the chart series, changing the first argument from G2 to C2, or changing this:

=SERIES(Sheet1!$G$2,Sheet1!$C$3:$C$14,Sheet1!$G$3:$G$14,2)

to this:

=SERIES(Sheet1!$C$2,Sheet1!$C$3:$C$14,Sheet1!$G$3:$G$14,2)

The corrected chart is shown below left.

Select E2:E14, hold Ctrl while selecting G2:G14 so both areas are selected, and copy (Ctrl+C). Select the chart and use Paste Special as before with new series in columns, series name in the first row and category labels in the first column. A new set of dots appears (Excel remembers that we just changed the previously added series to dots, saving us a few steps), and I’ve given them a black border and blue fill. Change the series name from cell G2 to cell E2 (below right).

Target dots correctly labeled, and Above dots added

Copy F2:G14 and use Paste Special as before to add the third set of dots, which I’ve formatted with a black border and orange fill. Change the series name from cell G2 to cell F2 (below left).

Double click the secondary vertical axis (right edge of the chart) and change the maximum scale to 1. Now the dots line up perfectly with the Target bars and the primary vertical axis labels (below right).

Below dots added, and all dots aligned with Target bars

Now we can hide the secondary vertical axis. Double click on it, format it so its Label Position is None and so it uses a Line Color of No Line. We can hide the Target bars: double click on one of the gray bars, and format them with No Fill (we need the bars to retain the vertical axis labels). Finally, we don’t need the Target legend entry for the hidden bars: click once to select the legend, click again to select the unneeded legend entry, and press Delete (below left).

Now we will add custom error bars for the grip of our dumbbells. Column H has the Delta values, Actual minus Target. We’ll use these values for the custom error bar values for the negative horizontal error bars of both series. Select the Above series of dots, and on the plus icon floating beside the chart, click on Error Bars > More Options. Both vertical and horizontal error bars appear (below right).

A little chart clean-up, then adding error bars

Select the vertical error bars and press Delete. Select the horizontal error bars and press Ctrl+1 to format them. In the task pane, choose Cap for End Style, choose Custom for Error Amount, and click on Specify Value. In the dialog, type 0 (zero) for positive, and select H3:H14 for negative. Click OK, then format the error bar with a blue line color (below left).

Repeat for the Below series. Select the series, then click on the plus icon > Error Bars > More Options. Delete the vertical error bars, format the horizontal error bars with No Cap and Custom Error Amount, with 0 for positive and H3:H14 for negative values. Format these error bars with an orange line color (below right).

Now we need the data labels. Select the blue Above series, click on the plus icon floating beside the chart, click the chevron (>) beside Data Labels, and select More Options. In the Format Data Labels task pane, click on Value From Cells, and in the dialog select the range containing the labels (I3:I14). Uncheck the other options in the Format Data Labels task pane, and for Label Position choose Right. Make the labels bold, and color them blue to match the marker fill color (below left).

Select the orange Below series, and repeat this sequence, [+] > Data Labels > More Options > Value From Cells. In the dialog select the same data label range (I3:I14), unselect the other options, and for Label Position choose Left. Make the labels bold, and color them orange to match the marker fill color (below right).

Double-click on the horizontal axis, and apply a maximum value that is high enough to make room for the blue data labels (below left I’ve used 100k). You can also change the minimum value, since a chart with markers does not require the axis to include zero (below right I’ve used 30k). If the minimum is not zero, you should use no color as the vertical axis line color, so the vertical line at 30k looked like a gridline, not an axis. You can also remove the vertical axis labels, since the names also appear in the colored data labels.

Modified Dumbbell Plots

Once you have completed the basic construction of this chart, it is easy to modify it to change its appearance.

For example, use no markers for the Target series and tweak the marker and error bar formatting for the other two series to generate a Directed Dumbbell Plot (below left). Or hide all the markers and add arrowheads to the error bars to make an Arrow Chart (below right).

Modified dumbbell plots: Directed dumbbell plot and Arrow chart.

About This Chart

This is the second* of two posts inspired by Better than a Big Bar Chart, on the Storytelling with Data website, which starts with a regular bar chart showing Target and Actual data, progresses through a thermometer chart, and finally shows a dumbbell chart. The two posts on the Peltier Tech Blog apply those lessons, with more detailed instructions showing how to implement them in Excel.

*The first of these posts is Target vs Actual Thermometer Chart, which plots the same data like this:

Combination Charts on the Peltier Tech Blog

This dumbbell chart is a combination chart because it uses bars to provide vertical spacing and XY points to show values. Here is a partial list of other combination chart articles on the Peltier Tech Blog.

Dot Plots on the Peltier Tech Blog

Here are a few more articles about making Dot Plots in Excel.

Posted: Monday, April 1st, 2024 under Chart Types.
Tags: Combination Charts, Dot Plots, Dumbbell Plots, Target vs Actual.
Comments: none

Target vs Actual Thermometer Chart

Monday, January 29, 2024 by Jon Peltier Leave a Comment

Monday, January 29, 2024 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Many of the charts we create compare actual performance to a predefined target. This tutorial shows how to create a “thermometer” chart that compares actual and target levels of performance, and clearly highlights which performers surpassed their targets and which fell short.

An old-style thermometer is comprised of a glass cylinder containing a fluid that thermally expands and contracts to indicate the temperature on a scale etched on the glass container. We can use a column or bar chart to mimic the behavior of one of these thermometers: our target series serves as the container, while the actual series serves as the expandable fluid contained within.

In this chart we can see a list of sales personnel, their target sales, and their actual sales. The actual sales and the personnel names are color coded with blue if the target was exceeded, and orange if the target was not met.

Simple Bar Chart

The data for a simple target-vs-actual bar chart consists of three columns: name, target, and actual. Select the data and insert a clustered bar chart.

Some people would stop right there, but the chart isn’t quite right. Notice that the vertical axis displays the names upside-down compared with the list in the worksheet. This is such a common issue that I’ve written about it twice: Excel Plotted My Bar Chart Upside-Down and Why Are My Excel Bar Chart Categories Backwards?.

It’s easy enough to fix. Double-click on the vertical axis, select Categories in Reverse Order and Horizontal Axis Crosses at Maximum Category. All fixed.

Some people would stop there, but I don’t think we’re done yet. All the data is there, and the chart is sorted the way we expect, but I think the bars of alternating colors are difficult to compare. Let’s turn it into a variation sometimes called a “thermometer” chart after its resemblance to an old-style thermometer, comprised of a glass container containing a fluid that thermally expands and contracts to indicate the temperature on a scale etched on the glass container.

Thermometer Chart

In our thermometer chart, the Target data will be plotted as the container and the Actual data as the changing fluid. Start with the bar chart above. Double-click the Target series to format it. Format it with a thin black border and no fill; I’ve also changed Actual to a blue fill (below left).

Double-click either series, and in Series Options enter a series overlap of 100%, so the fluid is aligned with the container, and set gap width to 50% (below right).

Almost there. Unfortunately, the fluid (Actual) is not contained within the container (Target). Target is the first series, so it’s drawn under the Actual series: we must change the series order.

Changing the Bar Chart to a Thermometer Chart

You can right click on the chart, click on Select Data, and change the series plot order in the dialog. But I find it easier to select one of the series and edit the series formula, switching the last argument (plot order) of the formula between 1 and 2. So change the Target series’ formula from this:

    =SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$14,Sheet1!$C$3:$C$14,1)

to this:

    =SERIES(Sheet1!$C$2,Sheet1!$B$3:$B$14,Sheet1!$C$3:$C$14,2)

That’s not too bad, better than the default bar chart, and a lot of people would think they did a great job and stop there. But it’s possible to make the chart even better, without too many additional steps. What can we change? We can color code the thermometer so that the Actual series fluid changes from orange below the Target to blue above. We can also add data labels so we can read the Actual data. And we can color code the names along the vertical axis.

Advanced Bar (Thermometer) Chart

To add these enhancements to our simple thermometer chart, we need more columns in our data range. In addition to the three columns used above (Name, Target, and Actual), we need Above and Below columns for the color-coded bars, and X Above, X Below, and index columns for the color-coded names on the vertical axis. You can see the formulas used below the data range.

Select B2:C14, then hold Ctrl while selecting E2:F14, and insert a clustered bar chart (below left). I’ve already filled the Target series black, the Below series orange, and the Actual series blue.

The axis labels are in the wrong order, so double-click on the vertical axis, select Categories in Reverse Order and Horizontal Axis Crosses at Maximum Category (below right).

Insert the bar chart and reverse the order of the axis labels

Format with Target series with a thin black border and no fill (below left).

Apply a series overlap of 100% to any of the series. I’ve also changed gap width to 50% (below right).

Change the plot order of the Target series from 1 to 3 so the black border is visible around the colored bars (below left).

Apply data labels to the Above and Below series, using the default Values setting (below right). For the Below series, use the Inside End position, and use a white text color. For Above, use the Outside End position, and use a blue color to match the bars. Make both sets of labels bold. Make the vertical axis labels bold too (spoiler alert) to ensure the color-coded labels we add later will have enough room in the margin. Change the horizontal axis maximum if needed to allow room for the labels.

We can’t color-code axis labels individually. To add our color-coded labels, we need to add data points along the vertical axis, add data labels to these points, then hide the data points and the regular axis labels.

Add the first axis labels series. Select G2:G14, hold Ctrl while selecting I2:I14m and copy (Ctrl+C). Select the chart and use Paste Special (Home > Paste > Paste Special or right-click on chart > Paste Special). Use the New Series, Values in Columns, Series Names in First Row, and Categories in First Column options. The new series doesn’t appear in the chart itself, because it’s added as a bar chart series and has values of zero, but “index” appears in the legend (below left).

Right-click on any series in the chart and select Change Series Chart Type. In the list of series, find index, and change its type to XY Scatter. I’ve formatted the marker with a black border and blue fill for illustration purposes here (below right).

This tutorial shows how to make a thermometer chart. Our Target series is the container while the Actual series is the expanding fluid within that shows its level compared to the target. This article adds enhancements that label and color-code actual values above and below the target.

Add the second axis labels series. Copy H2:I14. Select the chart and use Paste Special: New Series, Values in Columns, Series Names in First Row, and Categories in First Column. This new series appears as an XY Scatter type, and I’ve formatted it with a black border and orange fill (below left).

There are now two “index” entries in the legend. Click once on the legend to select it, then click once on one of these unwanted entries and press Delete. Repeat for the other unwanted legend entry (below right).

Add the second axis label series and clean up the legend

Now we want to hide the vertical axis labels. If we format the axis and set Label Position to None, the labels vanish, but the space between the axis and the edge of the chart shrinks, leaving no room for the labels we will add. If we change the label font color to white, the labels seem to vanish, but they will haunt us because Excel draws the axis labels in front of any data labels they may obscure. The trick is to make the text transparent: double-click on the axis > Text Options > Text Fill > Transparency > 100%. This preserves the margin we will need for our labels (below left).

Select one of the series of dots along the axis, click the plus icon floating beside the chart, choose Data Labels > More Options. Under Label Options, choose Value From Cells, and in the little dialog, select B3:B14. Choose the Left position and uncheck Y Value (below right).

Hide the built-in axis labels, and add labels to the first axis label series

Add labels to the other set of XY scatter points, using Value From Cells and the same label range. Make all labels bold and use a font color to match the bar color of each series (below left).

Right-click one of the XY series, and format it to use No Markers. Repeat for the other XY series. The chart is finished (below right).

Finish with the labels and hide the dummy XY points

About This Chart

This the first of two posts inspired by Better than a Big Bar Chart, on the Storytelling with Data website, which starts with a regular bar chart showing Target and Actual data, progresses through a thermometer chart, and finally shows a dumbbell chart. The two posts on the Peltier Tech Blog apply those lessons, with more detailed instructions showing how to implement them in Excel.

Combination Charts on the Peltier Tech Blog

This thermometer chart is a combination chart because it uses bars to show values and XY points to anchor data labels. Here is an incomplete list of other combination chart articles on the Peltier Tech Blog.

Posted: Monday, January 29th, 2024 under Chart Types.
Tags: Bar Charts, Target vs Actual, Thermometer Chart.
Comments: none