How to make a Dumbbell Dot Plot in Excel (100% dynamic)

Dumbbell dot plots are an excellent chart style for presenting comparative data. These chart styles quickly show the difference or progress between two data points.

Often, Excel tutorials for dumbbell dot plots create charts that require manual updating for new data. But using the right Excel techniques these charts can be fully dynamic.

So, in this post, we will create a dumbbell dot plot in Excel that updates automatically when we add new data.

The final chart looks like this:

Final Chart
Table of Contents

Download the example file: Click the button below to join the Insiders program and gain access to the example file used for this post.

File name: 0192 Dynamic dumbbell dot

Watch the video

How to make a Dumbbell Dot Plot in Excel (100% dynamic) | Excel Off The Grid

Watch the video on YouTube


The source data for the example is a Table called Data.

Dumbbell dot plot data

We are building our dumbbell dot plot so that when we add new regions to the data, everything updates automatically.


We are using dynamic array calculations for this solution. These are available in Excel 2021 and Excel 365.

Let’s start by creating the formulas we require.

Calculations for Dumbbell dot plots

The values in cells E3-K3 are static values used for column headings.

The remaining values are all formulas. Each formula is detailed below.


The formula in cell E4 is:


This provides a unique list of regions from the Data table. The results spill into the cells below.

Comparison (2022)

The formula in cell F4 is:


This calculates the sum of the Value column where the Region is equal to those listed in the E4# spill range, and where the year is equal to cell F3 (value 2022).

Value (2023)

The formula in cell G4 is:

=SUMIFS(Data[Value],Data[Region],E4#,Data[Year], G3)

This is similar to the calculation in F4, but returns the values where the year matches cell G3 (2023).

Positive & Negative

To create the line between the two dots, we use a chart feature called Error Bars.

For these to display correctly, we need two numbers:

  • The absolute positive movement between 2022 and 2023.
  • The absolute negative movement between 2022 and 2022

To calculate the positive movement, the formula in cell H4 is:


The NA() function is used in this formula so that where the movement is negative the #N/A error returns.

#N/A is a special value in Excel because it is not rendered on a chart.

To calculate the negative movement, the formula in cell I4 is:


This formula also uses the NA() function to return the #N/A error where the movement is positive.

Label Position

The Label Position provides a location to anchor the region label. Ultimately this is a hack to get the label to appear in the right place.

Our label will be to the left of the leftmost dot. As there are positive and negative movements, we don’t know if 2022 or 2023 will be the leftmost dot. So, we use the Label Position to calculate that for us.

The formula in cell J4 is:



As we are using an XY Scatter plot we need to provide Y values to place the dots in the correct order. The Position formula provides the position of the dot along the Y-axis.

The formula in cell K4 is:


This formula creates a count for the number of rows. In our example, there are 4 rows, so the results are numbers from 4 to 1.

Named Ranges

To enable the chart to be dynamic, we must create a named range for the spill range of each calculation in the section above.

Click Formulas > Define Name

Formulas - Define name

In the New Name box, enter the following:

Create new name for the spill range
  • Name: Label
  • Scope: Example (the name of the worksheet)
  • Refers to: =Example!E4#
  • Click OK


If your worksheet has a space in the name, you will need to surround the sheet name with single quote (e.g. =’Example Sheet’!E4#)

That is how to create one named range. We need to repeat this process for each column in the calculations section.

Once finished, there should be 7 in total.

  • Label: =Example!E4#
  • Comparison: =Example!F4#
  • Value: =Example!G4#
  • Positive: =Example!H4#
  • Negative: =Example!I4#
  • Label Position: =Example!J4#
  • Position: =Example!K4#

Create the chart

Now it’s time to build the chart.

Add data to the dumbbell dot plot chart

Select the 2022 (comparative) column, then click Insert > Scatter (drop-down) > Scatter

Create scatter chart

A chart will appear.

Right-click on the chart, click Select Data…

In the Select Data Source dialog box, select Series1, then click the Edit button.

In the Edit Series dialog box, enter the following named ranges.

  • Series name: =Example!$F$3
  • Series X values: =Example!Comparison
  • Series Y values: =Example!Position
  • Click OK
Chart series data

REMEMBER: Use single quotes around the worksheet name if it includes a space.

Click the Add button, to add another data series.

  • Series name: =Example!$G$3
  • Series X values: =Example!Value
  • Series Y values: =Example!Position
  • Click OK

Click OK to close the Select Data Source dialog box.

The chart looks like this:

Chart - two dots added

Chart error bars

Select the dots for the 2022 (comparison) series. Click on the [+] symbol next to the chart and add Error Bars with More Options…

Error Bars in Chart - More Options

Error bars are added to the chart.

Chart with error bars

We don’t need the vertical error bars. Select the vertical error bars and press the Delete key.

Select the horizontal error bars. Apply the following settings:

  • Direction: Both
  • End Style: No Cap
  • Error Amount: Custom > Specify Value
    • Positive Error Value: =Example!Positive
    • Negative Error Value: =Example!Negative
    • Click OK
Error bar settings for chart

This adds a line connecting the two dots.

Format the dots

The dots on the chart are known as markers. Click on the 2022 (comparison) dots.

Click on the Paint can icon, then select Markers. Apply the following settings:

  • Marker Options: Built-in
    • Type: Circle
    • Size: 14
  • Fill: Solid fill – select a color. As this is the comparison, I’ve chosen light grey.
  • Border: No line
Format the markers

Follow the same steps to format the 2023 (Value) dots. Choose a more vibrant color to emphasize the current value.

After formatting the 2022 and 2023 dots, the chart should look like this:

Chart - formatted dots

Data labels

Select the 2022 (comparison dots). Click the [+] next to the chart and add Data Labels to the Center.

Add data labels to chart

Select the data label, then change it to display the X Value only.

Chart label as X axis

Repeat the steps above for the 2023 (value dots).

Format the data labels to match the style of your chart.

The chart now looks as follows:

Chart after data labels

Category labels

We don’t currently know which dots relate to which category, so let’s add the labels.

In the same way, as we added the 2022 and 2023 dots, add the Label Position dots.

The data series should be set up as follows:

  • Series name: =Example!$J$3
  • Series X values: =Example!LabelPosition
  • Series Y values: =Example!Position
  • Click OK
Add Label Position to chart

The dots will appear over the existing value or comparison dots.

It may be difficult to select the LabelPosition series. So, from the Ribbon, click Format, then select Series “Label Position” from the Current Selection drop-down (on the left-hand side of the Ribbon).

Click the [+] icon on the chart and add Data Labels to the Left.

Select the data labels. Uncheck the Y Values. Check Values From Cells.

In the Data Label Range dialog box enter the following:

  • Select Data Label Range: =Example!Label
  • Click OK
Format Data Labels

Format the label as required.

For the Label Position dot, change the Marker Option to None.

The chart now looks like this:

Chart Progress after all labels

Delete the chart junk

We now have everything we need. Delete the following:

  • Horizontal major gridlines
  • Vertical major gridlines
  • X axis
  • Y axis

The final chart looks as follows:

Final Chart

Add new data

OK, it’s time to prove this is fully dynamic.

Add new data to the Data table.

New data added

Ta Dah! Everything updates magically!!!

Conclusion – Dumbbell dot plots

In this post, we have seen how to create dumbbell dot plots in Excel.

By using dynamic array calculations and named ranges the chart is fully dynamic and expands when we add new data.

Related Posts:

Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

Leave a Comment