• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Jitter in Excel Scatter Charts

You are here: Home / Excel Charts / Jitter in Excel Scatter Charts
jitter in scatter charts
February 26, 2020 by Philip Treacy

If you use a scatter plot for a dataset that has discrete values in one dimension, for example your x-axis shows the days of the week, you can get points overlapping when you plot the data.

chart without jitter

To make the chart easier to interpret you can introduce jitter to the data points. This means moving the plotted points slightly so they don't overlap so much.

Doing this makes it easier to see the distribution and frequency of values.

chart with jitter

Download Sample Workbook

Enter your email address below to download the workbook containing the data, charts and examples used in this post.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

The data, charts and examples in this post can be downloaded in this workbook.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Creating Jitter

The first thing to say is that one axis needs to be discrete numeric values.

If you are plotting x-y values where x already varies a great deal, then there's no need to use jitter.

In the case of my example, I'm looking at the value of restaurant bills over four different days. These days can be represented by different numbers e.g. 6 represents Friday, 7 is Saturday etc.

Jitter in Power BI

I'm using the same tips dataset from my post on Jitter in Power BI.

NOTE: Excel doesn't provide a built-in way to scatter plot categorical data where the categories are not numeric. If you are in this situation then you will need to assign numeric values to your categories so your data can be plotted, then create your own text labels on the categorical axis.

Shift

We want to move, or shift, the x value a little left and right. We aren't actually changing the value we are measuring which is plotted on the y-axis.

Sample of Data

sample of data

You'll notice that the x-axis values are all 6. This equates to Fri when I set the axis format to date and use a custom format ddd.

To start, use the RAND function to generate a random number. You'll get something greater than or equal to 0 and less than 1.

If you add this to your original x value, it will be increased (of course). Doing this for all your data points will result in the data being pushed along the x-axis in the positive direction.

Sample Data Shifted Right

random number added to original data

Positive shift of data points

But we really want the data distributed either side of the central, original value. To achieve this, deduct 0.5 from the value generated by RAND. For random numbers less than 0.5 you'll end up with a negative value. Adding this to your data point will move it left along the x-axis.

For random values greater than 0.5 (which must also be less than 1) you will end up with a number greater than or equal to 0 and less than 0.5. Adding this to your data point shifts it in the positive direction along your x-axis.

Sample Data Shifted Left and Right

random number shifting source left and right

You end up with some data points shifted left, and some shifted right, giving a nice looking distribution around the original x-axis value.

Even shift of data points

Spread

Having created even shift we can also control the spread of these values, that is, the distance moved from the original value.

To control the spread, multiply the shift value by some number. The larger the number the wider the spread. In the animation below, with a small spread value of 0.1 the data points are clustered close to the central value of Friday. With a spread value of 0.8, the points are spread out much more.

Your browser does not support the video tag.

For the data I am using, a value of 0.3 is good but you will need to find a suitable spread value for your own data.

Jitter Formula

We end up with this final formula to calculate jitter

= x + ((RAND() - 0.5) * 0.3)

jitter formula

In practice we can use a formula like this, and copy down for every data point

=A1+((RAND()-0.5)*$E$2)

where A1 is our value on the x-axis and $E$2 contains the value that controls the spread of the data. If you refer to a cell for the spread value, you can change this on the fly.

NOTE: RAND is volatile so points are recalculated and replotted every time Excel recalculates. This is why the RAND values in the images above are different in each image. You can leave it this way in your workbook or you can copy/paste as values after calculating jitter. I leave it as it is and like pressing F9 to watch the points dance.

Plotting Jittered Data

Plotting the unaltered data we get this

Data without jitter

As you can see there are many overlapping points. Not easy to read.

Applying the jitter formula from above to the x value for each data point, we get a chart that provides a lot more information.

Chart using jitter

jitter in scatter charts

More Excel Charts Posts

burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
highlighting data in power bi visuals

Highlighting Data in Power BI Visuals

Learn several techniques to highlight or label important data points in your Power BI visuals. Sample file and code to download.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
project management dashboard

Excel Project Management Dashboard

Excel project management dashboard video tutorial covering various techniques including conditional formatting, PivotTables, Slicers, charts and more.
Custom Excel Chart Label Positions

Custom Excel Chart Label Positions

Custom Excel Chart Label Positions using a dummy or ghost series to force the label position neatly above the columns of data
Lookup Pictures in Excel

Lookup Pictures in Excel

Lookup Pictures in Excel using values in cells returned by data validation lists (drop down lists) or Slicers. No VBA/Macros required!

Cross Highlight Excel Charts

Filter and Cross Highlight Excel Charts like you can in Power BI using some Excel Power Pivot magic, regular charts and a Slicer.
save chart as image

Save Chart as Image

List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download

More Excel Charts Posts

picture fill excel charts

Picture Fill Excel Charts

Using a stylish picture fill in your Excel Charts is a simple way to make your data visualizations more captivating and memorable
excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.


Category: Excel ChartsTag: excel charts
Previous Post:Custom Excel Chart Label PositionsCustom Excel Chart Label Positions
Next Post:20 Pro Excel Keyboard Shortcuts

