Skip to main content

Create a dot plot chart in Excel

Author: Siluvia Last Modified: 2024-11-06

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.

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.

steps of creating a horizontal dot plot chart for a series

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)

steps of creating a horizontal dot plot chart for a series

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

steps of creating a horizontal dot plot chart for a series

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:

steps of creating a horizontal dot plot chart for a series

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.

steps of creating a horizontal dot plot chart for a series

The chart is displayed as follows.

steps of creating a horizontal dot plot chart for a series

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

steps of creating a horizontal dot plot chart for a series

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

steps of creating a horizontal dot plot chart for a series

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:

steps of creating a horizontal dot plot chart for a series

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

steps of creating a horizontal dot plot chart for a series

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

steps of creating a horizontal dot plot chart for a series

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

steps of creating a horizontal dot plot chart for a series

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

steps of creating a horizontal dot plot chart for a series

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.

steps of creating a horizontal dot plot chart for a series

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.

steps of creating a horizontal dot plot chart for a series

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

steps of creating a horizontal dot plot chart for a series

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.

steps of creating a horizontal dot plot chart for a series

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

steps of creating a horizontal dot plot chart for a series

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.

steps of creating a horizontal dot plot chart for a series

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.

steps of creating a horizontal dot plot chart for a series

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.

steps of creating a horizontal dot plot chart for a series

Now a horizontal dot plot chart is complete.

steps of creating a horizontal dot plot chart for a series

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:

steps of creating a vertical dot plot chart for a series

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

steps of creating a vertical dot plot chart for a series

Now a vertical dot plot chart is complete.

steps of creating a vertical dot plot chart for a series

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.

steps of creating a vertical dot plot chart for a series

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

steps of creating a horizontal dot plot chart for multiple series

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

steps of creating a horizontal dot plot chart for multiple series

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

steps of creating a horizontal dot plot chart for multiple series

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.

steps of creating a horizontal dot plot chart for multiple series

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

steps of creating a horizontal dot plot chart for multiple series

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

steps of creating a horizontal dot plot chart for multiple series

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.

steps of creating a horizontal dot plot chart for multiple series

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.

steps of creating a horizontal dot plot chart for multiple series

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.

steps of creating a horizontal dot plot chart for multiple series

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

steps of creating a horizontal dot plot chart for multiple series

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

steps of creating a horizontal dot plot chart for a series

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:

steps of creating a horizontal dot plot chart for multiple series

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.

steps of creating a horizontal dot plot chart for multiple series

Now the chart is display as follows.

steps of creating a horizontal dot plot chart for multiple series

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

steps of creating a horizontal dot plot chart for multiple series

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.

steps of creating a horizontal dot plot chart for multiple series

Now the chart is displayed as below.

steps of creating a horizontal dot plot chart for multiple series

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.

steps of creating a horizontal dot plot chart for multiple series

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:

steps of creating a horizontal dot plot chart for multiple series

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.

steps of creating a horizontal dot plot chart for multiple series

steps of creating a horizontal dot plot chart for multiple series

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

The chart is now displayed as below.

steps of creating a horizontal dot plot chart for multiple series

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

steps of creating a horizontal dot plot chart for multiple series

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.

steps of creating a horizontal dot plot chart for multiple series

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:

steps of creating a horizontal dot plot chart for multiple series

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

steps of creating a horizontal dot plot chart for multiple series

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.

steps of creating a vertical dot plot chart for multiple series

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.

steps of creating avertical dot plot chart for multiple series

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

steps of creating a vertical dot plot chart for multiple series

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

steps of creating a vertical dot plot chart for multiple series

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.

steps of creating a vertical dot plot chart for multiple series

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

steps of creating a vertical dot plot chart for multiple series

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.

steps of creating a vertical dot plot chart for multiple series

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.

steps of creating a vertical dot plot chart for multiple series

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

steps of creating a vertical dot plot chart for multiple series

Now the chart is displayed as the below screenshot shown.

steps of creating a vertical dot plot chart for multiple series

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

steps of creating a vertical dot plot chart for multiple series

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.

steps of creating a vertical dot plot chart for multiple series

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:

steps of creating a vertical dot plot chart for multiple series

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:

steps of creating a vertical dot plot chart for multiple series

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

steps of creating a vertical dot plot chart for multiple series

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

steps of creating a vertical dot plot chart for multiple series

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

steps of creating a vertical dot plot chart for multiple series

Easily create a horizontal dot plot chart for a series in Excel

The Dot Chart utility of Kutools for Excel can help you quickly create a horizontal dot chart for a series in Excel with several clicks only as the below demo shown.
Download and try it now! 30-day free trail

Download the sample file

steps of creating a horizontal dot plot chart for a series

Video: Create dot plot chart in Excel

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

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, Edge and Firefox.