Learn how to create an Excel dumbbell chart (dot plot) to emphasize the change between two points across multiple categories.
The main point of using a dumbbell chart (dot plot) in Excel is that it is easier to see the distance of a line than the space between the length of two bar charts. A dot plot is a great choice for telling some data stories. It looks distinctive and grabs a reader’s attention.
Dumbbell charts are not built-in native chart types in Excel, so creating them takes a bit of modifying a scatterplot. This tutorial will show you how to create a dot plot in Excel. If you want to create custom visualization using various chart templates, read our definitive guide.
How to Create a Dumbbell chart (Dot plot) in Excel
1. Format your Data
The first step is to format your data. In the example, our initial data set contains three columns. The first column has categories; the other two contain numeric values.
2. Add helper columns
Append your data set. The dumbbell chart required two columns of additional data. Dot spacing values should be equally distant from one another. We will use these values as the y-axis. To display the distance between two data points, use the values in the Length range.
3. Create the basic layout for the dumbbell chart
Select your first data series. Highlight the second and fourth columns. Use the Ctrl key to select multiple non-adjacent cells.
We use a scatter plot to create the layout of the dumbbell chart. The basic layout looks like the graph below:
4. Add the second series to the dot plot
Currently, the chart shows only the starting points. The next step is to add the seconds series. Right-click on the chart, then click the “Select Data” option.
Under the Legend Entry series, click Add:
Series 2 values your next data series. In the example, we use the column 3 data. X values are in column 3, then use the Dot space column as Y values.
5. Format markers
To increase the size of the points (markers), use the “Format Data Series” command.
6. Clean up the dumbbell chart
Left-click to highlight, then delete the major gridlines and the Y-axis (dot plot placeholders). Right-click the X-axis, select “Format Axis”. Change the “Major tick mark” to none.
7. Add the lines connecting two data points
We will use Error Bars to create the connection between the two series. Select the Series 1 point. From the Chart Elements context menu, select “Error Bars”.
8. Delete the vertical lines
To format the dumbbell chart, delete the vertical part of the error bars. Select the data point, then delete the unnecessary part. After the cleanup, the dot plot looks like below:
9. Formatting Error Bars
The next step is to format your horizontal standard error bars to connect your two dots.
Select the “Width” column for your “Positive Error Value”. Your dumbbell chart looks like this now: two dots connected by a line.
10. Add data labels and change colors
To add data labels as text boxes, insert a new textbox and link the category data. Finally, use small formatting tricks to ensure your key data story stands out.
Good to know that you can download the practice file from here.
Bottom line: Advantages of using a Dumbbell Chart
Just a few words about why you should use a dumbbell chart (dot plots):
- The chart is simple, but you can create attention-grabbing visualizations in Excel.
- Dot plots provide better readability than the classic stacked column charts.
- You can create and manage the chart using a small space perfect for dashboards.
- Visualizing the difference between the plan and the actual values is easy.
Additional resources:
