You have a main table and two lookup tables. You need to use all three tables in a pivot table. Bill shows Power Pivot and Mike shows VLOOKUP in Dueling Excel episode 161
Transcript of the video:
Alright, welcome back, it's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, I’ll be joined by Mike Girvin from Excel Is Fun.
This is our episode 161.
Pivot Table From Three Tables.
All right Mike, this is a great question: how can I create a Revenue Report by Region and by Year from three tables: Table 1: Transactions, SalesRep and Price.
Let's take a look, these… Table 1 is kind of what the SQL server guys would call “the fact table”.
It has all the transactions: Date, Product, UnitsSold, SalesRep.
Ah, but no Region and no Price.
To get the Region, we have to come out to the SalesRep table, to get the Price, we have to come out to the Product table.
So now, thanks to Excel 2010 and Power Pivot, we can easily create a Pivot Table without doing any VLOOKUPs at all.
I'm going to take all three of these tables and make them into real tables with Ctrl+T, official tables.
We'll call this Sales.
And then, the next one, SalesRep, Ctrl+T, click OK, we'll call this Reps.
And then the last one, Price, Ctrl+T, we'll call that Pricing.
All right, so we have our three tables, I'm going to add all three tables to Power Pivot.
Now, how do you get Power Pivot?
If you're in Excel 2010, you go to powerpivot.com and you download it.
If you're in Excel 2013, it's really hard.
You either need Excel 2013 Pro Plus, which they don't sell on the stores, or Excel 2013 Standalone, which of course you can get in the store.
So there is my Sales table in PowerPivot, I'm going to come back to Excel, go to the SalesRep table and I'm going to add that to the Data Model.
And then back to Excel, and the Price table, add that to the Data Model.
So we now have our three items here in PowerPivot and I need to create relationships.
This is where people would normally do a VLOOKUP, but thanks to Power Pivot, it's just simply dragging from one field to another field.
So here's our Diagram View, I'm going to go from the Product field in the Sales table to the Product field in Pricing.
Wait for that line to draw, there we go.
And then from the SalesRep table in the Sales table to SalesRep in the Reps table, right.
So now we're ready to go, we can report by Region, we can report UnitsSold.
Oh, but we need to get this Price field back into Sales.
So to do that, I'm going to go to the Data View, come back to our Sales table and, admittedly, this is like a VLOOKUP, but instead of using VLOOKUP, in PowerPivot we use =RELATED.
So the related field and it is from the Pricing table, so I take “Pri…” and it's =RELATED(Pricing[ Price ])*[ UntisSold ], times the UnitsSold.
By the way, for those of you, who would like to hit the Left arrow key there twice, you're not going to be able to do that.
The Power Pivot does not support using the arrow keys to build your formulas.
They always create these horrible names, like CalculatedColumn1, I'll call it Revenue.
And then, out here, I could go through the hassle of creating a Calendar Table.
Power Pivot does not let us roll dates, daily dates up to years, so I'm going to just use the year function out there to do the roll up myself.
So right click, Rename and call that Year, alright, we're now ready to go.
We have our three tables in Power Pivot, we're going to go back to Excel with a regular Pivot Table on a new worksheet and it's smooth sailing from here.
To get the Regions, just check mark, it's automatically going to go to the ROWS area.
To get the Revenue, just check mark, because it's numeric, it's going to go right there and then I'll take the Year field and drag that to COLUMNS and we are good to go.
Power Pivot is an amazing tool, I would have loved to have done three sets of VLOOKUPs to get all this to happen, or two sets of VLOOKUPs to get all this to happen, but it's so much easier now with Power Pivot.
Mike, let's see what you have.
Mike: Thanks, MrExcel.
Oh, the power of Power Pivot, absolutely beautiful, right from the field list you can pull fields from different tables.
Ah, but if you don't have Power Pivot, you are stuck building a bunch of extra helper columns and VLOOKUPs.
So let's see how to do that.
So we have Table 1, this has got Date, Product, UnitsSold and SalesRep.
We have to get the Regions based on the SalesRep, bring them back over the Transaction table.
We're also, since we only have Units, we're going to have to have another column that goes and gets the Price and then we multiply that time of Units, and finally, we're going to have a Year column.
Why don't we just start out with Year, Ctrl+Enter, Ctrl+B and we'll do the =YEAR(A2) function.
Ctrl+Enter, double click and send it down.
So that will be our column header in the Pivot Table.
The next thing is: we need Region, Ctrl+B, so we're going to do =, oh well, we're doing a =VLOOKUP, so the lookup_value will be SalesRep (D2), comma, the table_array, comma, over here on SalesRep, will highlight the whole table and hit the F4 key.
Notice, I went over to the next sheet, highlighted the range, so up here we can see our sheet reference.
I'm going to go ahead and finish the formula up here: comma, the col_index_num is 2, and commas, 0 for exact match.
Close parentheses (=VLOOKUP(D2,SalesRep!$A$2:$B$15,2,0)).
Ctrl+Enter, double click and send it down.
Double click and widen that column there, alright.
So now we need our SalesTotal.
First thing we do, is we need to get with =VLOOKUP the Price, so I'm going to look up the actual Product (B2), comma, go back over to Price table, highlight the table, F4, comma, 2, comma, 0, close parenthesis (=VLOOKUP(B2,Price!$A$2:$B$8,2,0)), Ctrl+Enter.
Now, that gives us the Price from that other table there, but we need to multiply this times our Units (*C2).
It doesn't look like we're ever going to have a rounding error, so I'm not going to use the ROUND, Ctrl+Shift+4 to format that, double click and send it down.
All right, so now we can make a Pivot Table, Alt, N, V, Enter, so we have our field list over here, we're going to have Region down to the ROWS.
Notice we're pulling from one table with a bunch of helper columns, right.
Year will be in COLUMNS and SalesTotal will be down in VALUES.
I'm going to immediately go up to… it bothers me so much… Report, Show in Tabular… and then right click this field and instead of Format Cells, we're going to use Number Formatting and do something like Accounting.
Click OK and so there we have: by Region and Year our Sales Report, from one, two, three different tables.
Alright, throw it back to MrExcel.
Bill: Alright, Mike.
Yeah, either way, the VLOOKUPs or PowerPivot, if you have Excel 2010 or 2013 and have Power Pivot, that probably is the way to go.
Otherwise: VLOOKUP.
So hey, I want to thank everyone for stopping by, we’ll see you next week for another Dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel, I’ll be joined by Mike Girvin from Excel Is Fun.
This is our episode 161.
Pivot Table From Three Tables.
All right Mike, this is a great question: how can I create a Revenue Report by Region and by Year from three tables: Table 1: Transactions, SalesRep and Price.
Let's take a look, these… Table 1 is kind of what the SQL server guys would call “the fact table”.
It has all the transactions: Date, Product, UnitsSold, SalesRep.
Ah, but no Region and no Price.
To get the Region, we have to come out to the SalesRep table, to get the Price, we have to come out to the Product table.
So now, thanks to Excel 2010 and Power Pivot, we can easily create a Pivot Table without doing any VLOOKUPs at all.
I'm going to take all three of these tables and make them into real tables with Ctrl+T, official tables.
We'll call this Sales.
And then, the next one, SalesRep, Ctrl+T, click OK, we'll call this Reps.
And then the last one, Price, Ctrl+T, we'll call that Pricing.
All right, so we have our three tables, I'm going to add all three tables to Power Pivot.
Now, how do you get Power Pivot?
If you're in Excel 2010, you go to powerpivot.com and you download it.
If you're in Excel 2013, it's really hard.
You either need Excel 2013 Pro Plus, which they don't sell on the stores, or Excel 2013 Standalone, which of course you can get in the store.
So there is my Sales table in PowerPivot, I'm going to come back to Excel, go to the SalesRep table and I'm going to add that to the Data Model.
And then back to Excel, and the Price table, add that to the Data Model.
So we now have our three items here in PowerPivot and I need to create relationships.
This is where people would normally do a VLOOKUP, but thanks to Power Pivot, it's just simply dragging from one field to another field.
So here's our Diagram View, I'm going to go from the Product field in the Sales table to the Product field in Pricing.
Wait for that line to draw, there we go.
And then from the SalesRep table in the Sales table to SalesRep in the Reps table, right.
So now we're ready to go, we can report by Region, we can report UnitsSold.
Oh, but we need to get this Price field back into Sales.
So to do that, I'm going to go to the Data View, come back to our Sales table and, admittedly, this is like a VLOOKUP, but instead of using VLOOKUP, in PowerPivot we use =RELATED.
So the related field and it is from the Pricing table, so I take “Pri…” and it's =RELATED(Pricing[ Price ])*[ UntisSold ], times the UnitsSold.
By the way, for those of you, who would like to hit the Left arrow key there twice, you're not going to be able to do that.
The Power Pivot does not support using the arrow keys to build your formulas.
They always create these horrible names, like CalculatedColumn1, I'll call it Revenue.
And then, out here, I could go through the hassle of creating a Calendar Table.
Power Pivot does not let us roll dates, daily dates up to years, so I'm going to just use the year function out there to do the roll up myself.
So right click, Rename and call that Year, alright, we're now ready to go.
We have our three tables in Power Pivot, we're going to go back to Excel with a regular Pivot Table on a new worksheet and it's smooth sailing from here.
To get the Regions, just check mark, it's automatically going to go to the ROWS area.
To get the Revenue, just check mark, because it's numeric, it's going to go right there and then I'll take the Year field and drag that to COLUMNS and we are good to go.
Power Pivot is an amazing tool, I would have loved to have done three sets of VLOOKUPs to get all this to happen, or two sets of VLOOKUPs to get all this to happen, but it's so much easier now with Power Pivot.
Mike, let's see what you have.
Mike: Thanks, MrExcel.
Oh, the power of Power Pivot, absolutely beautiful, right from the field list you can pull fields from different tables.
Ah, but if you don't have Power Pivot, you are stuck building a bunch of extra helper columns and VLOOKUPs.
So let's see how to do that.
So we have Table 1, this has got Date, Product, UnitsSold and SalesRep.
We have to get the Regions based on the SalesRep, bring them back over the Transaction table.
We're also, since we only have Units, we're going to have to have another column that goes and gets the Price and then we multiply that time of Units, and finally, we're going to have a Year column.
Why don't we just start out with Year, Ctrl+Enter, Ctrl+B and we'll do the =YEAR(A2) function.
Ctrl+Enter, double click and send it down.
So that will be our column header in the Pivot Table.
The next thing is: we need Region, Ctrl+B, so we're going to do =, oh well, we're doing a =VLOOKUP, so the lookup_value will be SalesRep (D2), comma, the table_array, comma, over here on SalesRep, will highlight the whole table and hit the F4 key.
Notice, I went over to the next sheet, highlighted the range, so up here we can see our sheet reference.
I'm going to go ahead and finish the formula up here: comma, the col_index_num is 2, and commas, 0 for exact match.
Close parentheses (=VLOOKUP(D2,SalesRep!$A$2:$B$15,2,0)).
Ctrl+Enter, double click and send it down.
Double click and widen that column there, alright.
So now we need our SalesTotal.
First thing we do, is we need to get with =VLOOKUP the Price, so I'm going to look up the actual Product (B2), comma, go back over to Price table, highlight the table, F4, comma, 2, comma, 0, close parenthesis (=VLOOKUP(B2,Price!$A$2:$B$8,2,0)), Ctrl+Enter.
Now, that gives us the Price from that other table there, but we need to multiply this times our Units (*C2).
It doesn't look like we're ever going to have a rounding error, so I'm not going to use the ROUND, Ctrl+Shift+4 to format that, double click and send it down.
All right, so now we can make a Pivot Table, Alt, N, V, Enter, so we have our field list over here, we're going to have Region down to the ROWS.
Notice we're pulling from one table with a bunch of helper columns, right.
Year will be in COLUMNS and SalesTotal will be down in VALUES.
I'm going to immediately go up to… it bothers me so much… Report, Show in Tabular… and then right click this field and instead of Format Cells, we're going to use Number Formatting and do something like Accounting.
Click OK and so there we have: by Region and Year our Sales Report, from one, two, three different tables.
Alright, throw it back to MrExcel.
Bill: Alright, Mike.
Yeah, either way, the VLOOKUPs or PowerPivot, if you have Excel 2010 or 2013 and have Power Pivot, that probably is the way to go.
Otherwise: VLOOKUP.
So hey, I want to thank everyone for stopping by, we’ll see you next week for another Dueling Excel podcast from MrExcel and Excel Is Fun.
