Source Data Best Practices

Quick reference

• 00:05 Before you start down your PivotTable journey,
• 00:08 it's really important to understand what makes a good source for PivotTable data.
• 00:14 PivotTables are pretty particular about what they want to
• 00:16 eat in order to serve you up the meal you're looking for.
• 00:20 The challenge that we've got is that a lot of people put their data into
• 00:23 a format that isn't really compliant for
• 00:25 this thing, and this is a really good example of what ends up happening.
• 00:29 We might see a set of data,
• 00:30 and it looks like a great big tabular set of data here, but people have gone in and
• 00:33 then they've put, say, a blank column in here to separate their data apart.
• 00:37 And they've decided that, you know what,
• 00:38 they've got some nice headers across the top.
• 00:41 But, they wanted to add just a little bit more spacing.
• 00:43 So they put a blank row in the middle of it.
• 00:46 Now, we can see this evidence in a problem by doing things like this.
• 00:50 If I press my end key, and press the down arrow, we go to the last non-blank cell.
• 00:56 Well here's the challenge, you can see there's a blank row in there and
• 01:00 then we continue on.
• 01:01 And as a matter of fact, if we keep scrolling down and we look,
• 01:05 there are lots of these things in here.
• 01:07 There's over 2,500 rows of data in this file, and
• 01:10 it looks like we have a blank line between every single set of days.
• 01:13 The challenge for this, if I go back up to the top,
• 01:17 is if I click somewhere in my data, and I say insert PivotTable,
• 01:22 notice the range that's being picked up?
• 01:25 It is A3 to C36.
• 01:26 We've got 33 rows of data.
• 01:28 That's our first January 1st records.
• 01:30 And that's it. We don't even have the headers,
• 01:32 so this is bad news for our data.
• 01:35 So the key things that we wanna remember when we're trying to serve up a good pivot
• 01:39 table source, is we want a contiguous set of data.
• 01:43 And what that means is something that starts in the top left and
• 01:46 goes all the way to the bottom right with no blank rows, no blank columns at all.
• 01:51 So, the fastest way to fix this data.
• 01:53 How do we do it?
• 01:54 Here's what I'll do.
• 01:55 I'll press Ctrl + End and that should take me down to the bottom
• 02:00 right hand corner that has data in my entire worksheet.
• 02:04 If not, I could always scroll and find it, but this will work.
• 02:07 Now, the next thing I'm gonna do is I'm gonna select all the data in
• 02:11 the worksheet here.
• 02:12 So what I'm gonna do is I'm gonna hold down my Shift key,
• 02:15 I'm gonna press Ctrl + Home while still holding down my Shift key.
• 02:19 Now I can let them all go.
• 02:20 And that's gonna select everything all the way up to the top left hand corner.
• 02:24 Again, I could do this manually with the mouse dragging,
• 02:27 but this is the fastest way.
• 02:29 The next thing that I'm gonna do is I'm going to go to my Home tab.
• 02:33 And I'm gonna choose to format my data set as a table.
• 02:36 And I can pick any color I like.
• 02:39 Now you'll notice that it's got the whole range that I selected up through 2596.
• 02:44 It doesn't' know that I have headers, so I'm going to tell it that I do.
• 02:46 And we'll say, OK.
• 02:49 Now, it makes a little bit of a mess from this.
• 02:51 I can double click on this row and bring this back up.
• 02:54 But, the key pieces that I want to be aware of here is that we
• 02:57 have some good column headers across the top.
• 03:00 But this one just says Column 1, and if I look at the filter arrows, it is blanks.
• 03:07 I don't need this column, so I'm just gonna delete it.
• 03:10 Don't need it at all.
• 03:11 In addition, I've also got a few different things in this particular area.
• 03:16 I've got a bunch of numbers under January.
• 03:18 Which is great, but I also have blanks here as well.
• 03:22 What I like to do with this is, I'll sort this from oldest to newest.
• 03:26 And we can see that I'm sorta missing a little piece in this row of data.
• 03:29 Here we go, that's expanded.
• 03:31 If I go End and Down, you'll see that down here,
• 03:35 I've got a whole bunch of blanks left in my table.
• 03:37 And I can see my table's still expanding, or
• 03:39 expanded, because of all the formatting here.
• 03:42 So I'm just gonna go back and I'm going to delete all these blank rows.
• 03:47 Right-click and Delete.
• 03:50 Now I have no blank rows.
• 03:52 I can see into my tables here.
• 03:54 I have no blank rows, and no blank columns in my data.
• 03:57 This is a much better data set than what I had beforehand,
• 04:02 so that will work quite nicely for me.
• 04:06 And I'm ready now to go and actually build my pivot table off of this.
• 04:11 A couple of other things that I wanna make sure that you're aware of is that pivot
• 04:14 table's whole point is to do aggregation.
• 04:16 So if there are any subtotals or grand totals in your table, get rid of them.
• 04:20 You don't need them because your pivot table will do that for you as well.
• 04:24 Your data in your columns should be consistent.
• 04:27 If you've got dates down this column, make it dates all the way down.
• 04:30 If it's numbers in this column, make it numbers all the way down.
• 04:32 If it's text, like this one is, we can see by the green indicator here, or
• 04:37 text here, these should generally be text all the way down.
• 04:40 Okay?
• 04:40 That's really important as well, consistent columns.
• 04:43 The only exception to that rule, is your header row.
• 04:46 You can see I've got numbers all the way down here,
• 04:48 but I do have text for my header row.
• 04:50 And that's because these guys here show up in our pivot table when we say,
• 04:55 insert PivotTable.
• 04:58 You'll now notice because I've got a table, my main range is selected here.
• 05:01 We'll say new worksheet.
• 05:03 Here's all my good headers, and we could now build the PivotTable that we've always
• 05:07 dreamed about because our data was in a good shape to do it.

Lesson notes are only available for subscribers.