Hey, Jason Morrell here.
If you're in the scientific field and you're dealing with a lot of data like hundreds or
thousands or tens of thousands of bits of data, you might find that the XY Scatter Chart
is the perfect tool for you.
In our example here, we have a table showing the monthly rainfall in Timbuctoo between
the years 2000 and 2017.
And just so you know, it's all made up.
This is a formatted table so I'm going to click any cell in the table.
Excel will work at the boundaries for me.
I'm then going to to go to the Insert tab here and find the XY Scatter options.
You got quite a few options in here.
After I've created the first one.
I'll explore some of these others with you.
I'm going to choose this first one as a starting point.
To make it easier to see.
I'm going to move this chart to its own sheet.
On the Chart Tools Design tab.
I'm going to choose Move Chart and specify New Sheet and let's just call it XY Scatter.
Across the bottom you have your years going from 2000 to 2017.
On the vertical axis, the y axis, you have your monthly rainfall in millimetres.
There's no label for that yet, but we'll add that in a minute.
This is a really good way to represent lots of data.
As you can see, you've got your periods down here.
Back on Sheet1 each month was numbered 1 through to 12, and the reason I didn't put January,
February, March in there is because the XY Scatter never displays that information.
It always just shows a single figure down the bottom here.
So in a minute it might be worth adding the extra numbers in here to represent all the
numbers 1 through to 12 for your 12 months.
If we look at the Scatter Chart, we can see that the monthly rainfall was highest in months
1 and 2 and 11 and 12, and it kind of dips towards the middle of the year there.
Each month the rainfall falls within a certain range and it's fairly consistent across all
the years.
What XY Scatter Charts are very good at spotting is anomalies.
That's data that doesn't really fit the mold.
So if we go back to Sheet 1 here, and let's just pick a value somewhere.
let's take very last cell, and make it something that's completely out of range, say, 250.
Let me go back to the XY Scatter.
You can see there's one anomaly here which stands out like a sore thumb from the rest
of the data because it really doesn't fit the pattern.
Maybe somebody has typed it in wrong.
Or maybe the weather did something crazy that month.
Whatever the reason, this is out of character and we can spot it straight away on the XY
Scatter.
So let's tidy up a few things here.
Down the bottom, let's add these extra months for the month numbers we don't currently have.
So what I'm going to do here is click the plus sign to show the Chart Elements.
I'm going to show the Gridlines.
I'm going to show the gridlines actually for Major and Minor for both directions and that
just fills in the details for us a little bit.
I'm then going to double click on the chart which displays the right hand side panel here,
and I'm going to change Chart Options to the Horizontal Value Axis.
That's the one down the bottom.
And under Axis Options here, I'm going to click this option here, which again says Axis
Options and open up Axis Options!
So Axis Options appears in three places there.
But in here you'll see the units, so Major and Minor.
Major is currently set to 2 and that's why it's only showing you two, four, six, and
eight.
I'm going to switch this over to 1.
Down the bottom you can see that you've got all the numbers now from 1 through to 12.
Also down the side here, we need a label to indicate what these numbers are representing,
the monthly rainfall in millimetres.
So to switch on the Vertical Axis Title (or label), I go to the plus sign here, go to
my Chart Elements and hover over Axis Titles here and choose the Primary Vertical Axis
Title.
This adds a little text box over here, and in here I can just wipe out what's there and
type 'Monthly Rainfall (millimetres)' like you normally would.
There's lots of other options for displaying your chart in different ways . There's different
effects, different layouts, that kind of stuff.
You may find one of these works better for you.
This one here, for example, it's making all the data points glow, so again you can see
quite clearly that anomaly that we highlighted before.
If we choose Change Chart Type, we're still on the XY Scatter category here, but at the
top here you got lots of different types to choose from, so I could, if I wanted to, join
up the dots and that kind of shows the trend of the rainfall year by year for each month.
So let's just do that as an example.
We'll just choose this.
Now.
It looks really messy, first time through, because we've got so much data on here and
again you can see that anomaly shooting through on that last one.
So, what you probably want to do here is to trim this down a bit.
You've go to your filters here.
So in here you'd probably choose less years.
So maybe you want to show the comparison every five years as an example.
You might de-select everything, then select, say, 2000, 2004, 2009, 2014 and just for the
sake of it, 2017.
And Apply.
And that way you get less of these lines.
It's less cluttered.
And you can see the trends quicker.
So that's XY Scatter Charts.
Very good when you've got lots and lots and lots of data.
Its main purpose is to consolidate all that data into a form that's readable, but also
to highlight the stuff that doesn't fit the pattern, the anomalies in your data.
Have fun.