How to create a Jitter Plot in Excel (100% dynamic)

At this year’s Global Excel Summit, two presentations included Jitter plots. So, this got me thinking about how I would create one in Excel.

If you’re not sure what a jitter plot is, this is the final chart we are creating in this post:

Jitter plot in Excel

I decided to make the jitter plot 100% dynamic so the chart automatically updates for new data.

In each stage, there are many approaches we could have used. This post contains one method; therefore, you may decide to take an alternative approach for creating your jitter plot in Excel.

Table of Contents

Download the example file: Click the button below to join the Insiders program and gain access to the example file used for this post.

File name: 0194 Dynamic jitter plot.zip

Watch the video

Jitter plot in Excel | 100% dynamic | Excel Off The Grid

Watch on YouTube

Example

The source data for the jitter dot plot is a Table called Data with Name, Department and Salary columns.

Data for example

We also have two parameters Jitter Offset (cell G5) and Jitter Size (cell G6). These are used in the calculations and can be adjusted for fine-tuning the final display.

Calculations

Before we create the chart, we need to calculate all the values. Unfortunately, there are quite a lot of them.

Depending on how you structure workbooks, you could avoid some of the calculations. However, in the interests of splitting data from presentation, and providing a clear flow, these are the calculations I have included.

Sections and labels

We start by creating the list of departments and the XY values required to place them on the chart in the correct location.

Labels section

Label

The formula in cell F10 is:

=SORT(UNIQUE(Data[Department]))

This creates an alphabetically sorted list of values from the Department column.

Label X

The department name label is placed in the center of each section. For this, we need a list of values starting at 0.5 and increasing by 1 for each department (0.5, 1.5, 2.5, 3.5… etc.)

The formula in cell G10 is:

