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.


Download the Practice Workbook


3 Handy Ways to Make a Dot Plot in Excel

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.

Sample Data


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.

3 Handy Methods to Make a Dot Plot in Excel

Step 2:

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

3 Handy Methods to Make a Dot Plot in Excel

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.

3 Handy Methods to Make a Dot Plot in Excel

Step 4:

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

3 Handy Methods to Make a Dot Plot in Excel

Step 5:

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

3 Handy Methods to Make a Dot Plot in Excel

Step 6:

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

3 Handy Methods to Make a Dot Plot in Excel

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.

3 Handy Methods to Make a Dot Plot in Excel

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.

3 Handy Methods to Make a Dot Plot in Excel

Step 9:

  • From that dialogue box, select Add.

3 Handy Methods to Make a Dot Plot in Excel

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.

3 Handy Methods to Make a Dot Plot in Excel

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.

3 Handy Methods to Make a Dot Plot in Excel

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.

3 Handy Methods to Make a Dot Plot in Excel

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.

3 Handy Methods to Make a Dot Plot in Excel

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.

3 Handy Methods to Make a Dot Plot in Excel

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.

3 Handy Methods to Make a Dot Plot in Excel

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.

3 Handy Methods to Make a Dot Plot in Excel

Step 17:

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

3 Handy Methods to Make a Dot Plot in Excel

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.

3 Handy Methods to Make a Dot Plot in Excel

Step 19:

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

3 Handy Methods to Make a Dot Plot in Excel

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.

3 Handy Methods to Make a Dot Plot in Excel

Step 21:

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

3 Handy Methods to Make a Dot Plot in Excel

Step 22:

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

3 Handy Methods to Make a Dot Plot in Excel

Step 23:

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

Sample Data

Read More: How to Move Data from Row to Column in Excel (4 Easy Ways)


Similar Readings


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.
=COUNTIF($C$5:$C5,C5)
  • 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 the number of which the function will count.
  • Here , the value is in the cell C5 and it is 25.
  • Finally, hit Enter.

Sample Data

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 value 25 and 26 appeared with an increment of 1.

Sample Data

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.

Sample Data

Step 4:

  • Consequently, we will get our desired dot plot.

Sample Data

Read More: How to Make a Box Plot in Excel (With Easy Steps)


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

In this method, we will reorganize our data to 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.

Sample Data

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.

Sample Data

Step 3:

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

Sample Data

Read More: How to Make a Modified Box Plot in Excel (Create and Analyze)


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.


Related Articles

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo