Views > Excel charts > How to create a Dumbbell Chart in Excel

In a previous blog post I showed you how to create the following Dot Plot. So I’m going to start off from where we left it and transform the Dot Plot into a Dumbbell Chart.

1. Right-click over Column C on the worksheet and then, on the shortcut menu, click Insert.
2. Insert the data for 2007 results. The data source should look like this:

3. Right-click the chart and then, on the shortcut menu, click Select Data.
4. In the Select Data Source dialog box, add a new data series by clicking the Add button under Legend Entries (Series).
5. In the Edit Series dialog box, refer to cell C2 in the Series name edit box. Next, edit the Series X values to include cells C3:C55 and the Series Y values to include cells E3:E55 as follows:

6. Click on the Ok button twice to close the Edit Series and the Select Data Source dialog boxes. The chart should look like this:

HOW TO CREATE A DUMBBELL CHART IN EXCEL

7. Select the newly added series 2007, change the size of the marker, its fill and border color. Change the title and the chart should look like this:

8. I’m going to use Error Bars to display the bars between the dots. To do that, add the following formula into cell F3 to calculate the Positive Error Value
 = IF((D3 - C3) >  0,(D3 - C3),0)
9. Select cell F3 then drag the handle to copy the formula into cells F4:F55. The data source should look like this:

10. To calculate the Negative Error Value, add the following formula into cell G3
    = IF((D3 - C3) <  0,-(D3 - C3),0)
11. Select cell G3 then drag the handle to copy the formula into cells G4:G55. The data source should look like this:

12. Select the chart then click the Charts Elements button located on the upper-right corner of the chart. Next, in the expanded menu, check the Error Bars option, as per the screenshot below:

13. Select the Series "2017" Y Error Bars in the chart and then turn them off by pressing the Delete key on the keyboard.
14. Select the Series "2007" Y Error Bars in the chart and then turn them off by pressing the Delete key on the keyboard.
15. Select the Series "Fatality rate 2016" Y Error Bars in the chart and then turn them off by pressing the Delete key on the keyboard. The chart should look like this:

16. Right-click the Series "Fatality rate 2017" X Error Bars in the chart and then, on the shortcut menu, click Format Error Bars.
17. In the Format Error Bars pane, with Error Bars Option icon selected, set the End Style to No Cap.
18. Set Error Amount to Custom and click on the Specify Value button.
19. In the Custom Error Bars dialog box, edit the Positive Error Value to include cells G3:G55 and the Negative Error Value to include cells F3:F55 as follows:

20. Right-click the Series "2007" X Error Bars in the chart and then, on the shortcut menu, click Format Error Bars.
21. In the Format Error Bars pane, with Error Bars Option icon selected, set the End Style to No Cap.
22. Set Error Amount to Custom and click on the Specify Value button.
23. In the Custom Error Bars dialog box, edit the Positive Error Value to include cells F3:F55 and the Negative Error Value to include cells G3:G55 as follows:

24. Finally, reduce the size of the markers and change the boundaries of the scales. The chart should look like this:

Thanks to Excel’s versatility and flexibility, I can transform the above chart into many different types by making few formatting changes. Let's have a look.

For example by doing the following:
- Hide the labels of the primary and secondary horizontal axis
- Delete the horizontal gridlines
- Remove the outline of the plot area, the primary and secondary horizontal axis, and the vertical axis
- Change the color and line weight of the X Error Bars
- Change the size of the markers
- Add data labels to the data series and flip manually the position of some data labels to align with the dots.

The chart will look like this:

Or if you want to create a range bar graph while emphasizing the end points you can do the following:

- Change the color and line weight of the X Error Bars
- Draw a short vertical line anywhere on the worksheet using the Shapes in the Illustration Group under the insert Tab. The length of the line should match the width of the Bar (may need few trials to succeed)
- Copy the newly created shape to the Clipboard by pressing Ctrl + C
- Select the markers of the data series in the chart and paste the shape by pressing Ctrl + V

The chart will look like this: