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

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.

PowerPivot Window

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 Access

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


DataBase PowerPivot

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 PowerPivot

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

Excel PowerPivot Table Select

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 PowerPivot

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

PowerPivot excel 2010

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!

Advertisement
  • 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.