Microsoft Excel 2010 PowerPivot

Introduction

PowerPivot(formally known as Project Gemini) is a great analysis tool from Microsoft which can be used in both Microsoft Excel and SharePoint. It adds great computational power to your excel sheet and provides fast manipulation on large data sets(often in millions of rows), and streamlined integration of data. Once you have achieved faster results in your massive excel sheet then you can use SharePoint to share it further.

How to Install PowerPivot

Download PowerPivot from the official site and install it. Here you will find separate installers for both Microsoft Excel and SharePoint. These installers are available in both  32-bit and 64-bit versions. Once the installation is complete, the PowerPivot tab will be available on your Excel sheet.

PowerPivot thumb Microsoft Excel 2010 PowerPivotView in gallery

How To Use PowerPivot

PowerPivot’s key features include processing large amounts of data within a second or two, loading data from any external source in almost no time, and using powerful analytical operations on data.

Click the PowerPivot window option under the PowerPivot tab and it will launch the main window where you can perform all the operations related to PowerPivot.

PowerPivotWindow thumb Microsoft Excel 2010 PowerPivotView in gallery

Lets see how we can import data from any external source to the Excel sheet using PowerPivot. Hit the From DataBase option and choose your DBMS, like I chose MS Access in my case. You can also use the From Files option to get the data source from file or the From Data Feeds option to get the data source from Data Feeds.

PowerPivot AccessView in gallery

On the next step, specify the Connection name, Database, and login details for the database (If applied).

DataBase PowerPivotView in gallery

Hit Next and choose whether you want to select a list of tables or you want to write an SQL query to import the data. I prefer to select the tables from the list.

Import Table PowerPivotView in gallery

Now I will select the table from where I want to import the data.

Excel PowerPivot Table SelectView in gallery

Click Finish to complete the configuration and you will see the congratulation screen that your data has been imported from your specified source to your Excel sheet.

Import Complete PowerPivotView in gallery

Here click the Close button and you will find it linked to your Spreadsheet.

PowerPivot excel 2010View in gallery

Clicking the Refresh button refreshes the data from its source, and you can edit your existing connections from the Existing Connections option. The Paste from Clipboard and the Calculation categories simplify the data manipulation process. Once done with PowerPivot, hit the Switch to Workbook option in the View section to switch back to your main Excel sheet. The Table and Column menu on the top lets you play with the settings of the linked tables and its columns. Enjoy!

  • schaefer.gudrun@medinova.ch

    please show example to add external data from other excel file which is intended as lookup table. Download will be successful but relationsship does not work because of “multiple data”. What happens ther ? If I add teh same table via “create linked table” teh relationship works. However then I need to add all the look-up tables to each and every Powerpivot file and cannot use one look-up table for all.
    please explain and provide a solution.