Locked lesson.
About this lesson
This chart type can be useful for identifying clusters and outliers.
Exercise files
Download this lesson’s related exercise files.
Scatter Plots.xlsx53.5 KB Scatter Plots - Completed.xlsx
53.8 KB
Quick reference
Scatter Plots
Working with Scatter plots
When to use
This chart type can be useful for identifying clusters and outliers
Instructions
Reminder of Charting Goals
- Every chart should tell a story, quickly and effectively
- Extra chart elements create noise, and get in the way of the story
- It is a best practice to remove as much excess ink (noise) as you can
Creating a Scatter plot
- You must have data with two columns of values in order to plot in a scatter plot
- Go to Insert --> Charts -->Scatter Plot and select the Scatter Plot icon
Enhancing the Chart context
- Reformat your source data number format and it will carry through to the chart
- To link a title to a cell, select the chart title, press the equals key, click on a cell and press Enter
Enhancing the Axes
- Right click an Axis and go to Axis Options in order to change the start and end points of the axis
- Add labels to your axes by going to Chart Tools --> Design --> Add Element --> Axis Titles
Hints & tips
- Right clicking any data series will allow you to add Trend Lines to the chart
- 00:05 We're now going to look at scatter plots, which are really good for
- 00:09 showing trends and outliers in your data.
- 00:11 So the first thing I'm going to do is I'm going to grab this big table of data here,
- 00:16 I'm gonna say Insert, Scatter plot.
- 00:18 And here we go, it looks like this, which unfortunately is not exactly what I want,
- 00:22 as a matter of fact, it's not at all what I want.
- 00:24 Because this is plotting by transaction ID number down at the bottom.
- 00:28 And something on the left hand side, which isn't really helping me out, so
- 00:32 I'm going to go and say, you know what?
- 00:34 Let's try this again, we're gonna delete this chart,
- 00:37 because what I'm specifically interested in is sales dollars by hour.
- 00:41 So here's what I'm going to do, I'm going to hold down my shift key,
- 00:44 press end, down arrow, I'm still holding down shift.
- 00:47 I'm gonna arrow across once so
- 00:49 that I've selected my big set of data for hours and sales dollars.
- 00:54 And what I'm going to do now is I'm going to try again with a Scatter plot, so
- 00:58 here we go, and it gives me something like this.
- 01:01 So this is almost, I guess you'd call this a busy hour analysis.
- 01:05 We do need to make some formatting changes to this, though, let's go and
- 01:08 crank this axis down to be a little bit different here.
- 01:11 I don't really need, if the store doesn't open until eight o'clock or
- 01:14 nine o'clock in the morning.
- 01:16 Let's make this 8 o'clock, and here we go, I'll hit tab.
- 01:20 It looks like the store closes at 6 PM, which is 1800 hours, so let's go and
- 01:25 knock this one down to 19.
- 01:27 This will allow us to actually just shorten things down a little bit more.
- 01:32 What you can see here is you can see the individual sales trends on dollars
- 01:36 transactions by hour.
- 01:37 For each of the things that we've got here, which is kind of cool,
- 01:41 because every transaction is plotted individually.
- 01:44 So I can see that at the 12 o'clock hour, we actually sell a pretty high,
- 01:48 a lot of sales in the top area and less in this lower area.
- 01:52 And yet, the people that come in in the morning have a higher propensity to buy
- 01:56 less items, which is interesting.
- 01:57 Likewise, later on in the day, it looks like we have an awful lot of transactions.
- 02:01 But they certainly aren't the dollar value of what's happening earlier in the day,
- 02:06 which is kind of an interesting piece.
- 02:08 So, if I wanted to also go and say, well, let's go right click and
- 02:11 say, Add a Trendline to this.
- 02:14 Maybe I can get an interesting thing that tells me, hey, look,
- 02:17 the sales generally go down over the day, which makes some sense.
- 02:20 So, if you're working with these kind of charts, you know what you're looking for
- 02:23 with your trendlines.
- 02:24 And I'll leave that to you, but
- 02:25 you can see that there's lots of different options there around to play with.
- 02:29 But this is to say, it's kind of a cool analysis to get a little time break as to
- 02:33 when things are actually happening.
- 02:35 So let's go look at another series of data, we're going to go over and
- 02:39 take a look at Sheet2.
- 02:40 I'm going to dismiss this task pane,
- 02:42 I find these things hang around far too much for my liking.
- 02:45 But you'll notice over here that we have an official Excel table which is called
- 02:49 Loyalty Spend.
- 02:50 Which is actually tracking the total dollars spent for
- 02:53 each of the customers through 2017, in this case.
- 02:56 So we have a couple of customers that were 16 years old on our loyalty program.
- 03:00 A couple of 17, 18, 19, 20, and so on, and this is how much they spent in total.
- 03:05 So what we'd like to identify is,
- 03:07 can we figure out what the average spent is by age, who's our prime target market?
- 03:12 So because this is a table, I'm just going to come and say Insert,
- 03:16 I've selected just a single cell, it'll expand to pick up the entire range.
- 03:20 And you'll notice that it gives us a nice scatter plot that shows us what
- 03:23 the customer spend looks like.
- 03:25 This is kind of an odd disbursement, but that's okay,
- 03:27 we'll play around with this for a second.
- 03:28 A couple of things that I might want for my chart, remember,
- 03:32 we want this to be self-explanatory.
- 03:34 If I put this in front of someone right now, they'd be like,
- 03:36 what is the 3,500 versus the 30, what's this all about?
- 03:40 So it's important, at this point, to go and add the chart elements that we need.
- 03:44 Things like maybe an Axis Title, for Primary Horizontal.
- 03:48 Something where we could grab this and say,
- 03:52 you know what, this is customer age in years.
- 03:56 We may want to go and grab this guy over here and add a chart element as well for
- 04:00 an Axis Title for Primary Horizontal.
- 04:02 That says, if I press Ctrl+A,
- 04:06 I can highlight everything, Annual Spend in $.
- 04:12 And then we could go and also put a new title on the top of this one here
- 04:17 that says, Loyalty Program Evaluation, or something.
- 04:22 Now, that makes the chart look a little bit nicer,
- 04:26 let's go also right-click format the axis.
- 04:28 We don't take anybody in our loyalty program unless they're at least 16
- 04:32 years old.
- 04:32 So we're going to drop this down to say, you've got to be 15 minimum, there we are.
- 04:37 And it looks like the largest person, or
- 04:40 largest age that we have in here is somewhere around 75, 76.
- 04:43 If I mouse over the data points so I can see those, so
- 04:46 I think I'm just going to drop this guy back to 80.
- 04:50 And at this point, we get a nice zoomed in piece here,
- 04:53 I could even also go back and drop this down.
- 04:57 But maybe I want the chart to automatically expand
- 04:59 if people start spending more.
- 05:01 The key thing that I'm really interested in, is what's the general trend here,
- 05:04 cuz with all these data points, it's kinda hard to see.
- 05:06 It looks like 35, we've got some big spenders in here.
- 05:09 And as we go down, it looks like the older people are getting here, they spend a lot,
- 05:13 or many times.
- 05:14 But they spend little amounts, maybe buying presents for grandchildren or
- 05:18 stuff like that.
- 05:19 And it looks like we've got a lot of activity from 15 to 35, maybe this is
- 05:22 where people are sort of building up and being really active outdoors.
- 05:26 But wouldn't it be nice to see a trend of our average spent?
- 05:28 So we can right click on this guy here and we can Add a Trendline.
- 05:32 And that trendline looks pretty straight, so
- 05:35 I'm not really sure that's what I'm looking for.
- 05:37 What if we went to an exponential, or a logarithmic, or we went to polynomial?
- 05:43 And this is probably the one that makes the most sense, where we
- 05:46 can actually see a bend in transaction history, but it's hard to read.
- 05:50 So, could I do something better?
- 05:52 Well, why not?
- 05:53 Let's go grab this, we'll make this a solid line,
- 05:56 we'll change it to orange, which is a default.
- 05:59 And I'm also gonna set the width up so
- 06:01 that it's actually much easier to actually read it.
- 06:05 So now I can actually see what the trend is as well, so, again,
- 06:08 it's another way of playing around with this.
- 06:10 And if you're working with these things.
- 06:11 You should really work through and
- 06:12 make sure you understand the science of the visuals that you're building here.
- 06:16 I wouldn't use this in your own dashboard, for example.
- 06:18 But it gives you an idea as to some of the things that we can actually do with
- 06:21 our charts.
Lesson notes are only available for subscribers.