GoSkills
Help Sign up Share
Back to course

Source Data Best Practices

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Setting up your data sets to ensure that they will be able to be consumed by a PivotTable.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Source Data Best Practices.xlsx
137.6 KB
Source Data Best Practices - Completed.xlsx
137.5 KB

Quick reference

Source Data Best Practices

Understanding best practices for PivotTable source data.

Where/when to use the technique

Setting up your data sets to ensure that they will be able to be consumed by a PivotTable.

Instructions

Data must be contiguous

  • Remove all blank rows
  • Remove all blank columns
  • Make sure there is a header row

Key points to remember

Data should be un-aggregated
  • PivotTables do the aggregation for use
  • Remove all subtotals
  • Remove all grand totals
Data should be consistent
  • Columns of numbers should contain only numbers
  • Columns of dates should contain only dates
  • Columns of text should contain only text
  • Only exception is the header row, which should be text
Choose good table headers
  • Data must have a header row
  • Make the header row descriptive
  • These headers will become the field names on your PivotTable
Login to download
  • 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.

Field Well Options
04m:24s
Using Excel Ranges
05m:28s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement