How to Create and Customise a Dumbbell Chart in Microsoft Excel for Comparative Benchmarking

When presenting comparative performance data—such as year-over-year revenue growth, pre-training versus post-training evaluation scores, or regional market share shifts—traditional clustered column and bar charts can quickly clutter executive dashboards. When multiple pairs of bars compete for canvas space, discerning the direction and magnitude of change across dozens of categories becomes mentally exhausting. A far cleaner and more sophisticated visual alternative is the dumbbell chart (also commonly called a barbell plot or connected dot plot).

A dumbbell chart plots two distinct data points for each category along a horizontal scale, connecting them with a clean, unobtrusive connector bar. This layout directs the reader’s eye immediately to two key insights: the starting baseline (Start dot), the final achievement (End dot), and the spread (the length of the bar connecting them). While Microsoft Excel does not have a dedicated “Dumbbell Chart” template on its ribbon, you can easily build robust, fully dynamic dumbbell charts using Excel’s built-in stacked bar or scatter plot features. In this comprehensive guide, we examine how to structure your comparative data, construct the chart, format contrasting endpoints, and sort categories for boardroom-ready presentations.

Anatomy of an Executive Dumbbell Chart

An effective dumbbell chart relies on three coordinated visual layers:

Visual Component	Chart Technique	Analytical Function
Baseline Marker (Start Dot)	First Data Series / Scatter Point	Marks the initial benchmark, previous year metric, or pre-treatment baseline (e.g., 2024 Revenue).
Target Marker (End Dot)	Second Data Series / Scatter Point	Marks the current performance, target metric, or post-treatment outcome (e.g., 2025 Revenue).
Connecting Bar (Dumbbell Bar)	Stacked Bar / Error Bar / High-Low Line	Illustrates the exact margin of growth, variance, or reduction between both markers.

Step 1: Structuring the Comparative Dataset

The foundation of a reliable dumbbell chart in Excel is setting up your data table with explicit baseline and differential columns. In your worksheet, organize your data table (cells A1:D6) as follows:

Category (A)	Start / Baseline (B)	End / Target (C)	Variance / Spread (D)
Customer Support	62	85	=C2-B2 (23)
Engineering	70	92	=C3-B3 (22)
Marketing	54	74	=C4-B4 (20)
Product Design	75	88	=C5-B5 (13)
Sales Operations	58	67	=C6-B6 (9)

Pro Tip: Sorting your rows by the Variance / Spread column in descending order before generating the chart creates an impactful visual narrative, allowing stakeholders to instantly identify the highest and lowest growth departments.

Step 2: Inserting the Base Stacked Bar Chart

We will construct the dumbbell chart using Excel’s 2-D Stacked Bar chart architecture:

1. Highlight columns A, B, and D (Category, Start, and Variance). Do not highlight Column C directly, as the stacked bar will automatically represent Column C by adding B and D together.
2. Navigate to the Insert tab on the Excel ribbon.
3. In the Charts group, click Insert Column or Bar Chart and select 2-D Stacked Bar.
4. Excel will render horizontal bars where the Start series forms the initial base and the Variance series stacks directly onto the end of it.

Step 3: Creating the Connecting Bar and Transparent Spacer

Now, transform the solid bars into an elegant thin connector line:

1. Right-click the Start series bar (the leftmost segment) and choose Format Data Series….
2. In the task pane on the right, navigate to the Fill & Line tab (the paint bucket icon).
3. Under Fill, select No fill. Under Border, select No line. The initial bar segment is now completely transparent, acting as an invisible spacer that positions the connector bar at the correct baseline point.
4. Click on the remaining Variance bar segment to select it.
5. Under Fill, choose Solid fill and pick a muted, neutral grey (e.g., Light Slate Grey).
6. Switch to the Series Options tab (the bar chart icon) and increase the Gap Width to 400% or 500%. This narrows the vertical height of the bar into a sleek horizontal connecting line.

Step 4: Adding the Dumbbell End Markers (Dots)

To render the circular dumbbell heads at each end of the connector bar, we add Scatter Plot markers or custom error bar caps:

Method: Using Custom Series Markers

1. Right-click the chart canvas and click Select Data….
2. Click Add to introduce a new series. Name it Start Dot, select cell range B2:B6 for Series values, and click OK.
3. Click Add again, name it End Dot, select cell range C2:C6 for Series values, and click OK.
4. Right-click the chart and choose Change Chart Type….
5. In the Combo chart menu:
   • Keep Start (Spacer) and Variance as Stacked Bar on the Primary Axis.
   • Change Start Dot and End Dot to Scatter (or Line with Markers) on the Secondary Axis.
6. Click OK. Excel will place markers precisely at the beginning and end of each connecting bar.

Step 5: Formatting and Colour-Coding the Dots

To make the chart immediately readable without requiring an extensive legend:

• Start Marker (Baseline): Click the Start Dot series markers, open the Fill & Line pane, expand Marker Options, select Built-in Circle, and increase size to 10 to 12 pt. Set the fill to a subtle slate grey or cool navy.
• End Marker (Achievement): Select the End Dot series markers, choose a Built-in Circle sized at 10 to 12 pt, and assign a vibrant accent colour (such as emerald green for positive growth or coral red for deficit benchmarks).
• Synchronise Vertical Axes: Ensure the secondary vertical axis categories mirror the primary vertical axis, then delete the secondary axis labels to maintain a clean layout.

Step 6: Adding Direct Data Labels

Rather than forcing stakeholders to look down at the horizontal axis numbers, add direct value labels to the markers:

1. Right-click the End Dot series and select Add Data Labels.
2. Click the newly added labels, open the Label Options task pane, and set the position to Right.
3. Right-click the Start Dot series, select Add Data Labels, and position them to the Left.
4. Format the label typography: bold the end achievement label and format the start label with muted grey text.

Dashboard Best Practices for Dumbbell Charts

To maximise clarity across financial, marketing, and operational dashboards:

• Remove Background Clutter: Delete vertical gridlines and chart background borders. The horizontal connector bars already provide ample horizontal eye guidance.
• Use Dynamic Header Text: Insert a dynamic chart title (e.g., “Departmental KPI Growth: 2024 vs 2025 Target”) with a small subtitle indicating: “Grey dot = 2024 Baseline | Green dot = 2025 Target”. This eliminates the need for an awkward floating legend box.
• Emphasize Negative Variance: If certain categories experience a regression (where End value is lower than Start value), format those specific connector bars and markers with a distinct warning colour (such as crimson) to draw immediate executive attention to underperforming segments.