How to Make Dot Plots in Excel? (Step by Step)
I have created one of the sample data item-wise sales numbers for five months, below is the sample data of the same. We will create a Dot Plot for this excel data. Follow the below steps:
- Step 1: Enter numbers 1, 2, and 3 in the next columns of the data. Refer below image.
- Step 2: Now select the first two rows of the data and insert the column chart.
- Step 3: Now right-click on the chart and choose “Select Data”.
- Step 4: In the below dialogue box choose the “Edit” option.
- Step 5: We can see the existing values as our selected range.
- Step 6: Delete these existing values and enter 0, 0, 0 for all the three items.
- Step 7: Click on Ok, we will have a blank chart like the below one.
- Step 8: Again right-click on the chart and choose “Select Data”. In the below window click on the “Add” button.
- Step 9: In the below window, select the cell A1 for “Series Name” and for “Series Values” enter 1 as the value.
- Step 10: Click on Ok, we will have a chart like the below one.
- Step 11: Now right-click on the chart and choose “Change Chart Type”.
- Step 12: In the below window select “Combo”.
- Step 13: For “Item 1” choose “Scattered Chart” and click on Ok.
- Step 14: Now we will have a chart like the below with just a dot, column bar replaced with just a dot.
- Step 15: Right-click on the chart and choose “Select Data” again. In the next window select “Item 1” and click on “Edit”.
- Step 16: Now we can see below the options window.
- Step 17: For “Series Name” choose cell A1, for “Series X Values” choose the column which has number 1 (the one we have entered manually in step 1) and for “Series Y Values” choose Item 1 numbers.
- Step 18: Click on Ok, now we will have a dot plot chart for “Item 1”.
- Step 19: Now we need to repeat the same process for the other two items i.e. “Item 2” & “Item 3”.
Below is for Item 2.
Below is for Item 3.
Now we will have a “Dot Plot” chart ready like the below one.
As we can see instead of the traditional “Column Bar” chart we have “Dot Plot” chart.
Things to Remember
- There is no built-in dot plot chart in excel.
- We need a combination of the scattered chart to create a dot plot chart.
- We can increase the size of the dots in case if required under format data series section.
- If you need a horizontal dot plot chart then we need to insert the bar chart instead of the column chart.
This has been a guide to Dot Plots in Excel. Here we discuss how to make dot plots in excel along with examples and downloadable excel template. You may learn more about excel charts from the following articles –