• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Dot Plot Charts

You are here: Home / Excel Charts / Excel Dot Plot Charts
Excel dot plot chart
December 6, 2017 by Mynda Treacy

Dumbbells and Lollipop Charts

Excel Dot Plots, dumbbells and lollipop charts are good for comparing one, two or three points of data. For example, year on year, before or after or A vs B.

dumbbells and lollipop charts

They make a nice change from a column or bar chart (like the one below) and are less cluttered:

column or bar chart

Column and bar charts also require the horizontal axis to begin at zero. This is because we instinctively compare the length of the columns/bars and make judgements based on the difference in size. If we don’t start the bar lengths at zero we can falsely exaggerate the difference and mislead our audience.

Take the following example where the horizontal axis starts at $600M:

horizontal axis starts at

Department A’s 2017 sales target appears to be double that of 2016, but in fact it’s only 23% more. Department C is even more misleading.

This is why we must always start bar and column chart axes at zero.

However, dot plots:

dot plot example

…and dumbbell charts (below), aren’t bound by this rule because the dots aren’t connected to the vertical axis base line:

dumbbell chart example

And so, our eye isn’t drawn to make comparisons in the distance from the vertical axis. Instead we judge them based on the position along the horizontal axis.

This allows us to emphasise the difference between the dots, whether that be two points as in the dumbbell charts above, or the single points in the dot plot.

Lollipop Charts

Lollipop charts get their name from the leader line that draws your eye to the dot, and because of this I think you should start your axis at zero for the same reasons we do with bar and column charts:

lollipop chart example

Dot Plots, Dumbbells and Lollipops - Which is Best

All charts are useful, and your choice will depend on the points you want to emphasize:

Bar/Column Charts – quickly compare the size of one department to the next and compare from one period to the next within that department

Lollipop Charts – a less cluttered take on the bar chart. Make sure the axis starts at zero.

Dumbbell Charts – emphasize the change from one point to the next with some comparison between departments

Dot Plot Charts – allow comparison between departments with more emphasis on the difference

Watch the Video

Subscribe YouTube

Download the workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Building Excel Dot Plot Charts

Start with your data structured like so:

data example

