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.

How to Make a Dot Plot 01

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

How to Make a Dot Plot 02

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

How to Make a Dot Plot 03

How to Make a Dot Plot 04

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

How to Make a Dot Plot 05

How to Make a Dot Plot 06

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

How to Make a Dot Plot 07

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

How to Make a Dot Plot 08

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

How to Make a Dot Plot 09

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

How to Make a Dot Plot 10

How to Make a Dot Plot 11

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

How to Make a Dot Plot 12

How to Make a Dot Plot 13

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

How to Make a Dot Plot 14

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

How to Make a Dot Plot 15

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

How to Make a Dot Plot 16

How to Make a Dot Plot 17

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

How to Make a Dot Plot 18

 

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

How to Make a Box Plot 19

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.

How to Make a Dot Plot 20

 

Share
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.
      dot plot fig 1
      Click on horizontal axis and select Format Axis and select Number. Change decimal places to 0 and press close.
      dot plot response fig 2
      Graph should now look like.
      dot plot response fig 3

  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.

  6. maddie says:

    thx i had home work and this helped me a lot

Leave a Reply

Your email address will not be published. Required fields are marked *


two × 9 =