About this lesson
Learn to create and modify basic PivotTables (for quickly summarizing and highlighting data).
Exercise files
Download this lesson’s related exercise files.
Creating PivotTables.xlsx33.6 KB Creating PivotTables - Completed.xlsx
41.9 KB
Quick reference
Topic
Creating PivotTables.
Description
Creating, adjusting and modifying basic PivotTables in Excel.
Where/when to use the technique
PivotTables 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 PivotTables
- Extremely quick data refresh
- Drag and drop interface to look at data in different ways
- Ease of use
Key Point to remember
- PivotTables are not live! You MUST refresh your data manually (but it’s easy to do)
Preparing your data to turn it into a PivotTable
- Make certain that your data is in tabular format (preferably formatted as a table)
- Ensure your data has a clear header row
Creating PivotTables
- Select any cell in the data range
- Go to the Insert tab, choose PivotTable and place it on a new worksheet
- Drag Price (or another field containing values) into the VALUES area
- Drag Inventory Item into the ROWS area, and Sold By below it
- Drag Date into the COLUMNS area
Removing items from a PivotTable
- Drag the Date field back into the field list
- Drag Price back into the field list
Modifying PivotTables
- Drag Sold By from ROWS to COLUMNS
- Drag Commission into the Values area
- Right click the values in the PivotTable, choose Value Field Settings, and choose Average
- Go back to the source data and update F4 to read 1000
- Return to the PivotTable, right click it and choose Refresh
- 00:04 In this video we're going to look at one of the most incredibly cool tools inside
- 00:07 Excel called the PivotTable.
- 00:09 Now these things have a bit of an intimidating name, but they shouldn't.
- 00:12 They are absolutely fantastic.
- 00:13 And they are the quickest way that we can actually go and summarize, and slice and
- 00:17 dice data into a variety of different formats.
- 00:20 It kind of allows us to create kind of a Rubik's Cube of our data,
- 00:23 which is really kind of neat.
- 00:25 So in this case here, we have a nice set of data here.
- 00:28 And some of the key characteristics that we always wanna work with when we're
- 00:31 working with a PivotTable, is we'd like to have our data in a good tabular format.
- 00:35 So again, that means we need a header row,
- 00:37 we need consistent types of data down the columns, no blank rows in between.
- 00:42 And the ideal way to actually work with your PivotTable is actually
- 00:46 have your data in a proper Excel table to begin with.
- 00:49 So I' gonna go and set up an Excel table right now.
- 00:52 And we'll just call this particular table here,
- 00:56 how about something like, pet inventory.
- 01:01 Now in order to create a PivotTable, what we're gonna do is we're gonna click inside
- 01:05 a cell, we're gonna go to Insert, and we're gonna choose PivotTable.
- 01:09 And it'll ask us to select the table or range.
- 01:12 You'll notice it's picked up the name of the table that I created already.
- 01:15 We could use an external data source like a database as well.
- 01:19 And I'm gonna say that I'm gonna put this on an existing worksheet, and
- 01:22 what I'm gonna do is I'm gonna click the little down in the corner here,
- 01:27 I'll go and drop it into the example PivotTable page, and we'll say OK.
- 01:32 And what you'll see is you get this PivotTable frame that ends up
- 01:35 showing up here.
- 01:37 Now, the nice piece about a PivotTable is we also get this little area over here
- 01:41 that is our PivotTable builder.
- 01:44 So what we can do is we can basically just drag an item from the field list up top,
- 01:50 and let it go in say the rows area.
- 01:52 And it will create us a unique list of every value that's in that PivotTable.
- 01:57 So it just cuts it down to a unique set.
- 02:00 So what if we said, well you know what maybe I don't want dates,
- 02:04 I'm gonna pull this back off the PivotTable and I'll let it go.
- 02:06 So if I decide that I don't like the way it's going, I can change it.
- 02:10 I could grab my inventory and pull it down onto rows, and there you go.
- 02:14 You can see all the unique inventory items that we started
- 02:17 off with in our original table.
- 02:19 I could pull say, sold by,
- 02:21 and I could start figuring out who sold the adorable kitty cats.
- 02:26 We've got a Fred, Jean, John and Mary have done so.
- 02:29 I could then go and take, let me see, how about price and
- 02:33 put it into the values area.
- 02:35 Well, we'll just drag that down in here and let go.
- 02:37 and you'll notice that it very quickly goes through and it actually sums up All
- 02:42 of the adorable kitty cats Fred has sold, and he sold $175 of adorable kitty cats.
- 02:48 Well, what if I don't want it to look exactly like this?
- 02:51 What if I wanted my Sold By to be sliced across the top here,
- 02:54 so I saw all of my inventory items down the left, and
- 02:57 all of my salespeople across the middle, and then a big table there.
- 03:00 Well no problem.
- 03:01 We'll just grab Sold By and we'll drag it over into the Columns area, and
- 03:06 there we go, that's exactly what we've got right now.
- 03:08 So this is the beautiful thing about PivotTable is it's very quick
- 03:13 to change the way that you look at data.
- 03:15 Don't like where it is?
- 03:17 Drag the field off the PivotTable and let it go.
- 03:19 Maybe we don't wanna look at price, we wanna look at say Commission.
- 03:22 So, I'll just get rid of price and I'll put commission on here.
- 03:25 Of course, if I wanted I could also even have price too.
- 03:28 I could drag that in and then have both pieces.
- 03:30 Okay, so here's the sum of commission and the sum of price.
- 03:33 Really, versatile tool this and
- 03:35 so quick, but there is one thing I'm just going to drag this back in here.
- 03:40 There's one thing you need to know about PivotTables.
- 03:43 I'm gonna dismiss the field list right and get it to go away.
- 03:46 And we can see that we've got our values here.
- 03:48 I'm just gonna go back over to the example data set for a second, and
- 03:52 I'm gonna change this commission up here to something ridiculous.
- 03:55 so we're gonna go to $100,000, so this is Fred's lovable kittens that he sold here.
- 04:01 And we'll go back to the PivotTable, and we'll look up lovable kittens and
- 04:06 here they are, and here's Fred and you can see that.
- 04:09 That sum of commission has not changed.
- 04:12 You go well why is that.
- 04:14 Well here's the thing, because PivotTables need to be so
- 04:17 quick they actually preprocess data when we use them.
- 04:20 So the data is not actually connected live to the data set.
- 04:24 In order to actually get these to work what we need to do is we need to
- 04:27 right click on our PivotTable.
- 04:28 And say refresh data, and at that point it will go and it will stream all of
- 04:33 the data from the original table into the pivot cache which serves up the PivotTable.
- 04:39 So again if we were to go back and saw well that's not right,
- 04:42 let's go put this back to the $1.35 that it was.
- 04:47 and we go back over to our PivotTable, you'll see that it hasn't changed,
- 04:51 again we need to right click and we need to say Refresh Data.
- 04:55 So this is one of the big key points to remember.
- 04:58 These are extremely quick to refresh data.
- 05:01 The drag and drop interface is awesome, and
- 05:03 lets you look at data in all kinds of different ways.
- 05:04 They're super easy to use, but they're not live.
- 05:08 You have to right-click and refresh, when you want your PivotTable to update.
Lesson notes are only available for subscribers.