A Dot Plot is a graphing utility showing the frequency of different pieces of data in a set. It is extremely effective for showing categorical data. Dot plots can be used for any situation for which a bar chart is commonly used.

Preparing the data for an Excel Chart

Step 1: Enter Raw Data: This chart is a combination Bar-XY series. For the construction of the chart you will need three columns of data: the label column, the values column, and a column of calculated values (“Height”). The Labels and Values columns are used to construct the bar chart. The XY series uses the Values column for X and the Height column for Y. A formula is used in the Height column so the “Heights” are spaced uniformly between zero and one.

The formula is:

=(ROWS($A$5:$A$20)-ROW()+ROW($A$5:$A$20)-0.5)/ROWS($A$5:$A$20)

After entering your labels and values in cells A4 to B20, enter the formula above in cell C5 and copy it down to cell C20.

Constructing the Chart

Step 2: Create Chart: Select the first two columns by highlighting cells A4 through B20. Click the “Insert Tab”. Click the Charts ribbon and select “Bar”, “Clustered Bar”. Click “OK”.

Step 3: Delete legend: Click the legend and press the delete key.

Step 4: Add XY data: Highlight cells B4 through C20. Right click and select “Copy”. Click the chart and click “Paste Special” by clicking on the down arrow under “Paste” and selecting “Paste Special”. In the pop up box click “New Series” under “Add cells as”, click “Columns” under “Values (Y) in”. Click Series “Names in First Row”, and “Categories (X Labels) in First Column”. Click “OK”.

Note: In order to view all labels, you may need to resize your chart by highlighting the chart and click and drag the handles.

Step 5: Right click on the first series of data (blue series). In the pop up box, select “Format Data Series”. Under “Series Options” in the “Plot Series On” section, click “Secondary Axis”.

Step 6: Click the blue series of data and click the “Layout Tab”. Click the “Axes” in the layout ribbon, click “Secondary Vertical Axis” and click “Show Default Axis”.

Step 7: Click on the red series of data and under “Chart Tools” click on “Design Tab” and select “Change Chart Type” and click on “XY chart type”, “Scatter with only Markers”. “Click Close”.

Step 8: Change the Marker: Right click on the markers and select “Format Data Series”. Select “Marker Options” and click “Built In” and select the circle from the drop down list. Click “Close”.

Step 9: Line up the XY Data: Right click on primary Y axis (vertical axis) and select “Format Axis”. Click “Axis Options”. Under “Minimum Values” click “Fixed” and type a value of 0. Under “Maximum Values” click “Fixed” and type a value of 1. Under “Horizontal Axis Crosses” click “Axis Values” and type a value of 0. Click “Close”.

Step 10: Move the primary Y axis (vertical axis) from left to right. Right click the primary X axis and select “Format Axis”. Click “Axis Options” and under “Vertical axis crosses” click “Maximum Axis Value”. Click “Close”.

Step 11: Move the Secondary Vertical Axis Labels to the Left: Right click on the Secondary Y axis along the top of the chart and click on “Format Axis”. Click “Axis Options” and under “Vertical axis crosses” click”Automatic”. Click “Close”.

Step 12: Hide the Secondary X (horizontal) and Secondary Y (vertical) axis. Do not delete them. Right click the Secondary Y axis (axis on right) and select “Format Axis”. Select “Axis Options” and under “Axis Labels” click “None”. Click “Close”. Right click the Secondary X axis (axis on top) and select “Format Axis”. Select “Axis Options” and under “Axis Labels” click “None”. Click “Close”.

Step 13: Line up the Markers with the Bars: Right Click the Y axis and select “Format Axis”. Select “Axis Options” and click “Categories in reverse order”. Click “Automatic” under “Horizontal axis crosses”. Click “Close”.

Step 14: Hide the Bars: Right click the bars and click “Format Data Series”. Under “Fill” click “No Fill” and under “Border Color” click “No Line”. Click “Close”.

Step 15: Add Title: Click the Chart and under “Chart Tools” click on “Layout Tab” then click on “Chart Title” and select “Above Chart”.

Step 16: Edit Title: Click the title and highlight the text. Enter the text you want. Change the font size by highlighting the new text, right click and select “Font”. Select the desired font size. In this example we used 12. Click “OK”.

Step 17: Clean up Chart Junk: Click the horizontal grid lines and press the delete key. Click the horizontal line at the top of the chart and press the delete key. Click the vertical line on the right of the chart and press the delete key.