Creating PivotTables

Quick reference

• 00:04 In this video we're going to introduce you to one of Excel's most powerful tools,
• 00:08 the pivot table and there is just no faster way to summarize data than
• 00:12 using this tool without writing a single formula in the process.
• 00:15 Now here's the deal,
• 00:16 in order to start with a pivot table though your data needs to be in good form.
• 00:20 What does that mean?
• 00:22 It means it needs a nice descriptive header row,
• 00:25 no blank row between the header and the data.
• 00:28 The data should be consistent all the way down the columns of the table.
• 00:31 So all numbers, all text, all dates, and no blank rows, no blank columns.
• 00:37 And once we have that, we can click any cell inside and
• 00:40 choose insert pivot table to get started.
• 00:43 But before we do, I want to call out something very, very important.
• 00:46 If you look in this table range area, this is pulling the source data and
• 00:49 you're going to see there's dollar signs in here.
• 00:51 There's four of them, that is Excel throwing money at you to
• 00:55 get your attention to know that you're about to do something very dangerous.
• 00:59 You do not want to do this.
• 01:00 If you see money, stop, okay?
• 01:04 We're going to hit cancel and
• 01:05 what we're going to do is we're going to turn this into a proper
• 01:09 official Excel table because that is a better choice of data for a pivot table.
• 01:13 So I'm going to just change this into a nice blue style here, that looks good and
• 01:18 of course I'm going to go and name my table right away to call it sales.
• 01:21 Now, I'm going to click somewhere inside here, insert any pivot table and
• 01:27 notice the difference, it now calls it sales.
• 01:30 And this is just going to make it a lot easier to make sure that your data updates
• 01:34 when you add new columns or new rows in hard coded ranges,
• 01:37 you actually have to maintain that data source when using a table it just happens.
• 01:42 Now the next option you've got, we can use to or
• 01:44 put our pivot table on a new worksheet or an existing worksheet.
• 01:48 I'm going to put this on an existing worksheet just so
• 01:50 we can see exactly what's going on here.
• 01:52 And before I click OK I just want to call out the last little thing here about this
• 01:56 add to the data model.
• 01:57 Unless you have taken training in a technology called Power Pivot,
• 02:01 I do not recommend that you check this button,
• 02:04 it actually adds a whole level of complication to your pivot tables.
• 02:07 So let's drop this in i five, we're going to go say, OK and
• 02:11 we get this pivot table frame.
• 02:13 Now, here's the cool thing, what we can do now is we
• 02:15 can actually write a bunch of formulas by just dragging and dropping fields.
• 02:19 So what I'm going to start with is I'm going to take the sold by and
• 02:23 I'm going to drag it down into the rows area.
• 02:26 And what you'll see is it gives me a unique list of all of my salespeople then
• 02:30 from the sold by call so we've got a Fred, a Jane, a John, and a Mary.
• 02:34 Now, the next thing I'm going to do is I'm going to grab price and
• 02:37 I'm going to drop it into the values area.
• 02:40 And it now basically does a great big sum if to go and
• 02:43 sum all of the prices if the name is equal to what you see on the left hand side.
• 02:47 And yet there are no formulas here at all so this is pretty cool.
• 02:52 If I want cost, I could go and drop that into the pivot table as well.
• 02:56 If I don't like the order, I can drag it around and
• 03:00 put it in a different order so now we have sum of cost and then sum of price.
• 03:04 And if I don't like this I can actually take it off and
• 03:08 throw it back in the field as well.
• 03:10 Now, let me try this, I'm going to move sold by into columns and
• 03:15 I'm going to move inventory item down into rows.
• 03:19 And this is the beauty of a pivot table as it allows you to very quickly try
• 03:23 different looks and different views of how you want to see things.
• 03:28 There is one thing you need to know about your pivot table though.
• 03:31 Let's change the price of this lovable kitten here to something extremely
• 03:35 obvious, $9,000, it's a very expensive kitten.
• 03:39 And what you're going to notice here is when we come down here and
• 03:42 we look for our lovable kitten, what we should see is that our lovable kitten for
• 03:47 Fred should have at least $9,000 and it's only got 22 or $225 in there.
• 03:51 Why is that out of sync?
• 03:53 Well, the answer is that pivot tables cache their data.
• 03:56 They're not live against your data source.
• 03:58 So this is the important thing to recognize about a pivot table is to make
• 04:01 sure it's up to date.
• 04:02 It's either right click and choose refresh on it,
• 04:05 that will now update everything there from the data source.
• 04:09 Or if we go back and set this level will kitten back to $45, we can also go up to
• 04:14 the data tab and choose refresh all and that will refresh all pivot caches
• 04:19 in the workbook to make sure that your pivot tables are up to date.
• 04:23 Pivot tables are amazing for very quickly summarizing things and dragging and
• 04:27 dropping to get the view that you want.
• 04:29 The only big key there remember, put it against the table to start with and
• 04:33 then make sure that you don't forget to
• 04:36 hit that refresh button when your data changes.

Lesson notes are only available for subscribers.