How to Install PowerPivot & Connect to your first SharePoint Lists & Libraries

PowerPivot as a great BI tool offered by Microsoft which can handle millions and millions of records with ease and supports table relationships.  I have been asked why PowerPivot just does not come OOB with Excel and that is because of the particular Hardware & Software requirements which are needed.  If you are not sure what the requirements are, read this MSDN article which outlines exactly what you need.

http://msdn.microsoft.com/en-us/library/ee210640.aspx

This post assumes that you have the requirements and are ready to install PowerPivot

To Install PowerPivot for Excel 2010

1.    http://www.microsoft.com/en-us/download/details.aspx?id=29074

2.    Select download

3.    Select the download for your current system

a.    X86 is for a 32 bit system & x64 is for a 64 bit system

4.    Run the installer after the download has completed

5.    Open Excel 2010

6.    Select File Tab

7.    Select Options

8.    Select Add-Ins

9.    Next to manage select COM Add-ins from the combo box

10. Select Go

11. Check the box next to PowerPivot for Excel and select OK

You should now have a tab on the ribbon for PowerPivot!

Enable PowerPivot for Excel 2013

 

1. Go to File →Options → Add-Ins.

2. In the Manage box, click COM Add-ins> Go.

3. Check the Microsoft Office PowerPivot for Excel 2013 box, and then click OK. If you have other versions of the PowerPivot add-in installed, those versions are also listed in the COM Add-ins list. Be sure to select the PowerPivot add-in for Excel 2013.

You should now have a tab on the ribbon for PowerPivot!

Connecting To Your First SharePoint List

1.    Open Excel

2.    Select the PowerPivot Tab

3.    Click PowerPivot Window

4.    On the Home Tab

a.    Select From Data Feeds

5.    You will be prompted with the Table Import Wizard

6.    Friendly Connection Name can be anything your heart desires, it is just the display name of the connection

7.    Data Feed URL is what actually enables you to connect to the list

a.    Input your site URL i.e http://domain/sitename

8.    At the end of your site URL you will need to have:

9.    /_vti_bin/ListData.svc in order to connect to the list

10. The final URL should look like this http://domain/sitename/_vti_bin/ListData.svc

11. Select Next

a.    If the connection was successful, you should see a list of your lists and libraries

12. Check the box next to the lists / libraries you would like to create reports on and select Finish

From this step on you can create your Metrics using Pivot Tables & Charts.  There are many great tutorials online regarding how to create the relationships amongst lists & how to use PowerPivot effectively, but I may release an article in the future.

Cheers!

Dan