How to Add a Database to PowerPivot for Excel

Are you new to PowerPivot for Excel? You’ve come to the right place. In this tutorial and in upcoming tutorials, I will be covering the basics of Excel’s powerful new data analysis tool. In this guide I will show you how to add an Access database with more than one table. Before you get started you will need to download and install PowerPivot from Microsoft. If you don’t know what PowerPivot is or what it does, please read the following:

“PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within the application users already know and love—Microsoft Excel. It’s the user-friendly way to perform data analysis using familiar Excel features you already know, such as the Office Fluent user interface, PivotTable and, PivotChart views, and slicers. It’s the fast way to generate rich and interactive analysis tools, easily mistaken for IT solutions after weeks of effort. It’s the right way to achieve deeper business insight and shorter decision cycles.” Via the PowerPivot Home Page.

If you have any PowerPivot or Excel tips to share, please post them below. Make sure to bookmark the PowerPivot link on GilsMethod.com to check back for the latest PowerPivot how-to’s.

Note: I used Excel 2010 and PowerPivot for this tutorial.

add-database-to-excel-power-pivot-b

1. With Excel open, click on the PowerPivot tab in the Office Ribbon.

2. Click on the PowerPivot Window button.


add-database-to-excel-power-pivot-b

3. When PowerPivot opens, click on the From Database button in the Get External Data group.

4. Click on From Access from the list.

add-database-to-excel-power-pivot-b

5. Enter the necessary information for the database and select the database using the Browse button. If the database requires a password, enter the information here.

6. Click Next when you are ready.

add-database-to-excel-power-pivot-c

PowerPivot will connect with the database…

add-database-to-excel-power-pivot-d

7. After PowerPivot connects to the database you will be asked if you would like to Select the tables to include or if you would like to Write a query to specify which data to include. Choose the option that suits your situation.

8. Click Next to proceed.

add-database-to-excel-power-pivot-e

9. In this example I chose the option to select the tables to include, click on the tables you would like to include.

10. Click Finish when you are done. PowerPivot will begin the export process.

add-database-to-excel-power-pivot-f

11. Once the export process is completed, click on Close.

add-database-to-excel-power-pivot-g

All the data from your Access database is now available in PowerPivot.