Creating PivotTables

Quick reference

• 00:04 It's now time to look at one of Excel's most powerful tools, the pivot table.
• 00:09 And what's that?
• 00:11 Well, it's basically where we can pull our data into Excel and
• 00:14 treat it like a Rubik's Cube.
• 00:16 So we can quickly pivot and
• 00:17 change the way that we're looking at data to get different insights out of it.
• 00:21 The secret to starting with a pivot table is to have a good data source.
• 00:25 What makes a good data source?
• 00:27 You've heard this before.
• 00:28 A good header row, no blank rows or columns in the data.
• 00:31 And consistent data down the actual columns as far as types go.
• 00:35 The easiest way to ensure that, formatting the table.
• 00:38 So we'll take this data and we will quickly turn this into a nice table.
• 00:43 Notice it has headers, that's great.
• 00:45 And we'll go and rename this table immediately to something like Inventory.
• 00:51 Now, to create the pivot table itself, this is super easy.
• 00:55 We can click somewhere in our pivot table, go to the Insert tab, and
• 00:58 choose to insert a Pivot Table.
• 01:01 Notice that it picks up the table's name.
• 01:03 This is great because as your table expands,
• 01:05 all the new data will automatically be fold into it.
• 01:09 I'm gonna go places on the Existing Worksheet and
• 01:12 I'm gonna drop it right here in cell H5, and will save OK.
• 01:17 This creates the PivotTable frame.
• 01:19 And you'll notice that we have the ability over here to look
• 01:22 at all these different fields, which come from the headers of the actual table.
• 01:27 What I'm gonna do with this now is I'm gonna take sold by and
• 01:30 I'm gonna drag it down here and drop it in rows.
• 01:33 And you'll notice that there we go, we have a unique list of every single
• 01:36 salesperson, no matter whether this is 15 or 15,000 rows of data.
• 01:41 I'm going to put inventory item underneath my rows by just checking it right here and
• 01:45 you'll notice that it now shows every sales item for every sales person.
• 01:49 On my Values, I'm going to check the box next to Cost.
• 01:52 Because it's numeric, it automatically throws it into the Values area.
• 01:56 And then I'm gonna look at this and say,
• 01:58 I'm not sure I like the layout of this pivot table.
• 02:00 I think I would actually rather see my employees across the top so
• 02:05 I can see what the sales are by item, by employee.
• 02:08 So what I'm gonna do is I'm actually gonna take Sold By,
• 02:10 I'm just gonna left-click and drag it up here to Columns and
• 02:13 that re-pivots the table into a different format.
• 02:16 And this is the real strength of a pivot table,
• 02:18 is the ability to actually change things.
• 02:21 And then I realized, wait a minute, I don't really wanna see cost,
• 02:23 I actually wanna see the price.
• 02:25 So let's uncheck Cost or drag it out, whichever, and check Price.
• 02:30 And there we go.
• 02:31 Now we're actually showing the sales price.
• 02:33 So that's pretty good, now the pivot table is super useful for
• 02:37 all of the values that we can get out of it nice and quick.
• 02:39 It is way faster than writing a whole bunch of SUMIF formulas in order to make
• 02:43 this work.
• 02:44 But the pivot table does have one nuance that you need to be aware of.
• 02:48 What I'm gonna do is I'm just gonna dismiss the field list here.
• 02:51 Now, we can always bring this back anytime we want by right-clicking
• 02:54 on the pivot table saying show field list.
• 02:56 I just want to get it out of the way for
• 02:57 a minute because I want to be able to show you something really important.
• 03:01 Let's say that Fred's kitten that he sold here was a really important one.
• 03:04 It was a very, very lovable kitten, and it was worth $1,500,000.
• 03:09 You would think that when you change this that it would be pretty obvious for
• 03:14 our lovable kittens for Fred that it would actually update right away.
• 03:17 And plainly it did not.
• 03:19 And the reason being is because the pivot table
• 03:21 actually uses something called the pivot cache in its process.
• 03:25 What that means is streams all of the data when you create it Into the pivot cache
• 03:29 and it pre-calculates everything that you could want.
• 03:33 And that's why, when you're dragging and
• 03:34 dropping fields, where there's 15 rows or 15,000 rows, it's just as fast.
• 03:39 The problem is, it's disconnected from the calculation chain.
• 03:42 So in order to update this, we need to remember that pivot tables are not live.
• 03:47 And to do this, we go right-click and choose Refresh.
• 03:49 And now you'll see that our 1.5 million comes in.
• 03:52 There's also another way to do this, too.
• 03:55 If I go back and say 45,
• 03:56 hit Enter, we'll change it back, it obviously hasn't updated here.
• 04:01 My personal preferred way to update things is to actually go to the Data tab.
• 04:05 And click the Refresh All button, and that's because I can create multiple pivot
• 04:10 tables off of multiple tables, and this one will refresh everybody all at once.
• 04:15 So the pivot table is hugely useful It allows you to quickly go and
• 04:21 reshape your data.
• 04:22 If you don't like the format you just drag fields around to different places or
• 04:25 drag them off or uncheck them and away you go.
• 04:27 You've got lots of different way to report from a small of a large data set.

Lesson notes are only available for subscribers.