Reading Time: minutes remaining

XY Scatter Charts (a.k.a. Scatter Plots)

Jason Morrell

by Jason Morrell 
March 15, 2020

Select Dynamic field

XY Scatter Charts (a.k.a. Scatter Plots) show the spread and disparity of a large dataset and provide a clearer view of the trend(s) and any anomalies that may exist.

In this post, I'll show you how to set up an XY Scatter Chart and how to custonmise it to your requirements.

1. How to create an X Y Scatter Chart

Consider this data. It shows 18 years of monthly rainfall data in Timbuctoo.

The data for our XY Scatter Chart

If you tried to plot this data on a conventional column chart or line chart, it would be very congested. Hence the benefits of the XY Scatter Chart.

To create an X Y (Scatter) chart from this data:

1.  Select a single cell in your data to select the entire range or select the specific data range you wish to use.

2.  Click the Insert tab then the Insert Scatter (X, Y) or Bubble Chart icon in the Charts group, or …

Click the Recommended Charts icon | All Charts tab | X Y (Scatter)

3.  Select one of the Scatter options. You can change this later using the Change Chart Type icon on the Chart Tools Design tab.

There are 5 scatter chart types to choose from. These are available on the Insert Scatter (X, Y) or Bubble Chart icons on teh Insert ribbon or by going to the Recommended Charts tab or All Charts tab

The default Scatter chart for the example above will put …:

1.  Years in the Legend

2.  Monthly rainfall figures on the vertical axis.

3.  Month number on the horizontal axis (if there are labels in the data, they will be ignored on the axis)

This is the initial Scatter chart that Excel provides:

This is the initial Scatter Chart that Excel provides

It is always worth checking out the Chart Styles on the Chart Tools Design ribbon for different configurations and colours.

The Chart Styles gallery on theh Chart Tools Design ribbon offers a variety of different configurations

2. Add extra gridlines to help read the data

To add extra gridlines:

1.  Click the PLUS icon in chart icon controls on the top-right edge of the chart (or click Add Chart Element on the left of the Chart Tools Design ribbon)

2.  Click the Gridlines option arrow, then tick any Major and Minor gridlines for the vertical and horizontal axis that you require.

3. Add the missing numbers on the bottom axis

To add a full complement of numbers on the bottom axis:

1.  Click the PLUS icon in the chart icon controls on the top-right edge of the chart (or click Add Chart Element on the left of the Chart Tools Design ribbon)

2.  Click the Axes option arrow, then choose More options.

3.  On the sidebar set Minimum and Maximum Bounds and the Major and Minor Units.

To add missing numbers to the horizontal axis, go to the Axis Options then More Options to set the min and max Bounds and the major and minor units

3. Switch on and customise the vertical axis title

To switch on the vertical axis title/label:

1.  Click the PLUS icon in the chart icon controls on the top-right edge of the chart (or click Add Chart Element on the left of the Chart Tools Design ribbon)

2.  Click the Axes Titles option arrow, then tick the Primary Vertical box.

3.  Click inside the vertical label box then overwrite the default text with your own.

After making the changes this is how the Scatter Chart looks

4. Filter the data

To declutter a messy or overpopulated Scatter chart:

1.  Click the FILTER icon on the top-right edge of the chart and untick items you wish to exclude.

2.  Untick any data that you want to temporarily hide. You can always add this back in later.

3.  Click the Apply button at the bottom.

5. Change the month number to a month name

Yeah, I’d like to able to do this too. Unfortunately, it cannot be done (currently). But there is a way to fudge it.

1.  Create a line chart which allows you to display the month names as axis labels,

2.  Add line markers then and switch off the lines.

The result ends up looking like a scatter chart but I acknowledge it’s by no means a perfect solution..

5. Summary

I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.

Have a fantastic day.

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

POPULAR EXCEL POSTS

Radar charts in Excel


Sunburst charts in Excel


Box plots (Box & Whisker charts) in Excel


Waterfall charts in Excel


Funnel charts in Excel


VLOOKUP de-mystified


How to fix broken formulas fast


3 Ways To Insert a Degree Symbol In Excel


11 little-known sorting secrets (wait until you see 5, 6 and 9)

«« SEE ALL EXCEL POSTS »»

more from

Office Mastery logo
Read More
5 Excel Functions for Beginners: Simplify Your Formulas and Release the Magic
Read More
Excel Formulas for Beginners: Bring Your Spreadsheet to Life

JOIN THE DISCUSSION

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>