Views > Excel charts > How to create a Dot Plot in Excel

The Dot Plot is my second favorite chart type to display data—especially if it matches the original design of William Cleveland; First place belongs to the Scatterplot.

Dot plots have many advantages over standard ways of displaying categorical data—such as the bar graph or the pie chart.

First, dot plots use position to encode quantitative data—more accurate in conveying quantitative information to the viewer compared to bar graphs that use length and pie charts that use angle or area as an encoding mechanism.

Second, dot plots use less ink, and hence are less cluttered (plus the added benefit of reducing your printer's cartridge expenditure).

And third, scales don’t have to start at zero—generally, a requirement for bar graphs. This allows for narrowing down the range on the scale and consequently more differences can be noticed in the data.

Naomi Robins wrote a thorough article explaining the benefits of dot plots as an alternative to bar charts.

Let’s say I want to visualize the data shown in the table below to compare traffic fatality rates by state for the year 2017 in the United States.

1. Highlight the range B2:C53 and then click Insert > Insert Column or Bar Chart > Clustered Bar. The chart should look like this:

2. Right-click the Vertical (Category) Axis and then, on the shortcut menu, click Format Axis.
3. In the Format Axis pane, with Axis Options selected, under Labels, set the Interval between labels to Specify interval unit and keep the default value of 1.
4. Turn off the Primary Major Vertical Gridlines. 5. Format the border of the Plot Area to Solid line with grey color.
6. Turn on the Primary Major Horizontal Gridlines and change their line Dash type to Dash.
7. Turn off the border of the Chart Area by setting the Border to No line. Resize the chart and the chart should look like this:

8. Right-click row number 3 on the worksheet and then, on the shortcut menu, click Insert to add one single empty row above Alabama.
9. Right-click row number 55 on the worksheet and then, on the shortcut menu, click Insert to add one single empty row below Wyoming.
10. Click anywhere in the chart. The currently displayed source data is selected on the worksheet, showing sizing handles. On the worksheet, drag the sizing handles to include the new data in range B3:C55. The chart should look like this:

HOW TO CREATE A DOT PLOT IN EXCEL

11. Right-click the chart and then, on the shortcut menu, click Select Data.
12. In the Select Data Source dialog box, add a new data series by clicking the Add button under Legend Entries (Series).
13. In the Edit Series dialog box, enter Fatality rate 2017 in the Series name edit box. Next, edit the Series values to include cells C3:C55. The chart should look like this:

14. Right-click the Fatality rate 2017 series in the chart and then, on the shortcut menu, click Change Series Chart Type.
15. In the Change Chart Type dialog box, check the Secondary axis checkbox for Fatality rate 2017 series. Next, choose the Scatter icon from the dropdown list under Chart Type. The chart should look like this:

16. To align the points with the bars, add the following formula into cell D3
  = 1 - (ROWS($B$3:$B$55) - ROW() + ROW($B$2)) / (ROWS($B$3:$B$55) - 1)
17. Select cell D3 then drag the handle to copy the formula into cells D4:D55. The data source should look like this:

18. Right-click the chart and then, on the shortcut menu, click Select Data.
19. In the Select Data Source dialog box, select the Fatality rate 2017 data series and then click the Edit button under Legend Entries (Series).
20. In the Edit Series dialog box, edit the Series X values to include cells C3:C55 and the Series Y values to include cells D3:D55 as follows:

21. Click on the Ok button twice to close the Edit Series and the Select Data Source dialog boxes. The chart should look like this:

22. Right-click the Secondary Vertical (Value) Axis in the chart and then, on the shortcut menu, click Format Axis.
23. In the Format Axis pane, with Axis Options selected, set the Bounds Maximum to 1.
24. Select the Vertical (Category) Axis in the chart. In the Format Axis pane, with Axis Options selected, turn on the On tick marks under Axis position.
25. Select the Secondary Vertical (Value) Axis in the chart and then turn it off by pressing the Delete key on the keyboard. The chart should look like this:

26. Right-click the Fatality rate series (horizontal bars) in the chart and then, on the shortcut menu, click Format Data Series.
27. In the Format Data Series pane, under Series Options, click the Fill & Line icon. Next, turn off the fill by clicking on the No Fill radio button under Fill. The chart should look like this:

28. Select the chart then click the Charts Elements button located on the upper-right corner of the chart. Next, in the expanded menu, check the Secondary Horizontal option under Axes, as per the screenshot below:

29. Right-click the Horizontal (Value) Axis in the chart and then, on the shortcut menu, click Format Axis.
30. In the Format Axis pane, with Axis Options selected, set the Bounds Minimum to 0.5 and the Bounds Maximum to 1.9.
31. Next, choose the Outside option from the dropdown list from the Major type under Tick Marks.
32. Click the Secondary Horizontal (Value) Axis in the chart and then set the Bounds Minimum to 0.5 and the Bounds Maximum to 1.9.
33. Next, choose the Outside option from the dropdown list from the Major type under Tick Marks. The chart should look like this:

38. Finally, change the Marker's fill and border color. Add title and source text boxes. The chart should look like this:

39. You’ll notice that the chart is dynamic in a way that it can handle negative inputs without breaking down. Try it. Replace Massachusetts' fatality rate— just for the sake of testing—with a negative value such as -0.6. Because the Bounds Minimum for both the Horizontal (Value) Axis and the Secondary Horizontal (Value) Axis were set to 0.5 (see step 30 and 32 above), the dot representing Massachusetts is hidden. To show it, reset the Bounds Minimum to Auto for both horizontal scales. The chart should look like this:

40. To fix the clutter created by the Vertical (Category) Axis overlap with the data points, right-click the Vertical (Category) Axis in the chart and then, on the shortcut menu, click Format Axis.
41. In the Format Axis pane, with Axis Options selected, set the Label Position to Low under Labels. The chart should look like this:

34. Lets bring some order to the data and rank the states by fatality rates from largest to smallest. Select the range B4:C54.
35. On the Data tab, in the Sort & Filter group, click Sort.
36. In the Sort dialog box, under Column, in the Sort by box, select Column C from the dropdown list.
37. Under Order, select Smallest to Largest from the dropdown list. Click the Ok button and the chart should look like this: