Microsoft PowerPivot for SharePoint

It is not a typo!  Microsoft PowerPivot for Excel has a twin, PowerPivot for SharePoint.  Before I tell you what it is; let’s review what you can do with PowerPivot for Excel.  You can now consolidate all you data into PowerPivot, define the relationship among them and extract information in lighting speed.  Isn’t it great?  The only problem is now everyone would come to you to get their information.  You become the central information hub!  Well, you can store the PowerPivot workbook in a shared network drive; so everyone can get the information they want.  But Microsoft have a more effective collaboration platform, SharePoint, which allows feasible controls and extensive collaboration features.  Here are some of the key benefits of PowerPivot for SharePoint:

Excel Services

Once you developed a PowerPivot workbook, you can upload it to SharePoint server.  SharePoint has an Excel Services that would allow CxO and other decision makers to use the workbook interactively and concurrently from their browser.  They can do every analysis that you do on desktop Excel.  They don’t even need to install Excel 2010 and PowerPivot.  The best thing is that you don’t need to worry if someone accidentally modifies your PowerPivot workbook which is securely stored in SharePoint Server.

PowerPivot Gallery

This is a new feature for SharePoint; PowerPivot Gallery is used to specially store the PowerPivot workbooks.  Decision makers can preview all the PowerPivot workbooks in thumbnail.  They can see if everything is green without opening any workbook.  Executives can find financial dashboard, sales scorecard and other KPIs all in a single page.


SharePoint is a very powerful collaboration platform which allows workflow processes to be triggered automatically by predefined criteria, e.g. if one of the marketing KPI is below certain value, then an investigation processes would be triggered.  When the investigation is completed and VP marketing approves the report, it can be linked to the KPI that initiates the investigation.

Change Management

How many times did someone modify you Excel workbooks that were stored on network drives?  You have no idea who did it?  Why it was modified?  SharePoint provides a good set of change management tools like check-in/out, collaboration, version control, approval workflows, email alerts when documents are updated or new documents posted, retention policies, etc.

Automatic Data Refresh

You can setup SharePoint to automatically upload data to the PowerPivot workbook in any specified schedules.  Financial and sales data can be refreshed daily and hourly respectively; product categories can be uploaded once a month.  The consumers of your PowerPivot workbook would always get the latest information.  If any upload process is failed, SharePoint can alert you immediately via email, SMS or pager.

PowerPivot for SharePoint is a natural extension of PowerPivot for Excel!  You prepare the PowerPivot workbook.  Once it is ready, you upload it to SharePoint Server and let the decision makers to get the information themselves.  PowerPivot for SharePoint provides managed, secured, self-service environment.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: