Create a dot plot chart in Excel

A dot plot chart is a great alternative to the bar or column chart to show the distribution of data visually. This tutorial provides a step-by-step guide to show you how to create a dot plot chart in Excel.


Create a dot plot chart for one data series

This section is going to show you methods to create a horizontal or vertical dot plot chart for a data series in Excel.

Horizontal dot plot chart for a series

Supposing you want to create a horizontal dot plot chart based on the data as the below screenshot shown, please do as follows.

1. Firstly, create a helper column. In a new column (column C in this case), enter the column header as you need, select the second cell, enter the below formula into it and press the Enter key. Select the result cell and then drag its Fill Handle down to get all results.

=(ROWS($A$2:$A$12)-ROW()+ROW($A$2:$A$12)-0.5)/ROWS($A$2:$A$12)

2. Select the original data range (without the helper column range), click Insert > Insert Column or Bar Chart > Clustered Bar. See screenshot:

3. Then, copy and paste the series values and helper column values (B1:C12) to the chart as follows.

3.1) Select the series values column and the helper column, press the Ctrl + C keys to copy them;
3.2) Click to select the bar chart you inserted just now;
3.3) Click Home > Paste;
3.4) Click Paste Special in the Paste drop-down list. See screenshot:

4. In the popping up Paste Special dialog box, select the New series and the Columns options separately in the Add cells as and Values (Y) in sections, check both the Series Names in First Row and Categories (X Labels) in First Column boxes, and then click the OK button.

The chart is displayed as follows.

5. Right click the blue series and then click Format Data Series in the right-clicking menu.

6. In the Format Data Series pane, select the Secondary Axis option under the Series Options tab.

7. Select the chart, go to the Design tab (in the Chart Tools tab), and then click Add Chart Element > Axes > Secondary Vertical. See screenshot:

After this step, you can see the chart is displayed as follows.

8. Right click the chart and select Change Chart Type from the context menu.

9. In the Change Chart Type dialog box, choose Scatter for the helper column series and then click OK.

10. Right click the Y-axis in the chart, and then select Format Axis from the right-clicking menu.

11. In the Format Axis pane, please configure as follows.

11.1) Enter 0 into the Minimum box;
11.2) Enter 1 into the Maximum box;
11.3) Select the Axis value option and then enter 0 into the textbox.

Now all dots and corresponding bars are on the same horizontal lines.

12. Right click the X-axis and select Format Axis from the context menu.

13. In the Format Axis pane, choose the Maximum axis value option as the below screenshot shown.

14. Click to select the secondary X-axis (on the top of the chart) in the chart, and then select the Automatic option in the Format Axis pane.

Now you can see the chart is changed as the below screenshots shown.

15. Hide the secondary Y-axis in the chart by selecting it, and then selecting the None option from the Label Position drop-down list in the Labels section under the Axis Options tab.

Tips: Do the same operation to hide the secondary X-axis in the chart.

16. Now you need to hide the blue bars and only keep dots displaying in the chart.

Click on any one of the blue bars to select them all, go to the Fill & Line tab in the Format Data Series pane, and then select the No fill option in the Fill section.

17. Right click the Y-axis and select Format axis from the context menu. In the Format Axis pane, check the Category in reverse order box under the Axis Options tab.

Now a horizontal dot plot chart is complete.

Vertical dot plot chart for a series

You can create a vertical dot plot chart for a series in Excel by inserting a Line with Markers chart and then hiding all lines. Please do as follows.

1. Select the original data table (the range A1:B2).

2. Then insert a line with markers chart by clicking Insert > Insert Line or Area Chart > Line with Markers. See screenshot:

3. Right click the lines in the chart, click the Outline drop-down list, and then click No Outline from the drop-down menu.

Now a vertical dot plot chart is complete.


Create a dot plot chart for multiple data series

Supposing you have multiple data series as the below screenshot shown, and want to create a horizontal or vertical dot plot chart based on these series, methods in this section can do you a favor.

Horizontal dot plot chart for multiple series

If you want to create a horizontal dot plot chart for multiple data series, please do as follows.

1. Firstly, you need to create three helper columns with the below formulas.

1.1) For the first helper column, please select a blank cell next to the original data (here I select D2), enter the below formula into it and press the Enter key. Select the result cell and then drag its Fill Handle all the way down to get other values.
=0.5/3

1.2) For the second helper column, select a blank cell next to the first helper column, enter the below formula into it and press the Enter key. Select the result cell and then drag its Fill Handle all the way down to get other values.
=1.5/3

1.3) For the third helper column, select a blank cell next to the second helper column, enter the below formula into it and press the Enter key. Select the result cell and then drag its Fill Handle all the way down to get other values.
=2.5/3

Note: In these formulas, the number 3 represents the number of series you will create a vertical dot plot chart based on. Please change it based on your data series.

2. Now we need to create a clustered bar chart. Please select the first two rows of the original data (A1:C2), click Insert > Insert Column or Bar Chart > Clustered Bar.

