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.
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.
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.
On the next step, specify the Connection name, Database, and login details for the database (If applied).
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.
Now I will select the table from where I want to import the data.
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.
Here click the Close button and you will find it linked to your Spreadsheet.
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!