Using PowerPivot for Excel 2010 to mashup data on two different worksheets.
Transcript of the video:
The MrExcel podcast is brought to you by “Easy-XL”!
Hey, welcome back to the MrExcel netcast.
Today's question sent in by Rahul.
Rahul says “Hey, now that the Office 2010 beta is out, any chance you can show us PowerPivot?
I had to check with the powers that be, this is so cool though, you're going to love this!
For those of you who haven't heard of PowerPivot, it used to be called Gemini, is a free add-in that's coming with Excel 2010.
It's not from the Excel team, it's from the Analysis Services team, it is AMAZING what you can do with PowerPivot.
So here I am in Excel 2010, I'm going to open the PowerPivot window, and say that I have some data, I’m going to get some data from a file, from a text file.
Alright come out here, will browse for that text file, and “demo”.
Now, just let's take a quick look over here, so this is a 58 MB, it's 1.8 million records.
Alright, so it's going to take a little bit of time for Excel to figure out what's going on here, first of all Store ID, Date, Division, great, yeah.
Let's see, we'll have this import, I'll come back, I'll tell you how long it took, alright?
OK, that was 68 seconds!
Success, 1.812.888 rows transferred, click Close.
Alright, see how this works, you have here called “demo” looks like a worksheet right, I can filter it, I can scroll it.
Check this out, there's record 200.000, record 400.000, record 600.000, record 1.3 million, record 1.5 million.
Look at this, I'm scrolling, I'm sorting, I'm filtering through 1.8 million rows in Excel, and that's not it.
If you have enough memory, you can put a hundred million records in this thing, alright, that's not the half of it.
I'm going to come back to Excel I'm going to copy this data, this is Excel data, we’ll go back to the PowerPivot window, and I'm going to paste from clipboard to New Table.
We'll call this StoreData, click Ok, all right!
So now, check this out, I have two worksheets, one called demo, with 1.8 million records, one called StoreData, and I want to do an analysis between the two of these.
So, what would you do?
Do VLOOKUPs use Easy-XL?
Don't have to do either of those, I'm going to create a Pivot table, Single PivotTable, New Worksheet.
Alright, check out this new Pivot table field list, there are fields from both sheets.
How the heck is that going to work?
Alright, so I want to see Revenue by store name, oh, relationship may be needed, click create, takes a look at the data, because I have Store ID in both, figured it out, got it right, click Close, alright.
And now I have data from Sheet1 and Sheet2, I didn't build a VOOKUP, it figured out what to do, and created that data.
This is 1.8 million records, Pivot table in Excel joining data from two different sheets, is this the greatest thing you've ever seen?
Check out the new slicers, these are new in Excel 2010.
So I can build Slicers Vertical, there's a list of Regions, maybe Mall Developer in Slicers Horizontal, I can choose, I just want to see things that are at Simon Malls or something like that, Simon Property Group.
Alright, so these are the states that have Simon Malls, where we have stores, those are the sales by those stores, and I could keep going on and on and on and on.
This is PowerPivot!
It's a free add-in that's going to come with Excel 2010.
In my opinion, this is the greatest thing that I've seen come out of Microsoft in 15 years!
And the amazing thing, it's free, Analysis Services team is providing this, knowing that some percentage of us Excel people who start using PowerPivot will want to get a SharePoint Server, so that we can publish this.
But for most of us, we're just thrilled, 1.8 million rows, two different worksheets, this is incredible.
So, Ray Holt, thanks for that request, hopefully, for those of you who watch this you'll be just drooling, can't wait to get a copy of Excel 2010 to give this a try.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast.
Today's question sent in by Rahul.
Rahul says “Hey, now that the Office 2010 beta is out, any chance you can show us PowerPivot?
I had to check with the powers that be, this is so cool though, you're going to love this!
For those of you who haven't heard of PowerPivot, it used to be called Gemini, is a free add-in that's coming with Excel 2010.
It's not from the Excel team, it's from the Analysis Services team, it is AMAZING what you can do with PowerPivot.
So here I am in Excel 2010, I'm going to open the PowerPivot window, and say that I have some data, I’m going to get some data from a file, from a text file.
Alright come out here, will browse for that text file, and “demo”.
Now, just let's take a quick look over here, so this is a 58 MB, it's 1.8 million records.
Alright, so it's going to take a little bit of time for Excel to figure out what's going on here, first of all Store ID, Date, Division, great, yeah.
Let's see, we'll have this import, I'll come back, I'll tell you how long it took, alright?
OK, that was 68 seconds!
Success, 1.812.888 rows transferred, click Close.
Alright, see how this works, you have here called “demo” looks like a worksheet right, I can filter it, I can scroll it.
Check this out, there's record 200.000, record 400.000, record 600.000, record 1.3 million, record 1.5 million.
Look at this, I'm scrolling, I'm sorting, I'm filtering through 1.8 million rows in Excel, and that's not it.
If you have enough memory, you can put a hundred million records in this thing, alright, that's not the half of it.
I'm going to come back to Excel I'm going to copy this data, this is Excel data, we’ll go back to the PowerPivot window, and I'm going to paste from clipboard to New Table.
We'll call this StoreData, click Ok, all right!
So now, check this out, I have two worksheets, one called demo, with 1.8 million records, one called StoreData, and I want to do an analysis between the two of these.
So, what would you do?
Do VLOOKUPs use Easy-XL?
Don't have to do either of those, I'm going to create a Pivot table, Single PivotTable, New Worksheet.
Alright, check out this new Pivot table field list, there are fields from both sheets.
How the heck is that going to work?
Alright, so I want to see Revenue by store name, oh, relationship may be needed, click create, takes a look at the data, because I have Store ID in both, figured it out, got it right, click Close, alright.
And now I have data from Sheet1 and Sheet2, I didn't build a VOOKUP, it figured out what to do, and created that data.
This is 1.8 million records, Pivot table in Excel joining data from two different sheets, is this the greatest thing you've ever seen?
Check out the new slicers, these are new in Excel 2010.
So I can build Slicers Vertical, there's a list of Regions, maybe Mall Developer in Slicers Horizontal, I can choose, I just want to see things that are at Simon Malls or something like that, Simon Property Group.
Alright, so these are the states that have Simon Malls, where we have stores, those are the sales by those stores, and I could keep going on and on and on and on.
This is PowerPivot!
It's a free add-in that's going to come with Excel 2010.
In my opinion, this is the greatest thing that I've seen come out of Microsoft in 15 years!
And the amazing thing, it's free, Analysis Services team is providing this, knowing that some percentage of us Excel people who start using PowerPivot will want to get a SharePoint Server, so that we can publish this.
But for most of us, we're just thrilled, 1.8 million rows, two different worksheets, this is incredible.
So, Ray Holt, thanks for that request, hopefully, for those of you who watch this you'll be just drooling, can't wait to get a copy of Excel 2010 to give this a try.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
