- 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.
Pivot Tables42.2 KB Pivot Tables - Completed
54.3 KB Creating PivotTables
41.6 KB
Quick reference
Topic
Creating, adjusting and modifying basic PivotTables in Excel.
When to use
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 a field that contains numeric values from the field list into the VALUES area
- Drag a field that contains textual values from the field list into the ROWS area
Removing items from a PivotTable
- Drag the field you placed in the VALUES area back into the field list
- Drag a different field into the VALUES area
Modifying PivotTables
- Drag the field you placed in the ROWS area from ROWS to COLUMNS
- Right click the field in the values area, choose Value Field Settings, and choose Average
- Go back to the source data table and change any field that has a value in it
- Return to the PivotTable, right click it and choose Refresh
- 00:04 Now we're going to talk about one of the coolest features that exists inside Excel; the PivotTable.
- 00:08 I'm not going to tell you a whole bunch of stuff about what PivotTables do because
- 00:11 it's a heck of a lot easier to actually show you. But before we get
- 00:14 to that we need to just understand some characteristics about the data that we have to work with that
- 00:18 actually build into good PivotTables. And basically
- 00:21 part of the wording in this thing is PivotTable so that kind of leads you to
- 00:26 believe that you need a table to start with. And effectively that's where we are here.
- 00:29 What I have is I have about 700 and some odd rows of data here and this all comes from a point of sale system from a restaurant.
- 00:36 You'll notice that it is actually formatted as a table. If you were to
- 00:39 scroll through the whole list you'll see that there's no blank rows or no blank columns
- 00:43 that are separating this data up. So it's a good solid contiguous block of data and that
- 00:47 is the number one ingredient for having a good data source for a PivotTable.
- 00:52 The second thing is to make sure that we have a good header row across the top that describes our columns really well.
- 01:00 Now that we have that, what we're going to do is we're going to create a PivotTable and we'll see why these are
- 01:03 so awesome. It's real easy we click somewhere inside our
- 01:07 list of data here. We go to Insert and we click PivotTable.
- 01:11 And then it says where's your range? And it's picked up our table for us, we're going to throw it on a new worksheet, we say OK.
- 01:18 What we get is we get a PivotTable frame and here's how PivotTables work:
- 01:23 What we can do is we can scroll down and say you know what, I'm going to grab Units here and
- 01:26 I'm going to drag it into the values field. I'm going to let it go.
- 01:30 And it's just summarized my 772 records I believe, to say that there's 782 units
- 01:35 total inside those 772 records just like that! And this is cool because
- 01:40 700 records, 700,000 records, it doesn't matter it goes almost as fast. Now
- 01:46 what else can we do with this? Well lets go grab the POSReportingGroupCode and throw that on the Rows field.
- 01:51 Oh okay now we get a breakdown between alcohol, food and non-alcoholic beverage.
- 01:56 We can further break that down by grabbing a category description and dragging that underneath the reporting groups.
- 02:02 And there we go now we can see what the breakdown of these particular items is between our alcohol, food or non-alcoholic beverage.
- 02:09 So it's pretty cool stuff. So this is the basics of how to create a PivotTable, first we find our data source we add it to or create a PivotTable and
- 02:16 we drag our fields on into the different areas on the PivotTable grid.
- 02:21 What about modifying it? Well, that's easy too. We just grab these guys here;
- 02:26 Sum of Units down in the bottom corner, I'm going to rip that right off the PivotTable and put it back to the Field list
- 02:30 and it takes it off the PivotTable. You can see the pivot updates.
- 02:33 We'll remove Category as well, drag that back up top. So now we know just what the values are we have in there.
- 02:39 And now let's go and throw the GrossAmount down in here. So now we're looking at dollars.
- 02:44 There we go, we've got $6000 worth of sales on this particular day.
- 02:49 What else could we do? Well you know what, we could take our POSReportingGroup and instead of having it down
- 02:53 the left hand side what if we did this? Lets drag it and put it into the Columns field.
- 02:58 So now the PivotTable reshapes and it's telling us that we still have the same
- 03:02 values they just kind of turned on their ear a little bit. What else can we do with this?
- 03:06 Well lets grab the Hours field. We'll drag that into Rows
- 03:11 and now I can actually see what my sales were by product type, by hour, which is pretty cool.
- 03:19 I can also go further with this. I don't have to look at just sums of values. I can actually go right click
- 03:25 say Value Field Settings
- 03:27 and I can go and choose to have an Average. So lets see what the average of the sale is.
- 03:33 Oh that's kind of ugly. I'm just going to go and switch in here real quick and go to Value Field Settings, I'm going to go into Number Formats
- 03:40 and I'm just going to change this to Accounting. Just so we get something consistent and say OK.
- 03:45 And here we go now we've got some sales. So we can see that
- 03:48 our average sale of alcohol is pretty low in the morning which you'd
- 03:51 expect because most people don't drink early in the morning. It generally stays the
- 03:55 same to around 6:00 at night and then we have a big spike. I guess people come in
- 03:58 for dinner and they want to have a beer or something that's what ends up happening there. That's kind of neat.
- 04:02 Our food sales also spike in the dinner hours compared to what
- 04:05 we have, except for it looks like we have breakfast at this particular restaurant.
- 04:09 Some neat things, you can see that you can actually get into here.
- 04:12 This is where PivotTables are so cool because you can drag these fields around from
- 04:15 one place to another and get them into the places that you want to show the data
- 04:20 from two dimensions and almost into three depending on how you need it.
- 04:25 But here's the trick with PivotTables, something really important.
- 04:28 When I go back to my original data here, I'm going to go to cell I4 and I'm going to go set this GrossAmount here to a ridiculous number: 1000
- 04:37 10,000 whatever it is. And you'll notice this is in the 11:00 hour.
- 04:42 And we also notice that its food. So when I go back to Sheet2 and I look at food in the
- 04:47 11:00 hour my average is still $8.42. Nothing's changed.
- 04:53 And this is where PivotTables get their incredible speed as they work on an optimized cache. And in order to actually get your PivotTable to show
- 05:01 the current numbers you have to refresh the data every time it changes. So if you see if I go right click
- 05:06 and say Refresh so I've right clicked on my PivotTable. Click Refresh and now my numbers
- 05:12 get updated. So your PivotTable is not always in sync with the source data,
- 05:17 you always have to refresh it to make sure it's working from the most live copy because that's where they get their speed. So
- 05:23 that in a nutshell is how to create and modify a basic PivotTable and why they are so amazingly cool!
Lesson notes are only available for subscribers.