What’s a Dot Plot?
A dot plot is a simple chart that plots its data points as dots (markers), where the categories are plotted on the vertical axis and values on the horizontal axis.
It’s a nice plot, but it isn’t built into Excel’s default chart offerings. It’s not too hard to make your own Dot Plots, and the Peltier Tech Charts for Excel features several variations of Dot Plots among its custom chart types.
The Problem
A popular chart type in Excel is a line chart. Below is a line chart showing some simple data. It’s a pretty clean chart, especially without connecting lines between data points. But the labels are hard to read without hurting your neck.
It would be nice to be able to rotate this chart, to make the labels more readable. The only reason not to rotate the chart would be if the categories (Label A, Label B, etc.) were dates, because people are used to time being plotted horizontally. But we can’t simply make a “line chart” in Excel that had the categories on the vertical axis and values on the horizontal axis. The closest we can get is a bar chart.
But the bar chart doesn’t look as clean, because of all that color. And if there are multiple sets of bars, it can be hard to distinguish the different series of data.
Let’s Build a Dot Plot in Excel
It’s possible and not too difficult to construct dot plots in Excel. But it takes a little work. We’ll use an XY scatter chart for the dots, and a bar chart for the vertical axis labels.
The Data
Here is some simple data for our dot plot. The “Labels” column provides the categories for the bar chart, which will adorn the vertical axis. The “Values” column provides the horizontal values for both bar and XY chart. And the “Height” column provides the vertical coordinates for the XY points. The height values are calculated using a simple algebraic formula:
=(ROWS($A$2:$A$17)-ROW()+ROW($A$2)-0.5)/ROWS($A$2:$A$17)
Start the Chart
Select the first and second columns of data and insert a bar chart. I’ve already configured it to be slightly taller than it is wide.
The labels are going in the wrong order, so double click on the vertical axis to open the Format Axis dialog. In Excel 2007 you have so right click on the axis and choose Format Axis… from the pop-up menu (which works in any version). Or in any version you can select the axis, then press Ctrl+1 (numeral one).
Fun tip: Select any Excel object (a cell, a shape, a chart element) and press Ctrl+1 (numeral one) to open the corresponding Format dialog. I think this is my #1 time saving shortcut in Excel, and it ticks me off that other programs don’t use it too. I’m going to harp on you repeatedly until you’ve learned to do it automatically. You’re welcome.
While you’re formatting the vertical axis in the Format Axis dialog, choose Horizontal Axis Crosses At Maximum Category, so the horizontal axis labels return the the bottom of the chart.
Add the XY Data
Select and copy the second and third columns of data, select the chart, and use Paste Special to add the data as a new series, with series in columns, series name in first row, and categories in first column.
Excel adds the data as another bar chart series.
Right click on the new series, choose Change Series Chart Type from the pop-up menu, and select the XY Scatter type. Excel adds a new vertical axis on the right of the chart, since it can’t plot numeric values on the category axis on the right. Fortunately Excel can use the existing horizontal axis for both the bar and XY data.
Align the dots with the bars by formatting* the secondary vertical axis (right side of the chart) so that its minimum and maximum are 0 and 1.
*You used that Select plus Ctrl+1 trick, right? Right?
Format the vertical axis (Ctrl+1!!) so it has no line color and no axis labels, which hides the axis.
Format the bar chart series (don’t make me say it!) so that it has no border and no fill color, which hides the bars and leaves the dots standing alone. I’ve formatted the dots so they have no fill color and a thicker marker line, because I think these look better than boring old filled circles.
And that’s our Dot Plot.
Adding more data to the dot plot doesn’t clutter it up the way it does with bar charts.
Drop Lines May Increase Readability
In this example, the labels are pretty close together, and some of the dots are pretty far away, so it might be tricky for people to tell exactly which dot goes with which label. Often dot plots have lines connecting the dots to the labels to clarify this. We’ll use horizontal error bars for these connecting lines.
Select the dots and add error bars. In Excel 2007 and 2010 find the Error Bars control on the Chart Tools > Layout tab. In Excel 2013 it’s on the menu that pops up when you click the plus sign icon. Choose any of the options, because we need to modify the settings.
For XY points, Excel adds vertical and horizontal error bars. Select the vertical error bars and press Delete.
Format the horizontal error bars, select the Minus direction and the No Cap style option, and for error amount, select the Percentage option and enter 100.
Finally format the error bars, giving them a light line color so they don’t overwhelm the dots.
Dot Plots in Peltier Tech Charts for Excel
This tutorial shows how to create Dot plots, including the specialized data layout needed, and the detailed combination of chart series and chart types required. This manual process takes time, is prone to error, and becomes tedious.
I have created Peltier Tech Charts for Excel to create Dot Plots (and many other custom charts) automatically from raw data. This utility, a standard Excel add-in, lays out data in the required layout, then constructs a chart with the right combination of chart types. This is a commercial product, tested on thousands of machines in a wide variety of configurations, Windows and Mac, which saves time and aggravation.
A simple dialog presents you with a few options for your chart.
The output appears in an instant. The necessary calculations are hidden behind the chart, which is scaled and sized so the chart axis categories line up with the rows of the table.
I’ve also added some additional types of dot plots, including lollipop charts and dumbbell plots. The dialog is simple to use:
The output of this dialog is produced on an inserted worksheet:
Please visit the Peltier Tech Charts for Excel page for more information.