How to Make a Dot Plot in Excel (3 Easy Ways)

The dot plot is a very powerful data visualization tool. It gives a quick visual analysis of the central tendency, dispersion, and skewness of the data. Despite these implications, Excel does not offer a direct way to make a dot plot like the other plots. In this article, we will discuss three effective methods to make a dot plot in Excel.

How to Make a Dot Plot in Excel: 3 Handy Ways

In this discussion, we will learn three handy ways to make a dot plot. Firstly, we will use the Select Data option in Excel to make that happen. Then we will resort to the COUNTIF function to do the task. Finally, we will rearrange our data in a long format to make a dot plot. We will use the dataset below to illustrate the first method.

1. Using Select Data Option to Make a Dot Plot in Excel

In this method, we will use the Select Data option to make a dot plot. Follow the ensuing steps to accomplish the task.

Step 1:

• Firstly, write down the horizontal axis number: 1,2,3 in consecutive cells along the row.
• Here, we will write them down in F5, G5, and H5 cells.

Step 2:

• Next, enter the same number in the remaining dataset cells in the corresponding column.

Step 3:

• Thirdly, select the first data row along with the header row of the data that you want to plot.
• In our case, we selected the cells in the range C4:E5.
• After that, go to the Insert tab.
• From there, click on the Bar Chart icon in the Chart group.

Step 4:

• Then, from the drop-down option, select the Clustered Column chart.
• Consequently, we will get a bar chart of the selected data.

Step 5:

• Then, right-click on the chart.
• From the options, choose Select Data.
• Consequently, Select Data Source dialogue box will appear.

Step 6:

• From the dialogue box, select Edit.
• As a result, the Edit Series dialogue box will be on the screen.

Step 7:

• In the Edit Series dialogue box , under the Series Value option, write down three zeros separated by commas(0,0,0).
• Consequently, we will have a blank chart.

Step 8:

• After that, right-click on the blank chart.
• From the available options choose Select Data.
• Consequently, we will again find ourselves in the Select Data Source dialogue box.

Step 9:

• From that dialogue box, select Add.

Step 10:

• Firstly, in the Edit Series dialogue box, write 1 for the Series values.
• Then, click on the upward arrow sign which is to the right of the Series name option.

Step 11:

• From the dataset select the header row of the first dataset column.
• We’ll choose the “Laptop Sales“-named C4 cell in this instance.
• Finally, select the downward arrow to the right of the box to confirm the selection.

Step 12:

• Consequently, we will find that the Series name is now “Laptop Sales”.
• Finally , select OK.
• As a result, we will have a bar chart named Laptop Sales.

Step 13:

• Then, right-click on the chart.
• Select Change Chart Type from the list of options.
• As a result, a dialogue box will appear on the screen.

Step 14:

• From the dialogue box, firstly, select Combo, under All Charts.
• Secondly, change the chart type of the “Laptop Sales” dataset to Scatter.
• Finally, click OK.
• Consequently, we will have the first dot of our dot plot.

Step 15:

• Afterward, right-click on the dotted chart.
• From the list of choices, select Select Data.
• Consequently, this will again take us to the Select Data Source dialogue box.

Step 16:

• Firstly, select the “Laptop Sales” entry from the dialogue box.
• Then, select Add.
• As a result, we will be in the Edit Series dialogue box.

Step 17:

• In the Edit Series, choose the upward arrow to the right of the Series X Values option box.

Step 18:

• Then, from the dataset, select the entire column containing the 1’s as the horizontal label of the chart.
• In our case, the cells are in the range F5:F9.
• Finally, select the downward arrow to confirm the selection.

Step 19:

• As a result, you will see that our selection is added under the Series X Values.
• Then, select the upward arrow to the right of the Series Y Values option box.

Step 20:

• Then, from the dataset, select the data under the header “Laptop Sales”
• In our case, the cells are in the range of C5:C9.
• Finally, click on the downward arrow to confirm the selection.

Step 21:

• Consequently, our selection will be added to the Select Y Values box.
• Finally, click OK.

Step 22:

• Consequently, we will get our desired dot chart for that particular dataset.

Step 23:

• To get the dot representation of the rest of the data, repeat the procedures from Step 16 to Step 21.
• In that case, select all of the columns that contain 2s and 3s under the Series X Values box.
• The Series Y Values will be the data under the header rows “Smart Phone Sales” and “ Tablet Sales” respectively.

2. Applying COUNTIF Function to Make a Dot Plot in Excel

In this method, we will use the COUNTIF function to do our desired job. Follow the subsequent steps to do that.

Step 1:

• Firstly, select the D5 cell and write down the following formula.

• The first argument of the COUNTIF function is the range of the dataset where it will operate.
• In this case, the range is C5:C5 with absolute cell references.
• The second argument is the value of the number of which the function will count.
• Here, the value is in the cell C5 and it is 25.
• Finally, hit Enter.

Step 2:

• Consequently, we will get the value count.
• In this case, the count is 1.
• Now, move the cursor down to the last cell of the dataset.
• We will get all the values counted in the C column.
• Actually, this formula counts how many times the values 25 and 26 appeared with an increment of 1.

Step 3:

• Then, select the data you want to plot.
• In our case, it will be in the range C5:D11.
• After that, go to the Insert tab.
• Then, hoover down to the Chart option.
• From there, click on the Scatter chart command.

Step 4:

• Consequently, we will get our desired dot plot.

3. Reorganizing Data in Long Format to Make a Dot Plot

In this method, we will reorganize our data to a long format to make a dot plot. Follow the outlined steps below to do that.

Step 1:

• Firstly, reorganize the data in the long format as indicated below.

Step 2:

• Secondly, select the long formatted data.
• In this instance, they are in the cell range E5:F11.
• Then, go to the Insert tab.
• Navigate down to the Chart option.
• Finally, click on the Scatter chart option.

Step 3:

• Consequently, we will see that our data are plotted as a dot plot.

Download the Practice Workbook

Conclusion

In this article, we have talked about three ways to make a dot plot in Excel. These methods will empower the readers with the visualization power of a dot plot. The methods will also allow them to use dot plots on a regular basis as they are very easy to implement.