Dot Plots in Excel (Table of Contents)
Introduction to Dot Plots in Excel
It is said and scientifically proved that; the human brain is better in manipulating and interpreting dots relationships than the lines. Therefore, it is said that dot plots can be easier to understand than the conventional bar charts or histograms. Dot plot allows viewing the relationship between two points more precisely than the bar charts as well as it is flexible in nature. It can easily be read as well, which comprises a better understanding to the user. Though there is no direct inclusion of dot plots under excel and it’s different versions, you still can have it done under Microsoft Excel with some amendments under conventional bar charts.
What is a Dot Plot?
A dot plot is the same as that of bar plot however, the only difference is the chart will have dots associated with data points in contradiction of column bars present under bar chart. In this chart, the data points/series values are plotted as dots which are placed across different categories. These categories are usually plotted on X-axis.
In this article, we will see how we can create the dot chart in excel. Though we have conventional scatter plots added under Excel, those can not be considered as dot plots.
Example of Dot Plots in Excel
Suppose we have month-wise sales values for four different years 2016, 2017, 2018 and 2019 respectively. We wanted to draw a dot plot based on this mentioned data.
We will be first inserting a 2-D Column bar chart on the first two rows of this data present. i.e. we will be inserting graph on cells B1:E2. Follow the below step to insert a column chart based on the first two rows.
Step 1: Navigate to Insert tab on the Excel ribbon and move towards the Charts section. There, click on Insert Column or Bar Chart dropdown bar and select Clustered Column chart under 2-D Column Chart section.
4.8 (1,875 ratings)
View Course
You’ll be able to see a graph as shown in the screenshot below.
As we wanted to create a chart with dots, it is mandatory to remove all these bars for every year values. We can’t remove the bars series directly otherwise our dot chart will get affected. Therefore, what we will do is we set the series values for these bars as zero so that they are not visible under the chart. Follow the below step to do so.
Step 2: Right-click on the chart area and click on Select Data option. It will open up a Select Data Source window where you can change the data source for Y-axis values a.k.a. Legend Entries (Series) as well as X-axis categories a.k.a. Horizontal (Category) Axis Labels.
Step 3: Click on Edit button under Legend Entries (Series) option you’ll be able to see all the values we have selected previously while creating the chart.
Step 4: Under Series values: option inside Edit Series, change the values to 0,0,0,0 for all four columns. This as discussed above will negate the column bars appearing on the chart.
Once you press on OK twice after setting the series values to zero, you’ll be able to see no column bars in the chart. See the screenshot below.
Now, we are going to update this chart into a dot chart with the combination of column chart & scatter chart. However, we need to decide spacing for four years data as it is mandatory while we create the dot chart. This spacing is nothing but the distance in units which allows the dot chart separate from each other. I will choose spacings as 1, 2, 3, 4 for four years. This means, all the dot charts associated with four years will be at 1 unit distance from each other. This spacing can be changed as per your requirement and is totally up to you on how you decide this.
Step 5: Again click on Select Data option and choose to Click on Add button under Legend Entries (Series) to add some series values.
Step 6: Mention Series name as cell A1 (Where year 2016 is marked) and series value as 1 under Edit Series window.
Your graph will look as shown in the screenshot below.
Step 7: Now we are going to use the combo of scatter plot along with Bar chart. Right-click on graph area and choose Change Chart Type option.
Step 8: Select Combo chart option under Change Chart Type window and change the chart type as scatter plot for the series added. See the screenshot below.
After hitting the OK button, you can see the chart below.
Step 9: Now, you again have to go to Select Data and click on Edit button for series 2016 under Legend Entries (Series)
Step 10: Inside the Edit Series window, change the reference of the series name as A1 (2016), reference for series x values should be the column of spacing with the unit as 1 (column E). Reference for series Y values as sales for the year 2016. See the screenshot below for better understanding.
Step 11: Press OK key two times and you can see a graph changed to dot plot as shown below.
Step 12: Follow the same procedure from step 9 to step 11 for 2017, 2018 and 2019 with respective spacings as 2,3 and 4 (column F, G, and H respectively) so that we can add the sales values for these years on the chart. Final chart layout should be like the screenshot below after adding the values for these years sales.
Note that, I have changed the colors for dot series here in the graph. You can choose your custom colors while creating a one under format series options or design options.
This is how we can create dot plots in Excel. The article ends here. We will see some points to be remembered with reference to the dot plot in Excel.
Things to Remember
- There is no built-in option for Dot Plot available in any version of Microsoft Excel. However, you can create this graph with the help of a combination of a simple bar chart and scatter chart.
- Scatter Plot cannot be directly considered as Dot Plot though it has dots within.
Recommended Articles
This is a guide to Dot Plots in Excel. Here we discuss How to create Dot Plots in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
