Locked lesson.
About this lesson
Creating your first PivotTable is the first step to unlocking serious Excel power in data optimization.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
2016, 2019/365.
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Creating your first PivotTable.xlsx127.9 KB Creating your first PivotTable - Completed.xlsx
173.4 KB
Quick reference
Topic
Creating your first PivotTable.
Where/when to use the technique
When you’d like to take the first step to unlocking serious Excel power in data optimization.
Instructions
Select a cell inside your data table
- Go to the Insert tab --> PivotTable
- Choose to land your PivotTable on an existing worksheet
- Select K2 and click OK
Lay out the data fields on the PivotTable
- Drag Class to Rows
- Drag Category to Rows under Class
- Drag Units to Values
- Drag POSChitHour to columns
- Move Class to Columns
- Move it back
Refreshing data
- Update cell E15 to 1000 (units of coffee sold in the 10 o’clock hour)
- Look at the PivotTable (N19 is where we should see this)
- No change?
- Right click the Pivot and choose “Refresh”
Activating the field list
- Click outside the PivotTable (field list goes away)
- Click inside the PivotTable (field list comes back)
- Dismiss the field list by clicking the x in the top right corner
- Try clicking out and in again (field list stays hidden)
- Right click the PivotTable and choose “Show Field List”
Key points to remember
- PivotTables are easy to build.
- PivotTables are very quick to update.
- PivotTables are not “Live” (you must refresh your data).
- You can reactivate a dismissed field list by right clicking the PivotTable.
- 00:00 In this video, we're gonna show you how to create your very first pivot table,
- 00:06 and you're very quickly gonna see why this was one of my favorite features in Excel.
- 00:12 These things are amazing.
- 00:14 Check out the table of data that I've got sitting here.
- 00:17 This thing overall, if I press End+Down, You'll see that it's 2,500 rows of data,
- 00:22 and I want to get some key statistics out of this thing in a hurry.
- 00:26 So how do I go about doing that?
- 00:27 Do I write formulas?
- 00:28 Heck no, I'm gonna use this amazing tool called pivot tables.
- 00:32 Go back up to the top and you'll see that we've got some nice headers across
- 00:35 the top here, we've got a whole bunch of repeating data
- 00:38 broken down into different classes and categories, and stuff like that.
- 00:41 And now what we're gonna do is we're gonna perform some magic.
- 00:45 I'm gonna click somewhere in my table of data, anywhere.
- 00:48 And then I'm gonna go to the Insert tab and click Pivot Table.
- 00:52 When I do that you'll see that it's automatically selected my range of data.
- 00:56 And it's filled it in here for me.
- 00:57 I can see the marching ants around it.
- 00:59 And I get the option to place a new or existing worksheet.
- 01:02 I'm gonna choose New Worksheet for right now and say OK.
- 01:06 And this gives me a pivot table frame.
- 01:09 It also gives me the field well over on the right hand side here and all of these
- 01:13 headers that have come in, these were the headers on the previous worksheet.
- 01:16 You can see all the headers here have been pulled in as fields into my pivot table
- 01:21 field list.
- 01:22 Now what can I actually go about doing with this thing?
- 01:25 Well as it turns out, an awful lot.
- 01:27 What I'm going to do first is I'm going to take this class field and
- 01:31 I'm going to drag this guy down into the rose area a let it go.
- 01:35 And what you'll see is that my pivot table immediately populates with
- 01:38 every unique value that was in that particular column.
- 01:41 There's only two, Alcohol and Food.
- 01:43 Well that's kinda neat, 2,500 rows summarized just like that.
- 01:47 So let's see if we can break this down a little bit further now.
- 01:50 Let's grab these values in the Category field and we'll drag those and
- 01:54 release them right underneath the Class.
- 01:56 And you'll see that it actually nests these guys right underneath and
- 01:59 breaks down all the alcohol by the different subcategories of alcohol, and
- 02:02 all the food by the subcategories of food.
- 02:05 How cool is that?
- 02:06 Again, 2,500 rows.
- 02:08 You know what? What would be interesting next is
- 02:10 to know a little bit more about the units.
- 02:11 How many of these did we sell?
- 02:13 Well it happens that our field list, actually our original data table,
- 02:16 had a listing of the units by day.
- 02:18 So let's drop these in here too.
- 02:19 We'll put these ones in the VALUES area since they are values.
- 02:22 And immediately, it summarized all the values in that field as well.
- 02:26 How cool is that?
- 02:27 1,260 units of alcohol sales broken down by the different categories for
- 02:31 this entire period.
- 02:33 Well what if I want to go a little bit even more granular?
- 02:35 I'd like to see what hour these particular things were sold by.
- 02:38 I'll grab POSChitHour.
- 02:40 That's the point of sale chit hour and drop it into the columns area.
- 02:44 And look at that broken down by every hour.
- 02:47 In the 8 o'clock hour we sold no alcohol but we sure sold food.
- 02:50 And in the 2200 hour, which is 10 o'clock at night hour,
- 02:53 we sold lots of alcohol and food which is kind of interesting as well.
- 02:57 Now, this is great because I've pulled all these values onto the table in a way they
- 03:01 make sense.
- 03:01 But what if I didn't know, or I got something messed up?
- 03:03 What if I decided I didn't like this?
- 03:05 Maybe I originally thought, well, it'd be better if I saw a class up above POS hour.
- 03:11 I could put it below by letting go here and
- 03:14 it would like this which doesn't look like it makes a lot of sense.
- 03:17 Let me drag it up a bit and drop it above point of sale chit hour, and
- 03:21 now we can see that my hours are broken down.
- 03:23 I've got all of my alcohol sales by hour for all the different ones, and
- 03:25 my food sales by hour.
- 03:26 So that's quite interesting.
- 03:28 But you notice the key point here is that I don't have to be afraid to play
- 03:31 with this data.
- 03:32 It's like a great big Rubik's cube of data.
- 03:34 I can just move stuff around as I decide and think it might work best.
- 03:38 If I don't want this on the pivot table, I'll say pull Class right off,
- 03:41 drag it back to the field well, let it go, and it goes off the pivot table.
- 03:46 Or if I did want it again, drag it back down and put it above Category.
- 03:49 So really easy to slice and dice the data, it's really important.
- 03:54 Now, couple of key things you wanna be aware of here.
- 03:57 What happens if you lose this field well?
- 04:01 If we click the X it will go away.
- 04:03 So that's nice because we can see whats going on here.
- 04:05 But if we need to bring it back ever we just right-click on it and
- 04:09 say, Show Field List.
- 04:10 It will always hide when we click outside the pivot table.
- 04:13 And it should always come back when we click inside the pivot table
- 04:16 until we're done.
- 04:18 Then we dismiss it.
- 04:19 But then again, the problem is we click outside, we click back in, it goes away,
- 04:22 so let's bring it back by Show Field List off the right-click.
- 04:26 The other thing that's really important to be aware of is that not all
- 04:29 data in a pivot table is considered live.
- 04:32 And here's what I mean.
- 04:32 If we go back to Sheet1 and let's take a look at coffee here.
- 04:37 And I'm gonna make the sales for this item, ridiculously large.
- 04:40 I'm gonna say 1,000 units of coffee in the 10 o'clock hour.
- 04:44 It's a food and beverage, nonalcoholic beverage type thing.
- 04:47 Let's go see what happens with our pivot table now.
- 04:52 Nothing.
- 04:54 Here's the 10 o'clock hour.
- 04:55 Here's our non-alcoholic beverage, it's still 161.
- 04:59 Pivot table data's not live, it has to streamed into the pivot cache.
- 05:02 The way we do that is we right-click and we say Refresh and then it refreshes
- 05:06 the data from the original table and now you can see that we have all those items.
- 05:10 So this is the beginning, the introduction, the very first pivot table
- 05:14 you've ever built and these things are amazingly powerful.
- 05:17 There's a lot more to learn but this is the basics and you see why it's so
- 05:20 important to learn these incredible tools.
Lesson notes are only available for subscribers.