If you have data in Excel and want to get that data into PowerPivot, you could copy and paste, or you can use the method in this video to declare the data as a table, then create a Linked Table.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
PowerPivot for the Data Analyst for Linking Excel Data to PowerPivot.
Well, hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
We're working through the examples for PowerPivot for the Excel Data Analyst.
We're taking a look here in Chapter 4 about how to take linked tables and add those to power pivot.
Now, tables. Tables are new in Excel 2007.
It's very possible that many people using excel have never really used tables before.
A table is good anytime you have headings across the top.
And then rows of data, which you think about is probably ninety percent of your spreadsheets.
But we need to do something special here.
We need to declare this as a table now.
There's a couple of different ways to do it.
I do it using Ctrl+t for table.
You could also come here to the home tab and save Format as Table.
Choose a table format.
Click OK and you get a table now.
Notice here on the table tool's design tab, it gives us the real imaginative name of Table1.
You can use Table1 but let's change that table name before we link.
I would just call it StoreInfo.
StoreInfo...
So, this map store number to store name and region and and other things like that.
Ok so now, we have an official table.
The benefits of an official table, well we get the filter dropdowns.
We could have added that.
We get the nice formatting, where we can you know show banded rows or banded columns.
That's nice.
The real benefit though is when we create charts or pivot tables from this data and paste new data to the bottom the chart or pivot table will automatically expand.
Now that we have PowerPivot, the other great advantages, we don't have to copy and paste this data to PowerPivot.
We can simply create a linked table, create a linked table.
So there the demo worksheet is what we imported in the first video.
Now this StoreInfo sheet is this linked table.
Alright, so we didn't actually copy and paste.
Later on if we decide that we need to change something here, we want to move this from the Atlanta region to the Arizona region.
All we would have to do is Update All.
And that data will get updated in PowerPivot.
So, linked tables great way if you just have a simple little Excel data set.
Great way to get that data into PowerPivot.
In this case good for a tiny little lookup table here.
That will use later when we're creating our pivot tables.
Okay, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
PowerPivot for the Data Analyst for Linking Excel Data to PowerPivot.
Well, hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
We're working through the examples for PowerPivot for the Excel Data Analyst.
We're taking a look here in Chapter 4 about how to take linked tables and add those to power pivot.
Now, tables. Tables are new in Excel 2007.
It's very possible that many people using excel have never really used tables before.
A table is good anytime you have headings across the top.
And then rows of data, which you think about is probably ninety percent of your spreadsheets.
But we need to do something special here.
We need to declare this as a table now.
There's a couple of different ways to do it.
I do it using Ctrl+t for table.
You could also come here to the home tab and save Format as Table.
Choose a table format.
Click OK and you get a table now.
Notice here on the table tool's design tab, it gives us the real imaginative name of Table1.
You can use Table1 but let's change that table name before we link.
I would just call it StoreInfo.
StoreInfo...
So, this map store number to store name and region and and other things like that.
Ok so now, we have an official table.
The benefits of an official table, well we get the filter dropdowns.
We could have added that.
We get the nice formatting, where we can you know show banded rows or banded columns.
That's nice.
The real benefit though is when we create charts or pivot tables from this data and paste new data to the bottom the chart or pivot table will automatically expand.
Now that we have PowerPivot, the other great advantages, we don't have to copy and paste this data to PowerPivot.
We can simply create a linked table, create a linked table.
So there the demo worksheet is what we imported in the first video.
Now this StoreInfo sheet is this linked table.
Alright, so we didn't actually copy and paste.
Later on if we decide that we need to change something here, we want to move this from the Atlanta region to the Arizona region.
All we would have to do is Update All.
And that data will get updated in PowerPivot.
So, linked tables great way if you just have a simple little Excel data set.
Great way to get that data into PowerPivot.
In this case good for a tiny little lookup table here.
That will use later when we're creating our pivot tables.
Okay, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
