Locked lesson.
About this lesson
You've got data, and you want to build a PivotTable now. What could possible go wrong? In this lesson we will show you!
Exercise files
Download this lesson’s related exercise files.
Creating a Power PivotTable.xlsx672.4 KB Creating a Power PivotTable - Completed.xlsx
677.2 KB
Quick reference
Creating a Power PivotTable
An overview of creating a Power Pivot Table.
When to use
When you want to build your Power Pivot data model.
Instructions
To create a new Power Pivot Table:
- Excel 2016: Click a blank cell in any worksheet --> Insert --> PivotTable
- Excel 2013: Go to the Power Pivot tab (in Excel) --> Manage --> PivotTable (on the Home tab)
Configuring the PivotTable
- Drag and drop fields from the list to the PivotTable areas as you would for a basic Pivot Table
NOTE: If you are using fields from multiple tables, you must first create a relationship between the two tables before they can be used in the same PivotTable
Hints & tips
- Tables that are part of the Power Pivot Data Model have a yellow “bucket” as part of the icon on the field list
- It is recommended that you avoid using tables that show as Workbook Tables, as they have not been linked into the Data Model
- 00:04 >> It's now time to create our first Power Pivot pivot table.
- 00:09 And what we're gonna do is we're going to go to the Summary tab and
- 00:12 we're gonna start building a financial report based on the three pieces of
- 00:16 information we have on the side.
- 00:18 The departments table, the budgets table and the transaction table.
- 00:22 Now, creating that Power Pivot pivot table gets easier in every single version.
- 00:28 I'm gonna show you how easy it is to start with Excel 2016.
- 00:34 We would just say insert and choose pivot table.
- 00:37 Starting from a blank cell, if we do that,
- 00:40 it automatically defaults to the data model, which is Power Pivot.
- 00:45 Note how it's not quite as easy in Excel 2013.
- 00:48 I find the easiest way to do it in Excel 2013, for reference,
- 00:52 is to actually come to the Power Pivot window and choose Manage.
- 00:56 And when you get inside here there's a button that has pivot table.
- 00:59 You can still do it through the regular user interface,
- 01:02 it just takes a lot more steps.
- 01:04 So in this case I can say pivot table and it'll ask me,
- 01:07 where would you like to put it?
- 01:08 New worksheet or existing, we'll say existing.
- 01:11 And I'm gonna put it right on top of this green cell B8 on the summary worksheet.
- 01:17 That will drop in a pivot table frame exactly like you're used to working with
- 01:22 a regular pivot table,
- 01:23 except that this one is actually linked back to Power Pivot.
- 01:27 So what can we do with this now?
- 01:28 Well, you know what, what we can do is we can say let's go right-click and
- 01:32 show the field list since mine's not showing.
- 01:35 And you're gonna notice, I'm just gonna get rid of the queries and
- 01:38 connections pane altogether here.
- 01:39 You're gonna notice that we actually have four objects that are showing in this
- 01:44 window if I'm looking at the All Fields.
- 01:46 Three of them look a little different than they use to.
- 01:49 They've got this little yellow bucket on the corner.
- 01:51 And they also have arrows to open them up.
- 01:54 And if I go and take a look at transactions, for example,
- 01:57 I'm gonna open this guy up and you'll notice that it's got all of
- 02:00 the fields that we've actually pulled in via Power Query.
- 02:03 As does departments, has department and department name,
- 02:07 and budgets has the four different fields that were available there as well.
- 02:12 Now something that I do wanna call out here is that in this interface you'll also
- 02:16 notice another department.
- 02:17 And this one looks like a regular old Excel table, and it is.
- 02:21 Personally my feeling is, if this thing is linked into Power Pivot, into the guy
- 02:25 with the little yellow bucket, it actually shouldn't show here at all.
- 02:29 But unfortunately, it still does.
- 02:31 These tables are dead to you, okay, we don't wanna use those at all.
- 02:34 What we're interested in is the ones with the yellow buckets.
- 02:38 Now what I'm gonna do is I'm gonna start to build a pivot table out of this.
- 02:42 I'm gonna go and I'm gonna grab Class, and I'm gonna drag it onto Rows.
- 02:46 And I'm going to grab Group from the Transactions table, and
- 02:49 drag it onto Rows as well.
- 02:50 So this looks pretty much the same.
- 02:53 I'm gonna grab Amount and drag it into the Values area as well.
- 02:57 You can see that it summarizes up quite nicely.
- 03:00 It works just like any pivot table would.
- 03:02 I can come back, I can grab Revenues, and I can even move it, left-click and
- 03:07 drag to snap this guy up here.
- 03:09 The secret if you didn't know for this one is your mouse pointer has to get to this
- 03:12 four-headed arrow in order to be able to left-click and drag, that's a manual sort.
- 03:16 It works the same as it does in any pivot table, no big deal.
- 03:20 What we really want though is we want the ability to be able to slice and
- 03:24 dice this by other things, like this for example.
- 03:28 If I grab Department and put it on Columns,
- 03:30 it slices out quite nicely to show the individual department columns.
- 03:35 Wouldn't it be cool though If we could actually grab it,
- 03:38 from a completely different table?
- 03:40 If we could grab Departments here, and say, let's use Department Name.
- 03:45 And when I do this,
- 03:46 you're gonna notice that something very seriously wrong happens.
- 03:51 It pops up a yellow message,
- 03:52 it says relationships between the tables may be needed.
- 03:56 There's an Auto Detect, and a Create.
- 03:58 Now I'm gonna advise you not to use auto detect because that's the purpose of this
- 04:02 course, is to teach you why you're getting this and what to do about it.
- 04:06 You'll also notice that when I actually look at this in the pivot table,
- 04:10 it's giving me the exact same numbers for
- 04:13 every individual column, for every individual row.
- 04:16 This obviously has a very serious problem here.
- 04:19 And the reality is, at the end of the day, the challenge,
- 04:23 we haven't actually told it which tables need to be VLOOKUP'd against each
- 04:28 other in order to actually get this information right.
- 04:31 So if you see this yellow message, it's a good idea to come back and
- 04:34 say you know what, something isn't quite right here, I better fix it.
- 04:38 Let me just pull this off for right now,
- 04:40 get back to a state where there is no yellow.
- 04:43 And now we need to learn a little bit about to how to actually perform
- 04:47 the VLOOKUP magic that you normally would use, although for Power Pivot,
- 04:51 we're never ever gonna use another VLOOKUP again.
- 04:55 So let's go take a look at what's required in order to make that happen.
Lesson notes are only available for subscribers.