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
129.8 KB
Source Data Best Practices - Completed.xlsx
130 KB

Quick reference

Topic

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

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

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