Microsoft Office Tutorials and References
In Depth Information
Loading from Excel Files
You have learned that if a table is stored in an Excel file, it can be used to create PowerPivot
linked tables. Nevertheless, linked tables have one big limitation: you can create a linked table
only if the table resides in the same Excel file as the PowerPivot data model. You might have
another Excel file containing some data—for example, a list of the special offers we plan to
give in 2005, such as the one shown in Figure 5-30 that you want to load into PowerPivot.
In this case, you cannot create a linked table, thus you will need to use the Excel data source
and treat the Excel workbook as if it was a database. This is exactly what we are going to
FIguRE 5-30 Special offers planned for the year 2005, in an external Excel file.
In this example, the Excel range containing data has been formatted as a table to make it
easier to refer to in other worksheets because we can use the table name instead of the cell
range. Let us suppose that you have saved this table in the CH05-03-2005Plans.xlsx file,
which contains several tables with your thoughts about the 2005 budget, and now you
want to import the data into PowerPivot. By the way, this Excel file is part of the book’s
companion material, so you can use the companion workbook to follow this example.
To begin the loading process, you need to select the From Other Sources button in the
PowerPivot window, after which you open the Table Import Wizard and then select Excel
File, which is near the end of the list, under the Text Files section. When you click Next,
PowerPivot opens the Table Import Wizard for Excel files (see Figure 5-31).