Creating Dot Plots in Excel

In Dot Plots we show how to create box plots using the Dot Plot option of the Real Statistics Descriptive Statistics and Normality data analysis tool. We now show how to create these dot plots manually using Excel’s charting capabilities.

Example 1: Create the dot plot for Example 1 of Dot Plots using Excel’s charting capabilities. The data for this example is replicated in range A3:C13 of Figure 1.

Figure 1 – Dot Plot (step 1)

Step 1: Highlight the first two rows of the data (column headings and one row of data) and select Insert > Charts|Column Chart and select the Clustered Column option. The result is shown in Figure 1.

Step 2: We next click on the chart and so Charting Tools appears in the ribbon. Now select Design > Data|Select Data. This will bring up a dialog box similar to that shown in Figure 2 of Creating Box Plots with Outliers.

Click on the Edit button on the left side of the dialog box to edit the Series1 series (i.e. the only series created thus far). The dialog box in Figure 2 now appears. Change the Series values: field as shown in the figure, namely a series of zeros separated by commas, one zero for each of the three groups of data.

Figure 2 – Dot Plot (step 2)

After clicking on the OK button on this dialog box and on the Select Data Source dialog box that reappears, the result will be that vertical bars on the chart in Figure 1 will disappear (actually their values will be set to zero).

Step 3: Next add a column with all 1’s (column D), a column with all 2’s (column E) and a column with all 3’s (column F) since we have three groups. The result is shown in Figure 3.

Figure 3 – Dot Plot (step 3)

Step 4: We now click on the chart and select Design > Data|Select Data. This will again bring up a dialog box similar to that shown in Figure 2 of Creating Box Plots with Outliers. This time we click on the Add button, and when a dialog box similar to that shown in Figure 2 appears, we click on the Series Name field and then select cell A3 and then insert the value 1 in the Change the Series values: field (actually we can put almost any values in these fields; the important thing is to add a series with only one element).

Step 5: We next need to change this added series to a scatter chart. This is done by right-clicking on the newly added series and selecting Change Series Chart Type… This brings up the dialog box shown in Figure 4.

Figure 4 – Dot Plot (step 5)

Change the chart type for Brand A from Clustered Column to Scatter and click on the OK button. This will replace the column bar for Brand A by a dot at 1 on the vertical axis.

Step 6: We again click on the chart and select Design > Data|Select Data. This will again bring up a dialog box similar to that shown in Figure 2 of Creating Box Plots with Outliers. This time we click on the Brand A series and then click on the Edit button. We fill in the dialog box that appears, as shown in Figure 5 and click on the OK button.

Figure 5 – Dot Plot (step 6)

Step 7: In step 6 we modified the data for the first series (after we reclassified it as a scatter plot in step 5. This time we simply add a new series (since it will automatically be considered to be a scatter plot type. Since the Select Data Source dialog box is now displayed (i.e. the dialog box similar to that shown in Figure 2 of Creating Box Plots with Outliers, just need to click on the Add button.

We now fill in the dialog box that appears with the information for Brand B, as shown on the right side of Figure 6.

Figure 6 – Dot Plot (step 7)

Finally, we repeat step 7 for Brand C and add a chart title to obtain the final result, as shown in Figure 7.

Figure 7 – Dot Plot (final)