Excel Dot Plots

Introduction

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.

Click 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 free chart labeler add-ins:

• XY Chart Labeler  (Rob Bovey)           
• Chart Tools (John Walkenbach)

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 alignment.

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.