# How to Make a Dot Plot

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. (Please see example in Excel Worksheet).

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

This entry was posted in Editing Charts & Graphs, Excel Tips & Tricks. Bookmark the permalink.

### 7 Responses to How to Make a Dot Plot

1. Jen says:

Interesting. My thought was that dots are most often associated with correlation (scatter plot); multiple responses (a sort of frequency distribution viz); plotting 2 continuous variables against each other…

The first thing I did was look at the picture – skipped right over the words! – and expected that the AE’s were being correlated versus some continuous variable like dosage. My second thought was “why not use a bar chart”?

2. Meic Goodyear says:

What a rigmarole to go through just because Excel doesn’t include the option to designate the vertical axis for categories in a line chart.

3. jonmcrawford says:

Something that Kathy neglected to mention is that the bar chart doesn’t allow for blank categories, while the scatter plot does. A Bar Chart will move your categories right next to each other with equal space, because it thinks it is “helping” you. Scatter plots will actually help you discover where you have gaps (which could be good or bad depending on what you’re measuring and how)

4. Alain Girard says:

If you look at the last image, the horizontal scale starts at zero, but on the previous image, I have a dash instead of a zero.

How can I change the dash to zero? I went to Format axis, but got no result in modifying the scale.

• Lisa says:

Thank you for your question. I think the steps below should resolve the issue you are having.

Original graph when you complete step #2.

Click on horizontal axis and select Format Axis and select Number. Change decimal places to 0 and press close.

Graph should now look like.

5. Thomas says:

Thank you for sharing this interesting solution on how to generate a dot plot. It is quite a work-around, but it works and this is most important.