Locked lesson.
About this lesson
The whole reason we get data in the first place is to turn it into information. And the best tool to quickly turn data into information? Excel's PivotTable! This module will review the basics of how to build and update a PivotTable.
Exercise files
Download this lesson’s related exercise files.
Creating Basic (non-Power) Pivot Tables.xlsx1.3 MB Creating Basic (non-Power) Pivot Tables - Completed.xlsx
1.8 MB
Quick reference
Creating Basic (non-Power) Pivot Tables
An overview of creating a basic Pivot Table in Excel.
When to use
When you need to build a Pivot Table without Power Pivot.
Instructions
Creating and Configuring PivotTables:
- Select your data source and choose Insert --> PivotTable
- Drag and drop the data fields from the Fields Section to the various Areas Sections to create the desired output
- Multiple PivotTables can be created against the same data source
Best practices:
- Format the data in your worksheet as an Excel Table before building your Pivot Table
- Name the PivotTable by going to the Pivot Tables Tools tab --> Analyze --> Pivot Table Name
Hints & tips
- Pivot Tables are not connected live to the data
- If the underlying data table changes, the Pivot Table needs to be manually refreshed
- 00:05 In this module, we're gonna start just a quick basic pivot table review for
- 00:09 non-power pivot tables.
- 00:11 And what I wanna call out is before we get started here,
- 00:14 we have a beautiful set of data that looks like it's perfectly ready to be pivoted,
- 00:18 except for one small problem.
- 00:20 You'll notice it has all the characteristics of good data.
- 00:22 It's got a great header row.
- 00:24 It's got consistent data types down the column.
- 00:27 These guys are all dates.
- 00:28 These guys look like they're all numbers formatted as text.
- 00:31 These guys are all decimal numbers and we've got a bunch of text here.
- 00:35 With no blank row between the header and the data,
- 00:38 everything is perfectly ready to be setup.
- 00:39 What we need to do before we pivot this,
- 00:42 it's a best practice to make sure that it's a proper Excel table.
- 00:45 So we'll click inside the table somewhere and then we'll make the choice of what
- 00:49 color we want our table to be which in this case, I'm gonna choose blue.
- 00:53 Notice that it picks up the data range for us,
- 00:55 because it had no blank row between the headers and the data.
- 00:58 It can grab the entire thing, which is great and
- 01:00 it's also identified immediately for me that my table has headers.
- 01:04 It was able to do this due to both the date and the amount columns having numbers
- 01:08 down them that are not formatted as text, because the header's text.
- 01:13 The values are numeric.
- 01:14 It gets this choice right.
- 01:16 If everything were text, in other words, if the date and
- 01:19 amount columns weren't here, I would have to check this box manually.
- 01:22 Because it wouldn't know.
- 01:24 We're now going to say, OK.
- 01:27 This puts a table format on it and
- 01:29 you'll notice that we are immediately on the table tools design tab.
- 01:33 Now, one of the things that was missing in that initial dialogue was the ability
- 01:36 to name a table and this is the most important thing you can
- 01:39 do once you've created a table is to give your table a name.
- 01:43 I'm gonna call this one transactions and
- 01:45 that gives me a nice named table which I can now get back to at any time by just
- 01:50 clicking on the little drop-down list here, and there we go.
- 01:54 Now, the key thing here is we've got our data in the top of the form.
- 01:58 Everything is great, it's in a format and in a table which is perfect.
- 02:01 I'm gonna go now say, insert pivot table.
- 02:04 Notice there's no dollar signs in the table name range here, which is excellent.
- 02:08 It's using the name and that's fantastic.
- 02:11 I'm now gonna go and drop my pivot table onto an existing worksheet and I'm gonna
- 02:15 put it into cell B7, the green cell on this next worksheet and we'll say, OK.
- 02:22 So this creates me the pivot table frame that I can now go and start slicing,
- 02:27 and dicing the way I want.
- 02:29 So I'm gonna drag class onto my rows and
- 02:31 that will show me all of the unique values that were in that set of data.
- 02:35 I can drag group into here as well and it'll actually segregate
- 02:39 these things quite nicely for me and I could put say, department on my columns.
- 02:44 And I could go and throw amounts into my values, and
- 02:47 it's gonna summarize everything up for me very nicely.
- 02:50 I could then say, I wanna see more detail.
- 02:52 Maybe I'll throw account name in here.
- 02:54 I'll think, well, that's kind of interesting.
- 02:57 But maybe that's too much detail, so maybe I'll just take it back off.
- 03:01 So have the ability to move things around if I wanna see,
- 03:04 maybe the department underneath the group.
- 03:07 So I can break it down this way or I decide not to and
- 03:10 I decide to move it back.
- 03:13 The nice thing with the pivot table,
- 03:15 of course is that we have the ability to quickly move data around into different
- 03:19 formats in order to see it the way that we want.
- 03:22 And if we want multiple reports, we can go back to the data tab.
- 03:26 Create a new pivot table and we can have multiple views off the same data.
- 03:30 I like to think with pivot tables is kind of the rubric's cube of data, but
- 03:34 I can slice and dice any way I want.
- 03:36 Now the one thing that is really important about this though,
- 03:40 that we have to remember is that the pivot table is not connected live to our data.
- 03:46 So if we look at this right now, I'm just gonna go and take this information here.
- 03:50 I'm gonna copy the grand total, Ctrl+C.
- 03:52 I'm gonna go right-click up on here and paste it as a value and
- 03:55 then I'm gonna format it as a number, so we can read it a little easier.
- 03:59 So it's 257,000.
- 04:02 I'm gonna go back to the data tab.
- 04:04 And on one of these values here, this sports wear one here.
- 04:07 I'm gonna throw a whole bunch of nines in front of this thing just to try and
- 04:11 make this number really, really big and
- 04:13 it extends it out to be too big to see in the cell.
- 04:15 So if I go back and format it, you can see, here we are.
- 04:17 I'm gonna go back to the report.
- 04:21 Now, it should be pretty obvious that we've changed this.
- 04:23 And if I go back to the report,
- 04:25 you can see that we're still looking at the 270,000.
- 04:30 So the key to remember here and this does not change with regular pivot tables or
- 04:35 power pivot tables, what we wanna do is go to data and
- 04:39 go to Refresh All and Refresh All will force this to update.
- 04:43 Now again, the number format is looking a little bit ugly on this guy here.
- 04:46 So we'll just reformat that for a second.
- 04:48 There we go.
- 04:49 Now, it's showing up quite nicely to show that we actually had something happen.
- 04:54 If I wanna update it again or set it back to where it was, I can go back to data or
- 04:58 strip off all these nines enter.
- 05:03 When I go back, notice the report is still the way it was.
- 05:06 I can also if I want to right-click and do a refresh on the pivot table itself, and
- 05:11 that will update every single pivot table that is built off the original data table
- 05:16 as well.
- 05:16 So biggest thing to remember, lots of dragging dropability and
- 05:20 remember that your data is not setup live.
- 05:22 You must refresh it manually.
- 05:24 That's the one big thing that we have to remember with pivot tables.
Lesson notes are only available for subscribers.