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; declare the data as a table, then create a Linked Table.
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
PowerPivot For The Data Analyst 4: Linking Excel Data to PowerPivot. Well, hey, welcome back to the MrExcel netcast, I'm Bill Jelen, and we're working through the examples for PowerPivot for the Excel Data Analyst.
We're taking a look here, in chapter four, about how to take linked tables and add those to PowerPivot.
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 know-- think about it-- is probably 90% 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 say "Format as Table," choose a table format, click OK, and you get a table.
Now, notice here on the Table Tools' 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-- we'll just call it "StoreInfo".
StoreInfo.
So this will map store number to store name and region, and other things like that.
Okay, so, now, we have an official table.
The benefits of an official table?
Well, we get the filter dropdown-- 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 advantage is, we don't have to copy and paste this data to PowerPivot.
We can simply create a linked table-- Create 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 we'll 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 4: Linking Excel Data to PowerPivot. Well, hey, welcome back to the MrExcel netcast, I'm Bill Jelen, and we're working through the examples for PowerPivot for the Excel Data Analyst.
We're taking a look here, in chapter four, about how to take linked tables and add those to PowerPivot.
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 know-- think about it-- is probably 90% 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 say "Format as Table," choose a table format, click OK, and you get a table.
Now, notice here on the Table Tools' 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-- we'll just call it "StoreInfo".
StoreInfo.
So this will map store number to store name and region, and other things like that.
Okay, so, now, we have an official table.
The benefits of an official table?
Well, we get the filter dropdown-- 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 advantage is, we don't have to copy and paste this data to PowerPivot.
We can simply create a linked table-- Create 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 we'll 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.
