Dot plots were developed by Cleveland to take advantage of
viewers ability to interpret position along a common scale. They
show categorical labels on the Y Axis and continuous values on the X axis,
comparable to an Excel bar chart.
The example Excel dot plot to the right shows the magnitude of US petroleum use
in 2005. While Excel does not provide pre-made dot plots, this
tutorial shows how to make a dot plot in Excel using an XY
Scatterplot and a simple dummy Y axis series.
here for Worksheet demonstrating Dot
plots and Y axis dummy series.
Making a Dot Plot in 7 Steps?
A Dot Plot is simply an XY chart with a dummy Y axis series. Here's our data table.
We have Energy Source (Col D) and Energy Use - Quadrillion BTU (Col E). We want the energy use
to be on the X axis and we want the Energy Source to be on the Y axis. XY charts
can't use text on either the X or Y axis so we need to create a dummy axis
series for the Y axis.
1. Organize Data Table: We sort the energy sources by Quad BTU use, with the lowest
use on the top and highest use on bottom. We can then assign a rank to each
source, with a 0 for least use (solar) and 8 for highest use (petroleum). We add
0 values for the X axis positions of our energy source labels.
2. Create Raw Chart: With the Chart Wizard, we can insert an XY chart
with the X values pointing to the annual use and the Y values pointing to Rank.
This gives us our raw chart ready for formatting.
3. Remove Y Axis Grid Lines and Y Axis Labels: We remove the original Y Axis
rank labels with the Format Axis dialog. We also remove the Y grid lines.
Our chart is beginning to look like a Dot plot.
4. Add Y Axis Label Series: To add our Y axis source labels, we will add a
new Y Axis Label series. The X values are all 0's and the Y values are the rank
values. We use the Paste Special dialog to add our new series to the chart.
We can replace the colored box markers with horizontal lines.
5. Add Y Axis Data Labels: Now that we have our Y Axis markers for
each energy source, we want to add labels to them. There are two popular and
Using John Walkenbach's Chart Tools Data Label tool, we designate the range
($D$3:$D$11) as the source for our data labels.
We place the labels to the left of our markers by adjusting the label
6. Add Grid Markers and Use Values: To add Y Axis grid markers we can
use X error bars.
7. Final Editing: For final editing, we can add use value labels,
chart title, X axis title, and notes on source of data.
Click here for Worksheet
demonstrating development of dot plots and Y axis dummy series.