Power Pivot is a very powerful analytical tool which allows you to import data from various external sources!

This opens up many possibilities and gives you the power to do further data analysis and get insightful business metrics.

You can import data from the following sources:


  • SQL Server
  • Access
  • Analysis Services



  • Windows Azure Marketplace
  • OData Data Feed
  • Suggested Related Data



  • Oracle
  • IBM DB2
  • Microsoft Analysis Services
  • Excel Files
  • Text Files
  • Plus many more places…


In our example below, we are going to import data from an Excel File called Separate Excel Workbook.xlsx which is saved in our desktop.

You can view below the data that is in this Excel File:

Importing Excel Workbooks 01


Please download this workbook to follow the tutorial below:



You also need to download the following workbook and save it in your computer’s desktop.  You will need this file path for Step 4:



STEP 1: Open the Power Pivot Window.  Go to Power Pivot > Manage.

(For Excel 2010, go to PowerPivot > PowerPivot Window)

Importing Excel Workbooks 02


STEP 2: This will open the Power Pivot Window.  Go to Home > Get External Data > From Other Sources.

Importing Excel Workbooks 03


STEP 3: In the Table Import Wizard,  pick Excel File and click Next.

Importing Excel Workbooks 04


STEP 4: Browse for the Excel workbook that has your data.

Make sure check the Use first row as column headers checkbox i.e. Since our data has column headers.

Importing Excel Workbooks 05


STEP 5: Select your file and click Open.

Importing Excel Workbooks 06


STEP 6: Click Next.

Importing Excel Workbooks 07


STEP 7: Make sure the Source Table from the imported Workbook is selected.

Importing Excel Workbooks 08


You can also click on the Preview & Filter button (bottom right hand corner)  to choose or filter specific columns & data that you want to import:


When you have made your selection, click OK and then select Finish in the next screen.


STEP 8: You will get the following Importing Success dialogue box which shows the number of rows that have been imported.  Click Close.

Importing Excel Workbooks 09


STEP 9: Your data is now ready for analysis using Power Pivot.

Importing Excel Workbooks 10

Importing Excel Workbooks




If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Import Data from XML Using Power Query or Get ... Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data. But what if your data source is not in your Excel spreadsheet? It's very common nowadays to get data imported from a company's accounting or sales system in t...
Using Excel Tables in Power Pivot To be able to use Power Pivot, you need to have data imported into the Power Pivot Window first. If your data is in the normal Excel worksheets, then you will not be able to use this data in Power Pivot. Fortunately there are a lot of possible ways Power Pivot can retrieve ...
Linking Excel Tables in Power Pivot   When you have multiple tables, Power Pivot can help you link them together. After linking them together you can then create a Pivot Table that will give you a single view of data. What we will focus on is a simple example of two Excel Tables: a Name Table and a Sales...
Using the Diagram View in Power Pivot   Have you gotten overwhelmed with the amount of Tables whenever you used Power Pivot? As I got accustomed to Power Pivot, my data model expanded and the number of relationships connecting these Tables together grew to a larger amount. Fortunately, Power Pivot has t...