Creating PivotTables

Quick reference

• 00:04 We're now gonna look at pivot tables and these things are fantastic.
• 00:07 These are some of the best features inside Excel.
• 00:10 What a pivot table is,
• 00:12 is it's the ability to turn your data almost into a Rubik's cube.
• 00:16 So you can look at it in different ways from the same source.
• 00:20 In order to work with them though, our data does have to
• 00:23 have some specific characteristics and be in a specific shape.
• 00:26 You've heard this kind of stuff before, but it needs a good header row,
• 00:29 it needs to have no blank rows or columns anywhere in the data.
• 00:33 So the ideal format honestly, for a tabular dataset like this, is a table.
• 00:38 So I'm gonna grab this one and
• 00:39 I'm gonna flip this directly into a table right off the bat.
• 00:43 We'll go in, select our table boundaries, we'll say okay.
• 00:46 And we'll quickly go and rename our table to something like inventory.
• 00:53 Now, to create a pivot table, it's actually very, very easy.
• 00:58 What you're gonna do is go to the insert tab and the biggest button on the very
• 01:01 left hand side is pivot table, most important one on the tab.
• 01:04 We're gonna click to insert pivot table.
• 01:07 And it's going to say select your table or range.
• 01:09 It's already picked up the fact that we're in the inventory table.
• 01:13 I'm going to go put this on an existing worksheet.
• 01:16 Click the little rough edit box here, go to example pivot table and
• 01:19 I'm going to drop this into cell A3.
• 01:21 Click the box again and say okay.
• 01:26 And this will create us our pivot table ready to be used.
• 01:30 So here's how pivot tables work.
• 01:31 You can notice that we have in the field list on the right hand side here,
• 01:35 we have a whole bunch of field names.
• 01:38 These came from the headers of our original table.
• 01:42 So we can actually take these now and drag them into different places.
• 01:47 So if I wanted to go and say, let me put sold by on rows, you'll see that
• 01:52 it immediately creates a unique list of all of the items that were in that table.
• 01:57 I could grab my inventory item and slide them underneath my individual people.
• 02:03 I could even take the cost and put it into the values area.
• 02:08 If I don't like the way that this is lining up maybe I say you know what?
• 02:12 I'd rather see my sold by over here on columns.
• 02:14 So I'll just drag it up here and let go and now I can see my sales by salesperson.
• 02:19 Or rather my costs by salesperson.
• 02:21 I don't want costs, I'll just drag this back into the pivot table field list and
• 02:26 let it go and then I can maybe say,
• 02:29 maybe price would be a better metric to have on my pivot table.
• 02:34 So you can see they allow us an incredible ability to drag and
• 02:37 drop different things into different places.
• 02:40 If we don't like what we see, we just move it somewhere else, no big deal.
• 02:44 And look at the different views of the data we can get.
• 02:46 That's very, very cool.
• 02:48 But there are some things that we need to be a little bit careful with with
• 02:51 pivot tables.
• 02:51 One of the things about pivot tables is that they work very, very quickly.
• 02:56 Whether you're working with 150 rows, 15 rows or
• 02:59 150,000 rows, they calculate just about as fast.
• 03:04 And the reason being is because the data from our original table when we actually
• 03:08 create this is streamed into an optimized source called a pivot cache.
• 03:12 But the pivot cache has some issues and let me just show you here.
• 03:15 I'm going to set the price of this loveable kitten up,
• 03:18 this is a very special kitten.
• 03:20 We're going to go up to, to about $1.5 million.
• 03:22 We're going to go back to the example table and
• 03:27 you would think that this should be pretty darn obvious now, Fred's loveable kittens.
• 03:32 And look at Fred's lovable kittens.
• 03:35 There's still only $225 there, so you go well what the heck is going on?
• 03:39 Well, here's the challenge.
• 03:41 Because the pivot cache streams the data in and
• 03:44 is very optimized, Excel doesn't wanna be doing that every time the data is changed.
• 03:48 So instead it actually forces us to manually update the pivot cache.
• 03:52 We can do this by right clicking on our pivot table.
• 03:55 And saying refresh and
• 03:56 what you'll see is that now that money ends up coming in here.
• 04:01 This is a key thing to remember.
• 04:03 Pivot tables are fantastic for quickly slicing and dicing and getting alternate
• 04:07 views off of your data but they're not connected live to your data source.
• 04:12 So if we go back here and
• 04:13 we say, let me just put this back to a price of $45 and say enter.
• 04:19 Remember, when I go to that pivot table, it's not updated.
• 04:22 So again, we can right click and say refresh all or
• 04:25 we can actually go to the data tab and click refresh all and
• 04:29 this will refresh every pivot table in the workbook all in one shot.
• 04:33 So that makes life a little bit easier to work with there too because then you only
• 04:37 have to worry about doing it once.

Lesson notes are only available for subscribers.