Tip: The spacing simply assigns each department to a row in your chart so they’re nicely vertically distributed. You can change the spacing to suit your needs.

  1. Select the data in columns B and C > Insert tab > Scatter Chart. It should look like this:
  2. scatter chart

  3. Right-click the chart > Select Data > Edit the series name so it points to cell B71 that contains the year name:
  4. Edit Series

 
  1. Edit the vertical axis (right-click > format or left-click > Ctrl+1) > Set the maximum to 2.5 (or to match the maximum spacing value in your data set).
  2. If you want to emphasize the difference between the dots you can set the horizontal axis minimum to something closer to the lowest value in your data set. I’ve set mine to $600M.
  3. Turn off the vertical axis (just select it and press DELETE).
  4. Turn off the vertical gridlines (select them and press DELETE). Optionally also turn off horizontal gridlines.
  5. Select the dots > CTRL+1 to format > Marker > Marker Options > set the marker options, type and size (see my settings below -# 1). Set the fill to white (#2 & #3) and make the border thicker (#4 & #5):
  6. select the dots

  7. Add labels > align them left and format them to display the X value. If you have Excel 2013 or Excel 2016 you can also include the Department names using the ‘Values From Cells’ reference as shown below:
  8. Format Data Labels

Note: If you have an earlier version of Excel then you can use the technique described by Jon Peltier here to assign your department names to the vertical axis labels.

Or you can get Jon's Excel add-in that can create dot plots and more:

Peltier Tech Chart Utility

Peltier Tech Chart Utility – available for PC and Mac

 
  1. If you want to keep the horizontal gridlines, then you can fill the labels with white (select labels > Format tab) so that the line doesn’t strike through the label:
  2. keeping the horizontal gridlines

 

Excel Lollipop Charts

lollipop chart example

Lollipop charts require the same steps as the Dot Plot, but you delete the horizontal gridlines and replace them with error bars. To add error bars:

  1. Select the dots > Chart Tools > Design > Add Chart Element > Error Bars > Percentage. Then open More Error Bar Options from the same menu.
  2. Set the direction to Minus, End Style to No Cap and Error Amount is Percentage at 100%:
  3. Format Error Bars

Excel Dumbbell Charts

dumbbell chart example

Dumbbell Charts (sometimes called DNA charts), require the same steps as the Dot Plot. Then you simply add a second series:

  1. Right-click chart > Select Data > Add Legend Series
  2. Select the second set of data for the X series, in my case it’s 2016 data. The Y series are the Spacing values:
  3. spacing values

  4. Add error bars (note: the Error Bars are based on the difference between 2017 and 2016 and you can see the calculation in column E) – Select the 2016 dots in the chart > Chart Tools > Design tab > Error Bars > More Error Bar Options. This will open the Error Bar formatting dialog box or pane (shown below):
  5. error bar formatting

 
  1. Click on ‘Specify Value’ and select the Positive Error Values from the table:
  2. Positive Error Values

Tip: If you have negative error values (like the example below), then you’ll also need to add a column to your table to calculate them and then reference those cells in the ‘Negative Error Value’ field shown in the dialog box above.

negative error values

Be sure to download the Excel file to see the example above.

Camera Tool Alternative

If you’re familiar with Excel’s camera tool, then a quick and dirty way to create a dot plot is to insert a line chart with only markers and use the Camera tool to rotate it on it’s side.

However, often the image in the camera tool isn’t as crisp as you might like, and if you insert too many of them then Excel might have a tantrum and crash.

 

References

Jon Peltier: https://peltiertech.com/dot-plots-microsoft-excel/

Naomi Robbins: http://www.b-eye-network.com/newsletters/ben/2468

Stephanie Evergreen: http://stephanieevergreen.com/easy-dot-plots-in-excel/

Please Share

If you liked this please click the buttons below to share.

email icon twittericon fb icon LI icon
Excel dot plot chart

More Excel Charts Posts

excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.
stacked bar waffle chart

Stacked Bar Excel Waffle Charts

Stacked Bar Excel Waffle Charts are an alternate to using conditional formatting to build waffle charts, and some say they're easier.
Category: Excel Charts
Previous Post:Visualizing Parts to a Whole in Excel Charts
Next Post:Excel Chart Formatting Tips

Reader Interactions

Comments

  1. Karin Mack

    December 30, 2020 at 7:36 am

    This is very helpful! Thanks for the detailed instructions, and demo file. I’d like to have my names (your Departments A,B,C,D), all on an axis (left or right) and not be a part of the dot label. Is there a way to trick excel into that?

    Reply
    • Mynda Treacy

      December 30, 2020 at 8:31 am

      Thanks, Karin. One way to have the labels on the left is to add another series with a value of zero and assign the labels to that (left aligned).

      Reply
  2. Bryce

    May 23, 2020 at 3:47 am

    If both ends of my dumbbell chart are the same value, how do I make both circles visible? I currently have it set up as you do, so the outlined circle is on the left and the filled circle is on the right. When they are the same value, only the filled circle is visible. How can I make it so the outlined circle is layered on top, making them both visible?

    Reply
    • Mynda Treacy

      May 23, 2020 at 9:11 am

      Hi Bryce,

      Try making the first dot smaller and a different filled colour so that the second dot is visible from behind.

      Mynda

      Reply
  3. Michael

    August 15, 2019 at 7:30 am

    You’re missing a step. Once the first series is plotted you need to left-click to select all the data points and then click on the + sign on the top right side of the chart and click on “Error Bars”. Then you have to delete the vertical portion of the error bars.

    Taking these steps will ensure that Excel doesn’t default to “Vertical Error Bar” box but will instead bring up the “Horizontal Error Bar” box.

    Reply
    • Mynda Treacy

      August 15, 2019 at 10:00 am

      Thanks for spotting this. I’ve edited that step to say:

      Select the dots > Chart Tools > Design > Add Chart Element > Error Bars > Percentage. Then open More Error Bar Options from the same menu.

      Choosing percentage ensures a horizontal error bar is inserted.

      Cheers,

      Mynda

      Reply
  4. Kiran

    May 27, 2019 at 2:54 am

    In a excel table contains different column headings, but we need some column from table to be pasted some sheets and some column on other sheets. Some entire table in different sheets

    Reply
    • Mynda Treacy

      May 27, 2019 at 7:56 am

      Hi Kiran,

      You can use a PivotTable to do this: https://www.myonlinetraininghub.com/excel-pivot-tables-to-extract-data

      Mynda

      Reply
  5. Steve

    December 27, 2017 at 2:56 am

    Thanks Mynda,

    This is great for showing actual and planned performance for a set of business units or sites. By using the ‘power’ of NA() and a couple of additional columns one can have dumbbell charts with positive performance ending in a green dot, negative in red.

    This also does away with the need for a negative error column and also deals with the labeling issue Jerry Cook raised below.

    I would have liked to have an arrow head at the end of the error bar to emphasize the direction of movement have not had time to figure that out yet…

    Great work and hope that you and the family have enjoyed Christmas and all the best for 2018.

    Reply
    • Mynda Treacy

      December 27, 2017 at 8:24 am

      Thanks, Steve! Glad you’ll find it useful.

      For the forward pointing arrow you could use an image type Marker and reference an image file of an arrow that you create. In the Marker Options > Built-in > Type; choose the image file.

      Best wishes for 2018 🙂

      Mynda

      Reply
  6. Tim Anderson

    December 11, 2017 at 2:09 am

    Excellent!! Thank you for this. I already have uses for this. I will also experiment with using this type of plot as an alternative to a Gantt chart. Great stuff as always. Thanks.

    Happy Holidays

    Tim Anderson

    Reply
    • Mynda Treacy

      December 11, 2017 at 9:47 am

      Thanks, Tim! Great idea for the Gantt chart alternative.

      Happy holidays to you too 🙂

      Reply
  7. Jo

    December 10, 2017 at 9:17 pm

    I love the simplicity and yet it covert all of the information

    Reply
    • Mynda Treacy

      December 11, 2017 at 9:43 am

      Thanks, Jo. Glad you liked the dot plots.

      Mynda

      Reply
  8. Jerry Cook

    December 8, 2017 at 4:02 am

    Great tools! Quick question on the dumbbell chart with higher prior period. Did you manually change the label alignment on the “backward” item, or did you find a hidden method to automatically switch the alignment for negative error bars?

    Happy Holidays!
    Jerry

    Reply
    • Mynda Treacy

      December 8, 2017 at 8:11 am

      Hi Jerry,

      Yes, I had to format that label manually. No tricks, sorry. Although, if you had many negative errors you could add those 2017 values/dots as a different series and then apply the formatting to that series. It would be slightly quicker, but would also require more set up in the table etc. Swings and round-a-bouts.

      Mynda

      Reply
    • Microsoft Excel Recalc Or Die

      February 2, 2020 at 4:08 pm

      Hi Jerry,

      There is a way to have labels to be automatically positioned depending on the value of the series. Like Mynda said, you would have to do more set up in the table and add additional series to the chart, it seems complex but the trick is very simple.

      I would like to leave (and if I may Mynda, of course) this video tutorial where I explain to how set up the series in order to get the label positioned correctly and automatically. (also when new data comes in, they will positioned accordingly to the values of the data.)

      Link: https://www.youtube.com/watch?v=Rf_WF_F8VZU

      I hope it is still useful. It a great chart, thanks Mynda for sharing this post!

      Reply
  9. Renny Schweiger

    December 7, 2017 at 11:58 pm

    Nice article. I’m glad you discussed the issue of starting plots at zero. Personally, I would have a hard time creating any graph without starting the axis at zero, simply because we so intuitively judge relative lengths and distances. If I were to create a visualization solely to compare differences (as in the dumbbell chart) I might want to just show the relative differences from a initial comparison point at zero, in other words, eliminate the absolute values, but show the difference, either as the actual number or as a percentage of the initial state.

    Keep up the awesome work!

    Reply
    • Mynda Treacy

      December 11, 2017 at 10:06 am

      Thanks, Renny!

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x