Essentially, Google Sheets describes data as a series of columns and rows. Nevertheless, while this can be an excellent way to visualize data, it’s not necessarily attractive.
Google Sheets has some “infographic”-like features that help you visualize information. If you are familiar with reporting features in other software systems, you probably know the basics: pie charts, bar charts, and scatter plots.
But Google Sheets doesn’t have a built-in way to create a scatter plot. Since Google doesn’t have a scatter chart spreadsheet feature, you will need to create your own chart. Luckily, we’ve created a comprehensive step-by-step guide on ways to create dot plots in Google sheets.
So, how do you create dotted cells in Google Sheets?
In a dot chart (also called a dot plot), which is one of the simple statistical graphs, we use points to visualize data.
Two similar charts available for this chart in Google Sheets are Scatter and Bubble.
From there, we can create a dot plot using scatter charts.
So, how do we go about this?
First, we need to present the data in a completely different way. So a scatter plot looks like a dot plot. So how do we formart format the data?
We can format data with formulas and create point plots using scatter plots charts in Google Sheets.
In this post, you will find all the necessary formulas and instructions to create your own dot plot chart using Google Spreets.
Based on your data, it might be necessary to create dotted cells in with a different format.
Putting that to consideration, here are two examples.
The formula we might want to use depends on the type of data that we will use to create the dot plot chart.
This chart type is ideal for displaying research data (two-dimensional distributions/ratios).
In the first example, I have gathered the following data showing how long it takes researchers to solve a statistics problem.
The data above is C4:D14 in the spreadsheet.
Here are step by step guidelines for creating a dot chart/scatter plot in Google Sheets.
1. In cell F4, enter the following array formula to copy the values from C4:C14 to F4:F14.
Also, you can copy and paste (Ctrl+C, Ctrl+V) values from C4:C14 to F4:F14. However, later, updating any value in the range C4:C14, it will not be reflected in the chart.
2. In cell F5, enter the series formula below and drag it to F14.
= sequence(1, B2: B11)
The outcome will resemble this:
1. Select the range F4:L14 and go to the menu “Insert” and then select “Chart”.
2. Select the “Scatter” chart type from the resulting drop-down menu.
3. At this point, you shouldl see a dot plot (Draft) in Google Sheets as shown below. Double-click the legend icon and use the “Delete/Backspace” button on your keyboard to do away with it.
4.Alternatively, you can “erase” the checkmarks on the Y-axis. You cannot remove it. Changing the text color to white will hide it.
So, why is y-axis scaling unnecessary?
This is because we only want to count the points on the vertical axis.
For example, look at the checkmark 6 on the x-axis in the image. Only points are counted. You will see that the number of dots equals 4.
This means that each of the 6 researchers spends 4 minutes solving their own math problem.
More so, you will observe that the points (sequence points) on the graph are equidistant.
To mask the vertical axis values, choose Vertical Axis > Text Color in the Graph Editor’s customize panel.
Then the scatter plot filled in Google Sheets with the above sample data will be displayed as follows.
With that in mind, let’s move on to example 2.
This type of chart is suitable for building comparative (time-varying) data.
In this example we shall consider sales of two products in the first quarter (January to March).
Here, I’ll use three formulas to format the above data to fit in dot plots in Google Sheets.
Note that formulas and data formatting are not even remotely related to our first example. (Example 1)
The reason is that the two data types that create a scatter plot/Dot Plots are different.
Let’s view the guideline.
Our example 1 data above is in the range B4:C7. If so, enter the formulas below into cells F5, G5, and H5, respectively.
The formula in F5 will be:
This formula generates consecutive numbers from the smallest value in the range B5:C7 to the largest value in the range B5:C7.
This means that the formula will generate values from 4 to 20 in ascending order.
The formula in G5 will be:
This is a Vlookup array formula that returns/generates values from the corresponding values in B4:B7 and F4:F20.
The formula in H5 will be:
Dragging formula G4 to H4 will give the formula outlined above. The goal is the same here. This will return the value C5:C7.
Designate G3 as PRODUCT 1 and H3 as PRODUCT 2.
That is the step-by-step process for generating dots in Google Sheets based on the data provided above.
You will observe that the steps listed below are almost similar to the steps that we used in our example 1.
1. Select range F3:H20.
2. Select Insert > Bar Chart > Scatter Plot.
3. In the “Setup” tab of the chart editor, enable “Switch Rows to Columns”.
4. Double-click the title and click the Delete button to remove the title/legend, as shown in example 1.
As shown in example 2, a dot plots chart in Google Sheets would look like this.
That is it. I hope you enjoyed reading this step-by-step guide and found it helpful!