Dumbbell chart, aka Connected Dot plot is a type of chart that’s suitable for visually comparing values especially between two periods or showcase change. In this post, I demonstrate its usefulness as well some key steps on how to create one in Microsoft Excel (Office 365).
Let’s start with the data. I have some data (sources: Bing, macrotrends.net, OECD-ilibrary.org) on life expectancy of a few countries for comparison. The data I want to show is from 2003 and compare to 2023 data.
Sure, I could just do a simple bar or column chart, but a dumbbell chart would be best for this as it conveys all the critical information in a very concise way. So, let’s get started with creating one.
Once the data is shaped correctly, it all starts with a Scatter plot with two series: one for each year to compare. Then the key is to add Error Bars for each series to connect the two series data points by country. Then a few tweaks to make it look just right.
But shaping the data, or the table structure in our case, is the most critical part. Since a scatter plot requires both X and Y axes values to be numeric, we have to encode categorical values into numeric. Additionally, we need to add custom values for the Error Bars…to do that, we need to calculate the difference in values between the periods (e.g. life expectancy of a country in 2023 minus life expectancy of the country in 2003). The values can be positive or negative obviously, so our chart needs to handle those situations as well.
Finally, to put back the category labels on the chart, we need to add a new series, which requires another column with a fixed value. More on that below.
My first table arrangement looked like this:
So, we’ll need add 3 more columns: Encoded category values (Encoded Y for example), Difference of life expectancy between the two periods, and a fixed value for Y-Labels (Y-LabelValues for example). Now, the table looks like this:
We are ready to plot a scatter graph. I added the 2003 column values as a series, with series Y values being the Encoded Y column values (remember, ideally we want the Country column values here, but it cannot be done yet as they are not numeric as they’re required to be for Y values in a scatter plot). Then I added 2023 column values as an additional series.
Next, I added error bars for each datapoint for both series—which generated both horizontal and vertical error bars for each data point. I only want the horizontal error bars since I want to connect datapoints from 2003 to 2023 horizontally only, so I deleted the vertical ones. Now the chart is taking shape and looks like this:
Next, I need to add the Y-LabelValues column values as a new series to the chart and it’ll go along the Y-axis. While I’m at it, I changed the min and max values of Y-axis to 1 and 7 respectively because my table rows start from 1 and end at 7th country row…this ensures there’s no extra, unwanted vertical whitespace in the chart. Once I added the new series, the series X values are set to the Y-LabelValues column values, and series Y values to Encoded Y column values. This way, I get those datapoints nicely clustered along the Y-axis. Why did I choose 55 as the Y-LableValues? That’s because I want them to show up on the left-most X-axis edge and remember, I rescaled the X-axis min to 55 and max to 90? If you don’t remember why, that’s ok—I’ll explain. I did this because the average life expectancy values in my dataset don’t need to start at default 0 and go beyond 90. But we’re done with it yet because I want to set those datapoints’ labels to be the category labels which are in Country column. So, I formatted the series and instead of the default Y values, I pointed it to the Country column values. At this point, the chart looks like this:
Now, we’re getting somewhere! From this point forward, I removed the X-axis, removed the vertical gridliness, adjusted the formatting of the markers for each series, adjusted the title, chart background etc. to make it look polished and professional. I kept the horizontal gridlines because it makes it easier for viewers’ eyes to follow the path from left to right. The final chart looks like this:
And it’s a beauty! Let’s see what it conveys…
We can clearly see which countries had the highest increase in longevity (longer connecting line between two periods mean biggest change), the red markers are for 2023 while the blue ones are for 2003 so we can clearly see where life expectancy had a decline (e.g. Palau) over the periods—the red marker is on the left of the blue one, meaning it went down. We can also see right away that India has the highest gain during this period of these nations, while USA had the slowest improvement. We can also see Japan’s life expectancy is the highest overall among the selected nations followed by Canada. The colored numbers on chart match the colors of years in the title, making them instantly distinguishable. Wow, this chart tells a lot of stories in a compact image! To add a pro touch, I added an image on the chart background with high transparency.
I hope you found it interesting and inspiring. Be sure to check out some of the other cool charts you can create in Excel below. And if that’s not your cup of tea, check out my book on creating cool automated robots and gadgets right in your home with minimal investment! Book link is below, available on sale both in Paperback and Ebook formats. Happy charting, happy learning!
Related Blogs:
▛Interested in creating programmable, cool electronic gadgets? Give my newest book on Arduino a try: Hello Arduino! 
▟