Reader Interactions

Comments

  1. Robert R

    August 14, 2020 at 12:39 am

    For the final plot you created, how did you get the points to be separate colors for each day of the week?

    I am having troubles separating the X-axis points into the different categories as I am trying to make a legend based on color.

    Thank you

    Reply
    • Philip Treacy

      August 14, 2020 at 1:00 pm

      Hi Robert,

      The data for each day of the week is added as a separate series so they automatically get different colours.

      Regards

      Phil

      Reply
  2. Robert R

    August 14, 2020 at 12:19 am

    How would I make categorical X-axis labels that are not the days of the week? I am trying to make my X-axis locations.

    Is there a way of editing the X-axis labels so it shows as whatever you want it to?

    Reply
    • Philip Treacy

      August 18, 2020 at 11:17 am

      Hi Robert,

      You can create extra series that have the location as the Series name, and a single x,y co-ord where y is always 0. The x values for each category/location will be the same as the central value of the category.

      So in my example if I wanted to make the Friday category London instead, create a series London,x=6,y=0 which when plotted puts a single marker at 6,0 right under the data points for Friday.

      Then remove the (Primary Horizontal) x-axis.

      Click on the single data point for the London series -> Add data Labels – > Below. Format the Label Options so that the Label Contains only the Series name

      Format the marker for this data point to have no line, no fill. You should end up with the word London under the data points for Friday.

      Repeat for other categories.

      I’ve created an example in this workbook https://d13ot9o61jdzpp.cloudfront.net/files/Custom-X-Axis-Labels-Jitter-in-Excel-Scatter-Charts.xlsx

      If you need any more help please start a topic on the forum and attach your workbook.

      Regards

      Phil

      Reply
  3. Rick

    March 2, 2020 at 7:29 am

    Since this chart is an x-y graph, how do you get the days of the week to show up as categories along the x-axis. I believe this is part of Colin’s question.

    Reply
    • Philip Treacy

      March 2, 2020 at 9:22 am

      Hi Rick,

      In the Creating Jitter section above, all the sample data has the x value of 6, for Friday.

      The days are represented by numbers (Thu = 5, Fri = 6, Sat = 7, Sun = 8) so these are your categories along x.

      When you plot 5, 6, 7, 8 along the x axis, format the axis as Date and set the format to a custom format ddd which displays only the 3 letter abbreviation for the day i.e. Fri, Sat etc.

      Note that the number representing each day ‘wraps’ – so that Sunday is 1 but also 8. Mon is 2 and 9 etc.

      Regards

      Phil

      Reply
      • Rick

        March 4, 2020 at 3:42 am

        Thanks!

        Rick

        Reply
        • Philip Treacy

          March 4, 2020 at 8:38 am

          no worries.

          Reply
  4. Colin

    February 28, 2020 at 7:14 am

    Hi guys,

    I can’t seem to get my Horizontal axis to display the days of the week. Instead, I get the incremental amounts generated by RAND. When first creating the graph should I be selecting all three columns: days of the week, RAND formula multiplied by spread and tips? Also, on the tab with the Excel table what role does the first “Column1” play? When I right-click and select data on the scatter graph, it does not link back to the data source?

    Thanks,
    Colin

    Reply
    • Philip Treacy

      February 28, 2020 at 10:16 am

      Hi Colin,

      Have you examined the data series in the chart in my example workbook – on the Jitter sheet? That should show how the data is selected for plotting.

      You just need to select the 2 columns containing the day of the week (which is a number) and the jittered values. The jittered values are the result of putting the total bill value into the jitter formula.

      Select the data for each day separately and add as separate series to your chart.

      On the tips sheet, that is the original dataset. I’ve extracted the data I need, which is the data you see on the Jitter sheet. Column1 is just the record/row number and was part of the original dataset.

      If you are still having issues, please start a topic on the forum and supply your workbook.

      Regards

      Phil

      Reply
  5. jim

    February 28, 2020 at 12:39 am

    “…like pressing F9 to watch the points dance”

    yeah, we’ve all been there…

    Reply
    • Philip Treacy

      February 28, 2020 at 10:08 am

      🙂

      Reply
  6. Hans

    February 27, 2020 at 5:13 pm

    You guys are great 🙂
    Nice one, like I quiet often repeat in my postings when helping persons with their VBA, the limit lies in your imagination.
    Great job.

    “IT” Always crosses you path …

    Reply
    • Philip Treacy

      February 28, 2020 at 10:09 am

      Thanks Hans

      Reply
  7. Steven T.

    February 27, 2020 at 5:20 am

    Is it possible to create the jitters from in color groups like a bubble chart?
    For example: Is it possible to populate the jitters in Thursday in 3 color groups?
    1) Values less than 10% is Red.,
    2) Values between 10% and 20% is Yellow..
    3) And anything greater than 20% is Green colored jitters.
    Please let me know if that is doable?
    Thanks / Steve

    Reply
    • Philip Treacy

      February 27, 2020 at 9:14 am

      Hi Steve,

      Yes you can do this by adding the points as 3 different data series. If you have some data you want to do this with, please post a topic on the forum and attach the workbook.

      Regards

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.