Skip to main content

Import Linked Data from Excel

Rate:  

About This Video:

Import data from an Excel worksheet and maintain a link between the data in the worksheet and the data in PowerPivot.

This video available with closed captions. To view closed captions, click CC on the video control bar.

Transcript

We'll show you how to import data from an Excel worksheet and maintain a link between the data in the worksheet and the data in PowerPivot.

The data I created in the previous video is static because once I pasted the data, they're not automatically updated.

But I can also create link tables that automatically reflect changes made to the source.

Linked tables must reference Excel tables found in the same workbook as the PowerPivot data.

This is an important concept to explain.

When you're working with PowerPivot in an Excel workbook, that one workbook has an Excel window and a PowerPivot window.

The Excel window has one or more worksheets and the PowerPivot window has one or more tabs with each tab representing a table.

To create a linked table, the source table must exist in an Excel worksheet in the same workbook.

In order to create a linked table, I first open the Excel file that contains the data to which I'd like to create a link.

The name of the file in this case is Regions.

Then I copy the data I need and close the Regions file in order to avoid confusion.

In a blank sheet in my Excel window, I paste the Regions data and then I rename this sheet to Regions.

Now to create the link.

With the new data highlighted, select the PowerPivot tab and click Create Linked Table.

When I click OK, my PowerPivot window opens and I see that a new table has been created, notice the icon on the tab.

I renamed the table Regions.

Now let's check the link.

Click Go to Excel Table.

I'm back in the source Excel file.

I'll change UK to Great Britain and then return to my PowerPivot window.

The corresponding row has now been updated to the new value as well.

I've added data from my personal Excel worksheet and created a link to keep the data up-to-date.

Thank you for taking the time to view this video.

I hope you found it helpful.

 

Presented by: Michele Hart and Ed Price

 

Downloads

Video: WMV(Zip) | MP4 | WMV