=SEQUENCE(ROWS(F10#))+G5

This simply creates a list representing the number of departments and adjusts for the value in the Jitter Offset cell (G5).

Label Y

The department lables are placed at the bottom of the chart; therefore, we need a Y value of 0 for each department.

The formula in cell H10 is:

=SEQUENCE(ROWS(F10#),1,0,0)

This creates a zero for each department name.

Chart values

The next stage is to calculate the XY values for the grey dots.

Values section

Name

Let’s start with a list of the employee names.

The formula in cell J10 is:

=Data[Name]

That’s simple enough.

Department

Now let’s add the department name.

The formula in cell K10 is:

=Data[Department]

Also pretty simple.

X Values

Along the horizontal axis, the values are grouped by department.

To ensure the values are not all in a vertical line, they require a random offset to create the jitter effect.

The formula in cell L10 is:

=MATCH(K10#,F10#,0)+RANDARRAY(ROWS(Data[Department]),1,-G6,G6)/2+G5

Let’s break down this formula:

=MATCH(K10#,F10#,0)

Returns the position of the Department in the Label list (cell F10#). For example, Finance is the second department in the list, so it returns 2.

=MATCH(K10#,F10#,0)+RANDARRAY(ROWS(Data[Department]),1,-G6,G6)/2

Next, we want to calculate the jitter effect. RANDARRAY creates a list of random numbers from -0.5 to 0.5 (the 0.5 is based on the value in cell G6).

The value is divided by two to ensure the spread of the jitter is less than the Jitter Size.

=MATCH(K10#,F10#,0)+RANDARRAY(ROWS(Data[Department]),1,-G6,G6,FALSE)/2+G5

To ensure the dots fall between the vertical lines we apply the Jitter Offset (cell G5, which is -0.5 in our example).

This calculation may seem confusing. Ultimately, this calculation ensures:

  • Directors have X values between 0.25 and 0.75
  • Finance staff have X values between 1.25 and 1.75
  • Head Office staff have X values between 2.25 and 2.75
  • etc, etc.

Y Value

The Y Value comes from the Salary column. Therefore, the formula in cell M10 is:

=Data[Salary]

Highlight values

The chart includes a selector to determine which value to highlight. We require XY calculations for this value.

Where the name is not the same as the selector, we return the NA() function. #N/A is a special value as it is not rendered on a chart. This ensures only the selected values are displayed.

Highlight X

The formula in cell N10 is:

=IF(J10#=T6,L10#,NA())

Cell T6 contains the employee name we wish to highlight in the chart.

If the name value in J10# is equal to the selected employee name, then the X value is returned. Otherwise, the NA() function is returned.

Highlight Y

Highlight Y is similar to Highlight X, but for the Y values.

The formula in O10 is:

=IF(J10#=T6,M10#,NA())

Sorted name list

Cell T6 will contain an alphabetically sorted data validation list containing the employee names. This makes it easier to find which employee to highlight.

The formula in cell Q10 is:

=SORT(Data[Name])

Named Ranges

To ensure the chart is dynamic, we create a named range for each element that appears on the chart.

Select cell F10.

Click Formulas > Define Name.

Formulas - Define Name

Enter the following values in the New Name dialog box:

  • Name: Label
  • Scope: Example (the name of the worksheet containing the calculations).
  • Refers to: Example!$F$10#
    Label - Named Range

Follow these steps to create all the following named ranges. There should be 7 in total.

  • Label: Example!$F$10#
  • LabelX: Example!$G$10#
  • LabelY: Example!$H$10#
  • X: Example!$L$10#
  • Y: Example!$M$10#
  • HighlightX: Example!$N$10#
  • HighlightY: Example!$O$10#

Data validation List

Now let’s create the data validation list to select the employee to highlight.

Select cell T6. Click Data > Data Validation.

In the Settings tab of the Data Validation dialog box enter the following.

  • Allow: List
  • Source: =$Q$10#
Data Validation List

Create the chart

All the groundwork has been completed, we are now ready to create the chart.

In the ribbon, click Insert > Scatter (drop-down) > Scatter

Insert - Scatter chart

Right-click on the chart area, click Select Data… in the menu.

In the Select Data Source dialog box click Add.

In the Edit Series dialog box enter the following

  • Series name: Value
  • Series X values: =Example!X
  • Series Y values: =Example!Y
Add Data Series

Add two more data series as follows:

Labels

  • Series name: Label
  • Series X values: =Example!LabelX
  • Series Y values: =Example!LabelY

Highlight Values

  • Series name: Highlight
  • Series X values: =Example!HighlightX
  • Series Y values: =Example!HighlightY

There should be 3 data series:

Chart Data - all series

Click OK to close the dialog box.

The chart currently looks like this:

Initial Chart View

Chart formatting

From here it’s all formatting.

Format the data points

Format the value markers as follows:

  • Fill: Solid fill: Dark grey, Transparency: 50%
  • Border: No line
Format data series

Next, format the highlight value dots to provide emphasis:

  • Fill: Solid fill: Select a bright color, Transparency: 0%
  • Border: No line
  • Marker Size: Consider changing the size to make the selection clearer

Format the labels

Select the Label markers. Click the [+] > Data Labels > Below

Data label below

The data labels appear showing 0. Format the data labels to show the Value from Cells. In the Data Label Range dialog box, use the Label named range.

Value from cells

Finally, set the label markers to no border and no fill.

Final formatting

The final formatting required:

  • Delete the X-axis labels
  • Apply suitable number formatting to the Y-axis labels
  • Resize the plot area to fit nicely in the chart area

Auto-resizing the X axis

Unfortunately, we have an issue. Due to how Excel scales chart axis automatically, there is likely to be a blank selection to the right of the chart.

Empty space on chart

As we want the chart to be dynamic, we cannot change the Max of the X axis as it will become hard-coded.

To make this dynamic we will apply one formula and some VBA.

Axis count

In cell K10 enter the following formula:

=COUNTA(F10#)

This calculates the number of departments required for the chart.

Create a named range for this cell called Axis Count.

VBA code

In the code module for the worksheet enter the following code:

Private Sub Worksheet_Calculate()

Dim chtObj As ChartObject
Dim value As Integer
Dim chtName As String

chtName = "Chart 1" 'Change to your chart name

Set chtObj = Me.ChartObjects(chtName)

value = Me.Range("'" & Me.Name & "'!AxisCount").value

chtObj.Chart.Axes(xlCategory).MaximumScale = value

End Sub
VBA Code for auto adjust

The code above adjusts the X-axis so that the maximum value is equal to the number of departments.

Every time the sheet calculates the chart axis also updates.

The chart now looks as follows:

Jitter plot in Excel

Adding new data

Now, let’s see if this is fully dynamic. What happens if we add new data for the Legal department to the Table?

Dynamic jitter plot in Excel

Boom! Everything updates correctly and the chart includes the new data.

Conclusion

To create the dynamic jitter plot, we used the following steps:

  • The Table holds the data.
  • Dynamic array calculations refer to the Table.
  • Name ranges refer to the dynamic array calculations.
  • The chart refers to the named ranges.
  • Any non-updating aspects are handled with VBA.

Through this, we created a jitter plot in Excel that is fully dynamic.

Related Posts


Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

Leave a Comment