Dot plot / Dumbbell and Lollipop charts in Excel

Description
The Dot Plot is my second favorite chart type to display data—especially if it matches the original design of William Cleveland; First place belongs to the Scatterplot.

Dot plots have many advantages over standard ways of displaying categorical data—such as the bar graph or the pie chart.

First, dot plots use position to encode quantitative data—more accurate in conveying quantitative information to the viewer compared to bar graphs that use length and pie charts that use angle or area as an encoding mechanism.

Second, dot plots use less ink, and hence are less cluttered (plus the added benefit of reducing your printer's cartridge expenditure).

And third, scales don’t have to start at zero—generally, a requirement for bar graphs. This allows for narrowing down the range on the scale and consequently more differences can be noticed in the data.

Unfortunately, Excel charting library doesn't include native capabilities to produce dot plots. Users are either obliged to resort to VBA or to take the time consuming task of following a long list of steps to create one.

So here I'm offering you an Excel template that takes a data set as an input and creates a dot plot/Dumbbell/Lollipop chart. You will also get a PowerPoint template for the same graph. The Excel model has a summary worksheet (named PowerPoint) that organizes the data in a way compatible with the data input required by the PowerPoint template.

The Excel model has the following capabilities:

- Sort categories by alphabetical order, variable 1 (i.e. traffic fatalities 2005), variable 2 (traffic fatalities 2015), the gap between the two variables or as per the original data source.
- Sort categories by descending or ascending order.
- Show/hide one or two data series (i.e. 2005, 2015).
- Switch instantly between Dot Plot, Dumbbell Chart and Lollipop Chart.
- Define the number formatting of the labels.
- User-friendly interface to adjust the above settings.
- Data entered into Excel Table—hence the chart and formulas update automatically when the datasource expands or shrinks.
- The size of the data matrix is only limited by Excel's charting and calculating capabilities.
- Fully customizable chart using standard Excel functionality enabling you to design a large variety of charts.
- Unlocked workbook/worksheets with ability to expand template to suit your needs.

What's the refund policy?

If what you see is not what you expected, just reply to the download email within 30 days, and you'll get a full refund. No questions asked.

NOTE: THE TEMPLATE RELIES HEAVILY ON EXCEL'S DYNAMIC ARRAYS CAPABILITIES, ONLY AVAILABLE TO OFFICE 365 SUBSCRIBERS.