Reading Time: minutes remaining

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

Jason Morrell

by Jason Morrell 
March 15, 2020

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 Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.

He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.

Purely for amusement he sometimes talks about himself in the third person.


more from

Office Mastery logo
Read More
How to Consolidate Data in Excel: 2 Easy Ways to use Excel’s Consolidation Tool
Read More
Excel Screen: A Beginners Tour


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