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.
Select a blank cell which is adjacent to the original data table, in this case, I select D2, enter the below formula into it and press the Enter key.
=1/4/2
Select cell D3, enter the below formula into it and press the Enter key. Select cell D3, and then drag it’s Fill Handle all the way down to get other results.
=D2+1/4
Tips: In the formulas, the number 4 represents the number of rows (exclude the header row) in your original data table. If there are 8 rows (exclude header row) in your data, please change the number 4 to 8.

Please create the second helper column as follows.
Select a blank cell (here I select E2), enter the below formula into it and press the Enter key. Select the result cell, drag its Fill Handle all the way down to get other results.
=IF(B2>C2,B2-C2,)

Create the third helper column as follows.
Select a blank cell such as F2, enter the below formula into it and press the Enter key. Select the result cell, drag its Fill Handle all the way down to get other results.
 =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.

11.1) In the Series name box, select the header of the first series values (In this case I select B1);
11.2) In the Series X values box, select the first series values (here I select B2:B5);
11.3) In the Series Y values box, select the first helper column data (D2:D5);
11.4) Click OK.

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.

13.1) In the Series name box, select the header of the second series values (In this case I select C1);
13.2) In the Series X values box, Select the second series values (here I select C2:C5);
13.3) In the Series Y values box, select the first helper column data (D2:D5);
13.4) Click OK.

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.

21.1) Select Minus in the Direction section;
21.2) Select No Cap in the End Style section;
21.3) Select Custom in the Error Amount section;
21.4) Click the Specify Value button. See screenshot:

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.

24.1) Select Minus in the Direction section;
24.2) Select No Cap in the End Style section;
24.3) Select Custom in the Error Amount section;
24.4) Click the Specify Value button. See screenshot:

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.

3.1) In the Format Data Labels pane, check the Value From Cells box in the Label Options section;
3.2) In the opening Data Label Range dialog box, select the actual values of the selected series and then click OK;
3.3) Uncheck the Y Value box;
3.4) Uncheck the Show Leader Lines box. See screenshot:

4. For another series, please repeat the above step 1 to 3 to add the corresponding data labels.

After adding all data labels, the dumbbell chart is shown as the below.

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

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.