Source Data Best Practices

Quick reference

• 00:04 Before we dive head first into your pivot table journey, it's really helpful to
• 00:09 understand how do you have to serve up your data in order for
• 00:12 a pivot table to consume it.
• 00:14 They'll be able to make a beautiful dish out of what you have but
• 00:17 the problem is the ingredients have to be right to begin with.
• 00:20 Those ingredients follow some specific patterns.
• 00:22 We need to have a good header row.
• 00:25 And you can see for this data set, it looks like we do.
• 00:27 It's nice and descriptive, everything is good there.
• 00:30 We want consistent data types down the columns.
• 00:33 So if you'll notice here, we have a bunch of dates,
• 00:35 we want to make sure that they're all dates all the way down.
• 00:38 All numbers all the way down the Point Of Sale Chit Hour call.
• 00:41 All numbers formatted as text which is what's indicated by this little green
• 00:45 mark here down the Chit Number column, and text and values, and so forth.
• 00:49 The decimal points don't need to be lined up but
• 00:52 they do definitely need to be values all the way down the columns.
• 00:55 Other things that we don't want to see, totals and subtotals.
• 00:58 That's the job of the pivot table is to aggregate multiple rows of data to come up
• 01:02 with a single value.
• 01:03 Its job is to build your totals and subtotals for you.
• 01:05 So you don't want those in your underlying source data.
• 01:08 The other killer, blank rows and columns,
• 01:11 and you can see that we've actually got some of those here.
• 01:14 So how do we go about cleaning up a data set that looks like this?
• 01:18 Because here's the challenge here.
• 01:20 If I go and take a look at this cell and press End+down arrow,
• 01:23 you'll notice that we stop at row 36.
• 01:25 And yet, if I scroll a little forward, oh no blank rows, that's not good.
• 01:32 If I go and click somewhere in this data right now and say Insert > Pivot Table,
• 01:37 notice that it's picking up from a A3 to C36.
• 01:39 It's not picking up any of the rows after that.
• 01:42 It's not picking my header row.
• 01:44 And it's not picking any of the additional columns.
• 01:46 So this data set plainly needs a little bit of prepping before I can use it.
• 01:51 So how do I actually do that quickly?
• 01:53 Well, the first thing I'm going to do is press Ctrl+End.
• 01:55 Ctrl+End will take me down to the very last used cell on the spreadsheet which in
• 02:00 this case, the bottom right hand corner is J2596.
• 02:04 From here, I'll hold down my Shift key and press Ctrl+Home.
• 02:08 And that's going to select the entire contiguous block of cells from J2596,
• 02:13 all the way up to cell A1, blank columns and rows included.
• 02:18 Now the next thing I want to do here is to go to the Home tab, and
• 02:22 I'm going to choose Format as Table.
• 02:24 And I'm going to go and pick whatever color style I like.
• 02:27 I like this blue one here, so I'll just grab that.
• 02:30 And you'll notice it comes back and
• 02:31 says, this is the range of data that you want to use, does your table have headers?
• 02:35 Now of course mine does, so I'm going to say OK.
• 02:37 We'll check the box, say OK.
• 02:40 And to be fair, it's makes a bit of a mess out of the data but
• 02:43 I'm going to grab my first two rows here.
• 02:45 And I'm just going to go and double click on the line between 2 and 3.
• 02:48 And it now actually expands, shrinks them down and
• 02:51 expands them to what I need so that I can actually see things.
• 02:55 You'll notice as we go across the data set that my blank column over in D has picked
• 03:00 up and named Column 1.
• 03:01 And I can see you clicking on little filter arrow but it only contains blanks.
• 03:05 I don't need this one.
• 03:06 So we're going to right click, and we're going to choose to Delete it.
• 03:10 But what about these blank rows because there's a bunch of those.
• 03:12 The easiest way to clean up, click on the little filter arrow,
• 03:16 we'll use the one by Point Of Sale Chit Date.
• 03:18 We'll take a look at what's actually here,
• 03:20 we can see that we've got lots of different values are going on.
• 03:23 I'm going to sort by oldest to newest.
• 03:25 And then once they're sorted, I can now filter and
• 03:30 say let's uncheck 2013 and go and select just the Blanks.
• 03:35 This now shows me from row 2566 all the way down here to 2596.
• 03:39 I've got blank rows, I'm going to hold down Shift,
• 03:43 right click on the row number and delete those rows.
• 03:47 Now that that's done, I can come back and
• 03:50 clear the filter from the Chit Date, and away we go.
• 03:53 I've now got a nice contiguous set of data that's all ready to be
• 03:58 consumed by my pivot table.
• 04:00 So this is the key thing that you want to focus on when you are looking at
• 04:03 your data.
• 04:04 You want to make sure you've got that good header row, consistent data types down
• 04:08 the columns, no blank rows, no blank columns, no totals, no subtotals.
• 04:13 At this point, our data is now in a beautiful Excel table.
• 04:16 And it is perfectly set up for us to go and
• 04:19 build the most amazing pivot table we've ever seen.

Lesson notes are only available for subscribers.