- 720p
- 540p
- 360p
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
Learn to create and modify basic PivotTables (for quickly summarizing and highlighting data).
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Creating PivotTables44.1 KB Creating PivotTables - Completed
51.8 KB
Quick reference
Creating Pivot Tables
Creating, adjusting and modifying basic Pivot Tables in Excel.
Where/when to use the technique
Pivot Tables are an amazing tool that can summarize, slice and dice data in a variety of formats. While they shine with large data sets, they are equally powerful with small data sets as well.
Instructions
Benefits of creating Pivot Tables
- Extremely quick data refresh
- Drag and drop interface to look at data in different ways
- Ease of use
Key Point to remember
- Pivot Tables are not live! You MUST refresh your data manually (but it’s easy to do)
Preparing your data to turn it into a Pivot Table
- Make certain that your data is in tabular format (preferably formatted as a table)
- Ensure your data has a clear header row
Creating Pivot Tables
- Select any cell in the data range
- Go to the Insert tab, choose Pivot Table and place it on a new worksheet
- Drag a numeric field into the VALUES area
- Drag fields containing text into the ROWS area as desired
Removing items from a Pivot Table
- Drag any field from the layout area back into the field list
Modifying Pivot Tables
- Drag a field from ROWS to COLUMNS
- Drag a field from COLUMNS to ROWS
- Click the arrow beside any field in the VALUES area, choose Value Field Settings, and choose Average
- Go back to the source data and update any cell to a new value
- Return to the Pivot Table, right click it and choose Refresh
- 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.