One-dimensional Scatterplot with Jitter in Excel

One-dimensional scatterplots do a good job at showing and comparing the distributions of one or more categories of quantitative variables along a measurement scale.

It is used to provide a useful view of the overall structure contained within the data set by simply showing each observation as a point plotted along a scale line that represents the range of the data values.

When the number of observations is large, jittering can be used to randomly offset the plotting symbols in the vertical direction to avoid overlap.

Unfortunately, Excel charting library doesn't include native one-dimensional scatterplot capabilities. Users are either obliged to resort to VBA or to take the time consuming task of following a long list of steps to create one.

So here I'm offering you an Excel template that takes a data set as an input and creates a one-dimensional scatterplot. You will also get a PowerPoint template for the same graph. The Excel model has a summary worksheet (named PowerPoint) that organizes the data in a way compatible with the data input required by the PowerPoint template.

The Excel model has the following capabilities:

- Sort categories by alphabetical order, mean, median, range or maximum values.

- Sort categories by descending or ascending order.

- Highlight the median, mean, maximum and minimum values by category.

- Highlight a selected (by user) category with a different color using a dropdown list.

- Highlight selected (by user) data points with their labels using a dropdown list.

- Jitter data to avoid overplotting. Amount of jitter can be adjusted using a spinner.

- Define the number formatting of the labels.

- User-friendly interface to adjust the above settings.

- Data entered into Excel Table—hence the chart and formulas update automatically when the datasource expands or shrinks.

- The size of the data matrix is only limited by Excel's charting and calculating capabilities.

- Fully customizable chart using standard Excel functionality.

- Unlocked workbook/worksheets with ability to expand template to suit your needs.

System requirements

The template relies heavily on Excel's Dynamic Arrays capabilities, only available to Microsoft 365 subscribers.

What's the refund policy?

If what you see is not what you expected, just reply to the download email within 30 days, and you'll get a full refund. No questions asked.