Create a Dumbbell Chart in Excel
Dumbbell chart also called DNA chart, which can help you to compare two points in a series that are on the same axis. For creating a dumbbell chart in a worksheet, we provide a step-by-step guide in this tutorial to help you get it down with ease.
Create a dumbbell chart in Excel
Easily create a dumbbell chart with an amazing tool
Download the sample file
Video: Create dumbbell chart in Excel
Create a dumbbell chart in Excel
Supposing you want to create a dumbbell chart based on data as the below screenshot shown, please do as follows.
1. Firstly, you need to create three helper columns.
Please create the first helper column as follows.
Please create the second helper column as follows.
Create the third helper column as follows.
=IF(B2<C2,ABS(B2-C2),)
2. Select the axis column range and the first data series (in this case, I select range A2:B5), and then click Insert > Insert Column or Bar Chart > Clustered Bar.
Then a clustered bar chart is inserted in current sheet.
3. Right click on any one of the series bar, and then click Select Data from the right-clicking menu.
4. In the Select Data Source dialog box, please click the Add button in the Legend Entries (Series) section, and then click the OK button directly in the next popping up Edit Series box.
Note: The Series2 will be created in this step.
5. Repeat the step 4 to create the series3.
6. When it returns to the Select Data Source dialog box, you can see the Series2 and Series3 are listed in the Legend Entries (Series) box, click OK to save the changes.
7. Right click on any series in the chart, and then click Change Series Chart Type from the context menu.
8. In the Change Chart Type dialog box, change the chart type of Series2 and Series3 to Scatter, and then click OK to save the changes.
9. Right click on any one of the series, click Select Data from the right-clicking menu.
10. In the Select Data Source dialog box, click to select Series2 in the Legend Entries box, and then click the Edit button.
11. In the opening Edit Series box, you need to configure as follows.
12. When it returns to the Select Data Source dialog box, select Series3 and then click the Edit button.
13. In the opening Edit Series box, you need to configure as follows.
14. Click OK in the Select Data Source dialog box to save the changes.
Now the chart is displayed as below screenshot shown.
15. Right click the vertical axis in the chart, and then select Format Axis from the context menu.
16. In the opening Format Axis pane, check the Categories in reverse order box under the Axis options tab.
17. Click on the secondary axis in the chart, and then check the Categories in reverse order box in the Format Axis pane.
18. Now you need to hide the blue bars in the chart. Click on any one of the bars, go to the Format Data Series pane, click the Fill & Line icon, and then select No fill and No line separately in the Fill and Line sections.
19. Select the expense series (the orange dots) in the chart, click Design > Add Chart Element > Error Bars > Standard Error.
Now the chart is displayed as the below screenshot shown.
20. Select the vertical error bars, press the Delete key to remove them from the chart.
21. Select the horizontal error bars, go to the Format Error Bars pane and then configure as follows.
22. In the popping up Custom Error Bars dialog box, select the second helper column data in the Negative Error Value box, and then click OK.
23. Select the revenue series (the blue dots) in the chart, click Design > Add Chart Element > Error Bars > Standard Error.
Now error bars are added to the revenue series. See screenshot:
24. Remove the vertical error bars from the chart, select the horizontal error bars, and then go to the Format Error Bars pane to configure as follows.
25. In the opening Custom Error Bars dialog box, select the third helper column data in the Negative Error Value box, and then click OK.
Now the dumbbell chart is shown as below.
26. You can adjust the other elements of the chart, such as remove the secondary axis, add legend, reorder the position of the horizontal axis, add data labels for the series, and so on.
Remove the secondary axis
Just click to select the secondary axis in the chart, and then press the Delete key to remove it.
Add legend to the chart
Select the chart, click Design > Add Chart Element > Legend > Bottom (or other position as you need).
Then the legend is displayed on the chart as the below screenshot shown. You can remove the needless series name from the legend by selecting it and press the Delete key. (here I will remove the Series1 from the legend field).
Reorder the position of the horizontal axis
As you can see, the horizontal axis is locating on the top of the series. See screenshot:
If you want to locate it on the bottom of the series, please right click on it and select Format Axis from the context menu. In the Format Axis pane, expand the Labels section, and then select High from the Label Position drop-down.
Now the horizontal axis has been moved to the bottom of the series as the below screenshot shown.
Add data labels to the series
1. Select a series (the orange dots in this case) in the chart, click Design > Add Chart Element > Data Labels > Left (any position as you need).
2. However, the values of the first helper column are displayed as the data labels, to replace them with the actual series values, please right click on any one of the data labels, and then click Format Data Labels from the right-clicking menu.
3. Go ahead to configure as follows.
4. For another series, please repeat the above step 1 to 3 to add the corresponding data labels.
Tips: You can also remove or change the chart title, specify new colors for the lines and markers as you need.
Now the dumbbell chart is complete.
Easily create a dumbbell chart in Excel
The Dumbbell Chart utility of Kutools for Excel can help you quickly create a dumbbell chart in Excel with several clicks only as the below demo shown.
Download and try it now! 30-day free trail
Download the sample file
Video: Create dumbbell chart in Excel
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.