No single answer addresses this question completely, so I will give you a few different answers, and try to keep each one ultra-brief.
Keep in mind that all of these come from my own firsthand experience over the last two years – I am a practitioner of PowerPivot, not a salesperson. My partners and I at our startup company, Pivotstream, have built a very successful business applying it fulltime. I like to say that I preach what I practice, not the other way around.
1. It’s a Free Extension to Excel 2010, Built by Microsoft
Yeah, it’s free, and it’s from Microsoft, as opposed to a third party. It dramatically extends the powers of “normal” Excel, and more importantly, the powers of people who use it: people like me, and probably you too if you are reading this.
It snaps right into Excel as if it had been there from the beginning:
You can download it here, from Microsoft, and try it out.
2. “The Best Thing to Happen to Excel in 20 Years”
Even though I agree 100%, that’s not my description. That comes from Mr. Excel himself, Bill Jelen. He’s said this a number of places, but here he says it on the MrExcel.com forums:
Click to See the Rest of His Post
3. A Much More Agile & Cost Effective Approach to Business Intelligence
PowerPivot Offers the Low Startup Cost and Agility of Spreadsheets
Blended with the Robustness and Low Ongoing Cost of “Traditional” BI
For more information on how PowerPivot accomplishes this, see the following three articles in CIMA Insight, the monthly newsletter for the Chartered Institute of Management Accountants:
- Part One: Overview of PowerPivot’s Hybrid Approach
- Part Two: A Cost Comparison of a Single Project Implemented Via Traditional Methods, Subsequently Re-Implemented in PowerPivot
- Part Three: Total ROI Analysis and Breakdown of How the Savings Are Achieved
4. Something That Turns Workbooks Into Industrial Strength Apps
Check this out:
PowerPivot Workbook – Click for Larger Version
Slick and interactive. And very straightforward to produce. My company Pivotstream has built literally thousands of such applications for more than one hundred different clients. And we are an organization of Excel pros, not programmers.
5. An EXTENSION to things you ALREADY KNOW about Excel
It adds a lot of power to Excel, yes, but it doesn’t force Excel pros to learn a million new things. It’s still PivotTables. It’s still Excel formulas. It just adds a number of important new capabilities that Excel pros will never put down once they have tried them.
I like to say that PowerPivot’s new capabilities “rhyme with” the things already in Excel.
On the Left: A Normal PivotTable. On the Right: A PowerPivot PivotTable.
Can You Spot the Difference? (Click for Larger Version)
6. A Way to handle Massive Volumes of Data in Excel
Normal Excel caps out at 1 Million rows of data per sheet. PowerPivot goes well beyond that and for most people, you will find it actually HAS no practical limit.
For instance, here’s a workbook I like to demo that has 161 Million rows in a single sheet:
Special Sheet Type Can Hold 161 Million Rows
(Another Sheet in the Same File Holds Another 150 Million!)
That workbook, believe it or not, works just fine on my ultralight, 4GB of RAM laptop.
7. A Way to Add ANY Formula to a Pivot, and it Adjusts!
One of the most common desires in pivots: “I want to write a formula next to the pivot, and have that formula adjust when I add/remove fields.”
This “Pct Change Versus Last Year” Formula Could NOT Be Added to a Pivot in Normal Excel,
and Would Have Been Hard to Write Even OUTSIDE of the Pivot.
Simple in PowerPivot Though.
That in itself is pretty cool. But then when I rearrange the pivot, the formula just rearranges with it:
Rearrange the Pivot and the Formula Just Does the Right Thing
For more details on this, see this article on how PowerPivot makes your formulas Portable.
And for more details on points 4-7 above, see this article on the Top Five Ways in Which PowerPivot Helps Excel Pros.
8. A Way to Turn Workbooks Into Self-Maintaining Web Applications
That’s a PowerPivot workbook – Saved to the server and then viewed in the browser,
the user of this web application doesn’t even need Excel installed.
CLICK THE IMAGE TO TRY IT OUT
I think this article in CIMA says it best, but then again I am biased because I wrote it 
Here’s a quote from it:
“There are many benefits of PowerPivot that have to be seen to be appreciated. Of those ‘you have to see it to believe it’ benefits, the one that evokes the most visceral reaction is this:
PowerPivot workbooks are self contained web applications, merely requiring a ‘Save as’ to make them accessible in the browser as interactive solutions.”
That’s right:
- Build a workbook.
- Publish it to a secure server with one click.
- Send the link to colleagues and/or partners and customers.
- Schedule it to regularly refresh itself with the latest data. You never have to touch it again, unless you want to improve it.
Wrapping Up
I think that’s enough answers for now. I encourage you to visit the New to the Site? page, and send me any remaining questions in email. I am Rob. At a place called Pivotstream. Dot com.
Hi Rob,
What happen if I upload to SP2010 site (that hasn´t installed PowerPivot for SharePoint) a PowerPivor workbook with a slicer. And then I open with excel services and change the slicer?
1. Can I upload a PowerPivot file in this system?
2. Excel Services show slices dinamically or static?
Thanks
Hi José,
This unfortunately won’t work. The PowerPivot gallery is a different sort of document library. When you call a slicer in this library the data will be loaded to the MSOLAP server which runs in memory on the server.
So you have to run the SharePoint PowerPivot services to execute PowerPivot files on SharePoint.
Kasper
Hi all, thanks for this great site. It has really helped my. I had some questions about hardware. I have a team of about 6 analysts that I need to get new hardware for and wanted to know what the best spec would be in order to use powerpivot and just deal with massive data sets in general. i need to stick with XP so I assume a 64 bit version with 5 or 6 GB of ram? Anyone have some dream machine specs in mind? Out current setup of 32 bit, 2Gb of ram is painfully slow.
Thanks
We are putting up our first SSAS cube at our really large hospital system.
We have two or three (depends on how you count) dashboard tools in place, so the integrated data from our ODS will be great, but really won’t add any new features to our performance monitoring. It will give more depth and breadth.
Our doctors and researchers have no ad-hoc query facilities, and were/are looking forward to Proclarity and the ability to do pull reporting (consider metrics and dashboards a push for this question).
Your screenshot above looks like the Gemini demo I got from Microsoft in January. Every demo, picture, discussion I have had since focuses on powerpivot’s integration with Sharepoint for metrics/dashboards or some really weird v-lookup thingy for power users.
As someone looking at the Proclarity successor, where would you point me for more info on these features in Powerpivot?
Hi Paul. I need to understand a little better before I can answer. Are you looking for an ad hoc query tool to use against SSAS, or against other sources?
Are you looking for a cube browser on sharepoint 2010 ?.
I am looking for the same feature as you but I can not find it.
It seems that to find a web application that allow to browse a ssas cube like the owc pivottable component allow to do it we have to search for a third party tool like dundas or http://ranetuilibraryolap.codeplex.com/. RRrhhh it makes me so frustated to say to all end-users they can not play with data as it was the case with the owc pivot control.
I know microsoft will tell me POWERPIVOT is the solution.
Ok, But How do I allow end user to create powerpivot sheet without excell 2010 ?
So, Microsoft will tell me: you ask your poweruser/key user (meaning more advanced business users) to create the powerpivot sheet and publish it to sharepoint 2010 with powerpivot and so end user can see powerpivot sheet and play with the data without having excell 2010…. Heuuurk , no.
Powerpivot for sharepoint does not allow to browse cube data and play with axes as we usually do it with the owc pivotable component.
What do I have to propose to my end users/business users so they can browse the cube and play with the data inside the cube ? pps … no … because it requires silverlight and my client does not allow silverlight to be installed … but this is another story.
Hi Paul,
Just want to clarify that if Powerpivot excel sheet is published using Sharepoint 2010, then users need not have excel on their PC’s??
In other words Excel 2010 is only required to develop Powerpivot sheet, but once it is published using Sharepoint 2010, users don’t need Excel 2010 locally. and they can still play with slicers and dicers.
Regards,
Mandeep
That is correct, excel is not required on the desktop for users who are consuming SharePoint PowerPivot reports. Just a browser.
Rob you know you’re my hero… but with all do respect your sizzle reminds me of Sizzler
Thank you for PowerPivotPro “institution” it is really good and enriching
I have a few questions/Request for enhancements
1. in powerpivot Fields TableList, a small place is dedicated to field name so the table name is ommited so i suggget to include the full field name (Table and field) in a toolTip for each of the six filter positions(slicer, report, column, lines etc..)
2. Where i can find , in powerpivot, the relation diagram between tables as in Access ?
Hi Rob
I’ve been reading your posts for the last 2 days, which is also how long i have known about PowerPivot. I’ve seen you say a few times that you are finding slow adoption among excel users. I just wanted to say, as an excel user, that this is the coolest thing i have ever seen and i think it may revolutionize my life. Seriously. I can’t wait to dig in!
Thanks,
Kate
Hi,
I am an experienced Excel user and a new PowerPivot user. While I am using Excel 2010, my company has not upgraded to SharePoint 2010 – we are still using 2007.
One of the benefits of PP you noted above is “1.That same report, published as an interactive web app, with a single click ….”. Since our SP is 2007 rather than 2010, do I have other options?
Thanks much,
valerie
Hi,
the powerpivot tool is great, but is this tool only for excel 2010 or have I the chance to use it for the 2007 version?
Excel 2010 is required, sorry.
I just added this site to the Blog page on the Directory of Excel Experts as everyone needs to see this site. This is the best site for PowerPivot I have seen. I hope that it gets you traffic once that site is slowly launched.
Rob, you say, “we are an organization of Excel pros, not programmers.” Are you able to do most (all?) of your PowerPivot dashboards and tables without programming? I really like analytics, but I’m not a programmer and don’t want to be. It seems like I could get very far in PowerPivot without programming.
You absolutely do NOT need to be a programmer to use PowerPivot to great effect. We don’t require any programming to build any of our dashboards.
Thanks for the quick response. It’s impressive that these dashboards can be done without programming. I’m in.
hi,
I’ve been playing around with pwrpivot and have a question. how do add one large spreadsheet to another large spreadsheet – when combined exceed the 1 million row limit of excel 10 – so I can use in powerpivot?
Thanks,
Vaughn
Hi Vaughn. I assume you mean you have multiple large tables, each stored on its own worksheet (or in its own workbook). The tables all have the same columns.
If so, save each table as its own CSV file using Save As, choose file type CSV.
Then go into a Command Prompt (resembles DOS from the old days), go into the folder where the CSV’s live, and type the following command:
copy *.csv combined.csv
then you can import the combined.csv into PowerPivot.
I recommend going the CSV route because large tables cause problems with Copy/Paste.