Indzara

How to Create a Dumbbell Chart with Scatter Plot in Excel?

A Dumbbell Chart is perfect for visually representing a range of values, such as minimum and maximum, along with their spread, making it an ideal addition to reports or presentations to capture your audience’s attention instantly.

Dumbbell Chart
Dumbbell Chart

This post walks you through the steps required to create this chart using Scatter plots in Excel. Follow the steps here, and in a few minutes have this chart created.

Check our blog on creating a vertical dumbbell chart in Excel.

Ready to save time on your next presentation? Explore our Data Visualization Toolkit featuring multiple pre-designed charts, ready to use in Excel. Buy now, create charts instantly, and save time! Click here to visit the product page.

Step 01: Prepare the data

i. Select all the data, and press CTRL+1 to create a table

Dumbbell Chart create a table

ii. We need to create additional columns to create our dumbbell chart.

For this, the first column is “Space”. This is needed to include a space between each dumbbell and needs to be equidistant from each other.

This formula returns the sequential numbers equal to the count of rows in the table.

We’ll now add the “Width” column which is the difference between the two numeric values in your table.

With this, your new data would be:

Dumbbell Chart new data

Step 02: Create a scatter plot

Now, select the Min Salary and the Space columns and insert a scatter plot.

Dumbbell chart insert a scatter plot.

With this, a scatter plot with a single series gets inserted.

Scatter plot
Scatter plot single series

Now, insert a second series, the Max Salary into the chart.

For this, right-click on the chart and go to select data

Dumbbell chart Select Data

Now, insert the Max Salary series as shown:

Dumbbell chart Max Salary series

With this, the two-series chart looks as shown:

Scatter plot two series
Scatter plot two series

Step 03: Add second series to the scatter plot

Before we proceed further, let us format the scatter plot arrived from step 02.

i. Right-click on one of the series, go to Format Data Series

Dumbbell chart Format Data Series

ii. Format the marker as shown:

Dumbbell chart Format the marker

iii. Similarly format the other series as well.

With this, our chart looks something like this:

Dumbell chart after format

Step 04: Add error bars

i. Now, click on the series to the LEFT and use the “+” icon that appears on the top of the chart to insert Error Bars

Dumbbell chart Error Bars

ii. Click on the “Vertical errors” and press delete key

iii. Now, click on the chart, press CTRL+1 to open format pane again.

From here, choose the “Space” X Error Bars

Dumbbell chart Space” X Error Bars

 and under the Error bar options, format this as shown here:

Dumbell chart Error bar options

Choose the custom values as the “Width” as shown here:

Dumbbell chart_width

iv. Now go to the Fill & Line option to format the line shape to get the desired dumbbell shape.

Dumbbell chart_Fill & Line

Click on the Y-axis and press delete to key to delete the same

With this, the dumbbell chart looks like this:

Dumbbell chart without labels

Step 05: Add data labels

i. Click on the chart and use the “+” to add data labels.

Dumbbell chart data labels

ii. Click on the labels to the left and use the format pane to modify the labels

From here, uncheck the Y Value and Check the X value.

Dumbbell chart XY value

Also, since we have deleted the Y-axis, we’ll use the “Value from Cells” option to include the labels to indicate the category values.

Dumbbell chart Value from Cells

Now, position these labels to the left of the dumbbell.

Dumbbell cells labels

Similarly, modify labels to the right dumbbell as well to represent the Max salary values.

For this, click on the right labels and from the label options, uncheck others and choose only the “Value from cells” option.

Dumbell value from cells

The range will be the range of the Max Salary as shown below, also position the labels to the right.

Dumbbell chart Data Label range

Note that you can click on the labels, and use the home ribbon to format them as needed.

At this stage, the chart looks like below:

Step 06: Format the chart

Now, it’s time for some standard formatting of the chart.

i. From the formatting pane drop-down, choose the “Horizontal (Value) Axis Major Gridlines” and make the line less apparent.

Dumbbell chart Horizontal (Value) Axis Major Gridlines
Dumbbell solid line

Similarly, format the “Vertical (Value) Axis Major Gridlines” as well.

ii. Choose the Chart Area from drop-down and format the chart border as shown:

Dumbbell chart_rounded corners

iii. Now, click on the chart, use the “+” from the top-right of the chart and insert suitable chart title and axis titles.

Dumbbell chart axis titles

Since we have included the category values in the labels, click on the vertical axis title and click delete.

With this, your dumbbell chart created using a scatter plot is ready!

Leave a Reply

Your email address will not be published. Required fields are marked *