In the early 1980’s Cleveland and McGill showed that we are better at interpreting the relationship between points on a line better than any other method of representing numerical information – including line length, as used in the ever-popular Bar Chart.
The result of their work was a new chart type, the Dot Plot, which enables a better comparison between two points than a side by side Bar Chart. These charts are flexible and easy to read.
Unfortunately, Microsoft Excel doesn’t have a Dot Plot as a default chart type. But, with a little manipulation and a few formatting tricks, we can turn a Scatter Plot into a Dot Plot.
Note: The Dot Plots described here are not the dot plots used by mathematicians and statisticians. In those disciplines, a dot plot is used to compare frequency counts within categories or groups. They look a bit like a Histogram made up of dots.
Note: This tutorial applies to Microsoft Excel 2016. But you should be able to take the ideas shown here and get similar results in earlier versions of the software (where the feature described is available).
Creating a Dot Plot
To create a Dot Plot, the first thing you need to do is add a new column to your data table, which we’ll call Spacing. This should contain a simple sequence of numbers 1, 2, 3, and so on: One for every row of your data.
Select the first series column in your table (Male in this example) and the Spacing column. (You can do this by selecting the Male data, then holding down the Ctrl key while selecting the Spacing column.)
Then insert a Scatter Plot.
On the Ribbon Chart Tools Design tab, click the Select Data button.
Click the Add button and fill in the text boxes: Series name (here: Female), Series X values (here: D4 to D6) and Series Y values (the spacing data, here: E4 to E6). (You can do this by clicking in a text box, then selecting the appropriate range on the worksheet.)
Clicking OK returns you to the Select Data Source dialog. Clicking OK again returns you to the worksheet.
If you have additional data series repeat the previous step as required for the other columns. (But be careful if adding more than two series. The chart can become crowded. Stick to one, two, or maybe three dots per line at most.)
Now to format the Dot Plot.
If there’s a legend, delete it. It’s not needed.
Next you need to increase the size of the dots and add data labels.
To do this, first, right click on a dot in one of the series and select the Format Data Series … option from the menu.
This will open the Format Data Series panel on the right of the screen.
Look for Marker Options under the Fill & Line tab in the panel. Select Built-in.
If it doesn’t show a circle, change it to a circle.
Then increase the size to around 15 to 20. You can also change the colour of each series at this point, if appropriate, using the Color control in the Fill and Border sections of the panel.
In addition, if it helps readers interpret the chart, you can add data labels to each dot by right clicking on any dot in the series and selecting Add Data Labels.
Then right click again, this time selecting Format Data Labels … .
Make sure only the X Value is selected in the Label Contains section of the Format Data Labels panel, and select Center in the Label Position section.
Next, change the labels to white using the Font Color control on the Ribbon Home tab.
Repeat this for the second and any additional series in the chart.
Now we’re nearly there. Just a few more steps to go.
Click on the y-axis to select it. Then change the y-axis maximum value to match the largest number you have in the Spacing column (in this case 3), and the minimum value to match the smallest number in your Spacing column in this case 1). Also set the Major Units to 1 (this removes any intermediate lines between the dot lines).
Now delete the y-axis.
Adjust the maximum (and possibly the minimum) for the x-axis in the same way to ensure most effective use of the chart space.
And, I normally delete the x-axis at this point, but sometimes you may feel it’s better left in to provide a reference for your reader.
Click on one of the vertical gridlines and delete them. They’re not needed and only clutter the display.
Click inside the plot area and drag the left-hand side in a little, to make space for the category labels (in this case Pass, Credit and Distinction).
Then add text boxes to the chart with the labels (Pass etc.), positioned to the left of the plot area (where the y-axis would normally be). And maybe make them grey to lessen their visual impact slightly.
Finally, enter a meaningful title, and you’re done.
By the way, you can create a vertical Dot Plot by adding the spacing column to the left of your data series, rather than the right as seen here.
But from that point, the process is basically the same as shown in this tutorial, except the axes are reversed. So, spacing range is used for the x-axis, the data values are used for the y-axis and you select the Y Value (not the X Value) in the Label Contains section of the Format Data Labels panel.
Now you know how to create a Dot Plot.
Try it out the next time you think a bar chart is the way to go, but the display is cluttered (maybe due to many bars), or where the relationships between side by side bars are difficult to interpret.
See if it helps to makes your message clearer.