Skip to main content

Create a Dumbbell Chart in Excel

Author: Siluvia Last Modified: 2020-10-29

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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description

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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations