Using PowerPivot for SharePoint 2010

PowerPivot applications are created using Excel 2010 using PowerPivot add-in,  You can read my post on configuring PowerPivot in SharePoint 2010 here. After creating a PowerPivot application then you need to upload it to SharePoint 2010 PowerPivot gallery so that it can share across organization.


You should be able to preview the PowerPivot applications once you upload them to PowerPivot gallery and it is more user engaging. Silverlight needs to be installed to use PowerPivot gallery. You can get three different presentations in PowerPivot Gallery

  • Gallery View
  • Carousel View
  • Theater View

You can select these view from ribbon as shown below


Gallery View

It lists the preview of file along with smaller thumbnail view of each sheet in file. when you hover thumbnail primary view changes to show that sheet. When you click the thumbnail then it launches the PowerPivot viewer and loads the file full screen for interactivity.


Carousel View

It is a rotating presentation and eliminates the scrolling, Only one PowerPivot file is shown at a time and when you click the file then it launches the file in the PowerPivot viewer.


Theater View

In this view you can see the sheet in full screen in size and a series of thumbnails across the bottom of the screen.


PowerPivot Gallery is more intelligent than previewing static thumbnail views. It can refresh the data in PowerPivot workbooks to keep them current.

You can notice the two icons on the top right corner in the PowerPivot view as shown above, the icon which looks like calendar is the manage data refresh function.

When the workbooks in gallery gets refreshed , it takes new snapshot of each sheet for the gallery.

Publishing Workbooks  

Using Upload function in PowerPivot Gallery you can upload work books to SharePoint, Alternatively you can use save as option in Excel or you can use save and send option for publishing workbook from excel.

Easier option is uploading the workbook within SharePoint PowerPivot Gallery


One of the biggest benefit of using PowerPivot is ability to give end users to build analytical applications. To prevent the users from downloading the workbooks and to control the data exposer to users, you can add the users to SharePoint group named “Viewers” in Excel Services which still allow the users to change the slice rules but prevents from downloading the files.

Using the Health Data Collection service in SharePoint 2010 you can analyze the PowerPivot data usage and query usage. To Launch the PowerPivot Management Dashboard go to SharePoint Central Administration –> Application Settings then PowerPivot Management Dashboard.


PowerPivot enables end users to create powerful analytic applications and can be accessible across organization in controlled way.

Share this post :

%d bloggers like this: