Locked lesson.
About this lesson
Create a Dumbbell Chart to show the difference between two data points.
Exercise files
Download this lesson’s related exercise files.
4.14 dumbbell-charts - Exercise.docx94.3 KB 4.14 dumbbell-charts - Exercise solution.docx
5.8 MB Exercise - Dumbbell Charts.xlsx
11.1 KB Exercise - Dumbbell Charts - Solution.xlsx
15.8 KB Workbook - Dumbbell Charts.xlsx
11.8 KB Workbook - Dumbbell Charts - Complete.xlsx
18.1 KB
Quick reference
Dumbell Charts
Create and format a Dumbell Chart.
When to use
We use a Dumbell Chart when we want a visualization that highlights the difference between two data points using a pair of circles and a connected line.
Instructions
A dumbbell chart is a type of data visualization that highlights the changes between two data points. It effectively displays the difference or range between two values across various categories. This chart consists of pairs of circles (dots) connected by a line, resembling a dumbbell, which makes it easy to see variations and trends. This chart is also referred to as a connected dot plot chart.
There is no specific Dumbell Chart in Excel. This chart is built using a customized scatter plot chart.
Key Features of a Dumbbell Chart:
- Dual Points: Each category has two points representing different data values.
- Connecting Line: A line connects the two points, showing the relationship and difference between them.
- Comparison: Excellent for comparing two sets of data across multiple categories.
- Clear Visualization: Makes it easy to see which categories have the greatest or least difference between the two points.
In this example, we are using a Dumbell Chart to show employee satisfaction in 2023 vs 2024.
Why Dumbell Charts Are Useful:
- Change Visualization: Clearly shows the change between two points, highlighting increases, decreases, or stability.
- Trend Identification: Helps identify trends and patterns across different categories.
- Comparison: Facilitates comparison between two related data points, such as before and after, two different time periods, or two different groups.
- Simplification: Simplifies complex data into an easy-to-understand format, making it accessible for various audiences.
Create a Dumbell Chart
Insert a Scatter Plot
To create a Dumbell Chart we need to start by creating a blank Scatter Plot chart.
- Click anywhere in the spreadsheet outside of the data table.
- Go to the Insert tab on the Ribbon.
- In the Charts group, click on Insert Scatter (X, Y) or Bubble Chart.
- Choose Scatter with Straight Lines.
A Scatter Plot Chart requires values for the X-Axis and values for the Y-Axis. These values tell Excel where to plot the dot.
The percentage values in the table will be used to plot the X-Axis but we need values for the Y-Axis values.
- In the y-axis column, type the numbers 1 to 7.
We can now add the x and y-axis series manually to the chart.
Add Data Series
- Right-click on the chart and select Select Data.
- Click Add to add a new series.
- In Series name, click the cell C7.
- In Series X Values, select the cell range for the 2023 values.
- In Series Y Values, select the cell range for the y-axis column.
- Click OK.
Add the Second Data Series
- Right-click on the chart and select Select Data.
- Click Add to add a new series.
- In Series name, click the cell D7.
- In Series X Values, select the cell range for the 2024 values.
- In Series Y Values, select the cell range for the y-axis column.
- Click OK.
Connect the Dots
Now we have the dots in the correct positions we need to connect them. We do this using error bars. However, we need to tell Excel how long each bar needs to be and we can do this using a formula.
- Click in cell F3 in the Difference column.
- Type =D8-C8 and press Enter.
- Click on an orange dot.
- Click the plus next to the chart and check Error Bars.
- Select the vertical error bars and press Delete.
- Click on any of the horizontal error bars and press CTRL+1.
- Under Direction, select Minus.
- Under End Style, select Cap.
- Under Error Amount, select Custom and click Specify Values.
- In the Negative Error Value field, select the values in the Difference column.
- Click OK.
Notice that the y-axis is showing numbers instead of Item Labels.
Change the Y-Axis Labels
- Drag chart area over a little to make space for the new labels.
- In the Labels column, add 0 into each cell.
- Click on the chart.
- Right-click and choose Select Data.
- Click Add.
- In the Series name field, click cell G7.
- In the Series X values field, select the cell range in the Labels column.
- In the Series Y values, select the cell range in the y-axis column.
- Click OK.
- Click on the green dots.
- Right-click and select Add Data Labels.
- Click on the new set of numbers.
- From the Format Data Labels pane, in the Label Position area, click Left.
- From Label Options, select Value from Cells.
- In Select Data Label Range, select the values in the Items column.
- Click OK.
- Click on the data labels.
- From Label Options, deselect Y-Values.
- Select the original set of numbers and press Delete.
- Click on the green dots and change the Shape Fill and Shape Outline to None.
We can then apply formatting as required. I have changed the color of the dots and also modified the maximum bound of the horizontal axis to 100%.
Example Use Cases for Dumbbell Charts:
- Before and After Analysis: Compare performance metrics before and after implementing a new strategy.
- Yearly Comparisons: Show differences in sales, revenue, or other key metrics between two years.
- Group Comparisons: Compare survey results between two different groups.
- Progress Tracking: Track the progress of a project against two milestones or time points.
Sorry, we don’t have a transcript for this lesson yet.
Lesson notes are only available for subscribers.