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

COUNTROWS in Excel Power Pivot  In Power Pivot, one of the major and more powerful feature are Measures.Measures (also known as Calculated Fields in Excel 2013) are formulas/calculations that are added to a Pivot Table.We will work on a simple example to show you how easy it is to use the COUN...
Import Data from Text 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?If it's inside a text file, it's very easy to import data from text and right into Power Query!...
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...
Power Pivot is now available in all versions of Ex... Microsoft have just announced that Excel Power Pivot will now be available in all Windows editions of Excel, which is awesome news and a step in the right direction.Here is what they announced: I’m happy to announce that your input has had another direct impact on the produc...