Scatter Plots

Quick reference

• 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.