3. Right click the chart and select Select Data from the right-clicking menu.

4. In the Select Data Source dialog box, select the Series1 in the Legend Entries (Series) box, and then click the Edit button.

5. Then an Edit Series dialog box pops up. In the Series values box, remove the original value, enter 0,0,0 into it. Finally click the OK button.

6. When it returns to the Select Data Source dialog box, click the OK button to save the changes.

7. Now all bars are hidden in the chart. Select and right click the Y-axis, and then select Format Axis from the context menu.

8. In the Format Axis pane, select the At maximum category option and check the Categories in reverse order box under the Axis Options tab.

9. Right click the chart and click Select Data in the right-clicking menu.

10. In the Select Data Source dialog box, click the Add button in the Legend Entries (Series) box.

11. In the popped out Edit Series dialog box, please configure as follows.

11.1) Select the header of the first series for the Series name box;
11.2) Select all data (except the header) of the first series for the Series values box;
11.3) Click the OK button. See screenshot:

12. Repeat the above step 10 and 11 to add the second and third series. After that, you can see all series are added and listed in the Legend Entries (Series) box of the Select Data Source dialog box, please click OK to save the changes.

Now the chart is display as follows.

13. Right click any one of the series in the chart, and then select Change Series Chart Type from the context menu.

14. In the Change Chart Type dialog box, select Scatter in the Chart Type drop-down for all series except the Series1, and then click OK.

Now the chart is displayed as below.

15. Right click the chart and click Select Data in the right-clicking menu.

16. In the Select Data Source dialog box, select the first series (here I select KTE) under the Series1, and then click the Edit button.

17. In the Edit Series dialog box, you need to:

17.1) In the Series name box, keep the value unchanged;
17.2) In the Series X values box, select all values (except header) of the first series in the original data;
17.3) In the Series Y values box, replace the current value with the values of the third helper column;
17.4) Click the OK button. See screenshot:

18. Now repeat the above step 16 and 17 to edit the rest of series (in this case, only the KTO and KTW left). The series are modified as the below screenshots shown.

19. Click OK in the Select Data Source dialog box to save all changes.

The chart is now displayed as below.

20. Right click the secondary axis and select Format Axis from the context menu.

21. In the Format Axis pane, you need to:

21.1) In the Axis Options section, enter 0 into the Minimum box, enter 1 into the Maximum box;
21.2) In the Labels section, select None from the Label Position drop down list.

22. Select the chart, click the Chart Elements button, go to the Gridlines options and then only check the Primary Major Vertical box. See screenshot:

Now a horizontal dot plot chart for multiple series is complete.

Vertical dot plot chart for multiple series

Please do as follows to create a vertical dot plot chart for multiple series in Excel.

1. Firstly, create three helper columns containing 1, 2 and 3 separately in each column in the next of the original data.

2. Select the first two rows of the original data, and then insert a clustered column chart by clicking Insert > Insert Column or Bar Chart > Clustered Column.

3. Right click the chart and select Select Data from the right-clicking menu.

4. In the Select Data Source dialog box, select the Series1 in the Legend Entries (Series) box, and then click the Edit button.

5. Then an Edit Series dialog box pops up. In the Series values box, remove the original value, and then enter 0,0,0 into it, and then click the OK button.

6. When it returns to the Select Data Source dialog box, click the Add button.

7. In the Edit Series dialog box, select the first cell (A1) for the Series name box, in the Series values box, enter 1, and then click the OK button.

8. Click the OK button to save the changes when it returns to the Select Data Source dialog box.

9. Right click the series in the chart and select Change Series Chart Type from the context menu.

10. In the Change Chart Type dialog box, select Scatter from the Chart Type drop-down list for the KTE series, and click OK.

Now the chart is displayed as the below screenshot shown.

11. Right click the chart and select Select Data from the context menu.

12. In the Select Data Source dialog box, select the series you have created in step 7 (in this case, I select the series “KTE”) and then click the Edit button.

13. In the Edit Series dialog box, please configure as follows.

13.1) In the Series name box, keep the original value unchanged;
13.2) In the Series X values box, select the values in the first helper column;
13.3) In the Series Y values box, replace the original value with the values (except the header) of the first series;
13.4) Click the OK button. See screenshot:

14. When it returns to the Select Data Source dialog box, click the Add button.

15. In the Edit Series dialog box, please configure as follows.

15.1) In the Series name box, select the header of the second series;
15.2) In the Series X values box, select the values in the second helper column;
15.3) In the Series Y values box, replace the original value with the values (except the header) of the second series;
15.4) Click the OK button. See screenshot:

16. Repeat the step 14 and 15 to add the third series (KTW) to the Select Data Source dialog box.

17. Finally, all series are added to the Select Data Source dialog box, click the OK button to save all changes.

Now a vertical dot plot chart for multiple data series is complete.


Download the sample file


Video: Create dot plot chart in Excel


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.