twitter-mono
facebook-mono
linkedin-mono
youtube-mono

Views > Excel charts > How to create a one-dimensional scatterplot in Excel with
jitter

In a previous blog, How to Create a Density Scatterplot in Excel, I discussed one problem encountered in data visualization—Overplotting.

Several strategies for minimizing the effects of overplotting were suggested—One of them is Jittering.

Have a look at the one-dimensional scatterplot below. It shows the distribution of 2,155 items sold by 9 salespersons. Despite using a straight vertical line as a plotting symbol—to enhance the distinguishability of individual data points—there still is quite a bit of overplotting.

Finally, play with the Jitter amount, say 8,000, to narrow the band width of the jitter and the chart should look like this:

Thank you for reading, and I hope you found this useful. If you have any questions, find me on Twitter and ask me anything.

HOW TO CREATE A ONE-DIMENSIONAL SCATTERPLOT IN EXCEL WITH JITTER

HOW TO CREATE A ONE-DIMENSIONAL SCATTERPLOT IN EXCEL
WITH JITTER

Or alternatively buy product on Gumroad

Or alternatively buy product on Gumroad

Or alternatively buy product on Gumroad

1. In the chart, right-click the data series and then, on the shortcut menu, click Format Data Series.
2. In the Format Data Series pane, with Fill & Line selected, under Marker, set the Marker Options to Built-in, Type circle and its Size to 3.
3. Next, set the Fill to Solid fill, its Color to orange and its Transparency to 75%. The chart should look like this:

4. Enter the label "Jitter amount" in cell F15.
5. Enter the number 3,500 in cell G15.
6. Enter the label "Jittered y" in cell D1.
7. Enter the following formula in cell D2:
   = C2 + RANDBETWEEN(-100,100) / $G$15
Select cell D2 and drag the handle of the cell to copy the formula into the range D3:D2156.

The worksheet should look like this:

8. Next, in the chart select the data series and drag the y coordinate range from "y" (range C2:C2156) to "Jittered y" (range D2:D2156) as per the snapshot below:

And the chart should look like this:

Jittering—the process of displacing the points by a small amount in the direction perpendicular to the scale line—could be one solution to the overlap problem as shown in the graph below.

In a previous blog post I showed you how to create the above One-dimensional Scatterplot. So I’m going to start off from where we left it and transform the graph with jittering so that it looks like the chart below.

How to create a one-dimensional scatterplot in Excel with jitter 2
How to create a one-dimensional scatterplot in Excel with jitter 1
How to create a one-dimensional scatterplot in Excel with jitter 3
How to create a one-dimensional scatterplot in Excel with jitter 4
How to create a one-dimensional scatterplot in Excel with jitter 5
How to create a one-dimensional scatterplot in Excel with jitter 6
How to create a one-dimensional scatterplot in Excel with jitter 7
How to create a one-dimensional scatterplot in Excel with jitter 8

Download Excel Model