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 three years – I am a practitioner of PowerPivot, not a salesperson. My partners and I at my former company built a very successful business applying it fulltime. I like to say that I preach what I practice, not the other way around.
***NOTE: If you’d like a jumpstart on maximizing the benefits of PowerPivot, one that takes a very “commonsense” and visual approach, this book is written specifically for the 30 million heavy users of Excel worldwide and aims to make your adoption/transition smooth and profitable.
1. It’s a Free Extension to Excel 2010 and 2013, 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.
PowerPivot is now also included in Excel 2013 as well – the new “Data Model” features of pivots in 2013 are really just PowerPivot, and the remaining functionality of the PowerPivot addin can be enabled with a single checkbox (under Addins).
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, using only Excel. No programming expertise required.
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.
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 PowerPivotPro. Dot com.
The PowerPivot Book
If all of the above seems promising to you but also a little daunting, I encourage you not to worry. ALL of this is very learnable, and you can do so at your own relaxed pace. For three years I’ve been teaching Excel users all over the world how to use PowerPivot – both in person and on this blog – and I’ve condensed that approach into a very affordable (<$20) and compact book.
Click the Image for More Info, Sample Pages, and Reviews
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.
Hi:
I have question. I would like my powerpivot to be portable, and it uses CSV as data source. So people should be able to move both the XLSX and CSV in a different folder. But once the path is changes, powerpivot can no longer find the CSV. If there was one CSV, or a couple, it should be fine. But the dashboard we are thinking of implemeting would have tens of CSV and changing the path for each one of them everytime seems quite manual and should have an elegant solution.
Any suggestions ?
Hi Salim. Yeah, this is something I wish PowerPivot were better at – changing connections.
Is it feasible for you to import all the CSV’s into Access first? Then you’d only have one connection to change (each CSV would be one table).
We have some hacks for editing the PowerPivot files themselves, whether these would work for you depends on what type of user you expect to be able to change the connections (the hacks are a bit “techie”).
Thanks. The Acces idea is good, provided importing of all the CSV’s into access can be automated. I have not done enough research on that topic except for manual import or using CSV as external tables, and have not figured out how can the path change be automated in Access.
We are a bunch of analyst with strong development background, so hacks are pretty cool
Please share if you have some ideas.
Could you not use a tool like WinZip to create a compressed archive with folder_names to capture all of the files and their locations, and then distribute that to you users. Upon “unzipping” the file, the same folder structure would be created on their local PC. I think Excel/PowerPivot would then be able to find the files its referencing.
- .CSV files (text) are HIGHLY compressible
- Yes, this would create as many “installations” of your PowerPivot data files as you have users (a maintenance headache!)
- But it would be easy to “redistribute” an updated installation of changed files, simply overwriting their old versions.
Hi Rob! Is it possible to use powerpivots without installing sharepoint? Thanks!
Absolutely. If you have Excel 2010 on your desktop, you just download the free addin and you’re off and running.
If you want the server features, like secure publishing, giving users interactive apps without requiring them to install PowerPivot (or even Excel, or Windows for that matter), scheduling automated refreshes, and/or making your analyses available on the Internet, then you either need to install SharePoint or sign up for a cloud site like what we offer at Pivotstream.
Lots of options to use PowerPivot without installing SharePoint
THANKS!
I am so looking forward to using PowerPivot in Excel 15. I also look forward to what you will put on this site, the best site for PowerPivot bar none.
Please keep adding such powerful content.
Thank you,
Christopher
hi, i’m a newbie, if i create a file in power pivot, can it be viewed by other users by using excel 2010 w/out sharepoint?
thank you
Yes, it can be viewed interactively by others, in Excel 2010, as long as they also have PowerPivot installed.
If they don’t have PowerPivot installed, they can still open the file and view the pivots, etc. – but the pivots won’t be interactive (slicers won’t work for instance).
Hello,
Is it possible to use SharePoint Foundation or do I need the full SharePoint Server 2010?
Thank You,
Jesse
Hi Rob,
I have created some power Pivot reports ( graphs with data) for our departmental KPI but for the next stage i want to display to all, in our company we have SP 2007 only, is it possible to display on that or we need to upgarde to SP 2010. also suggest me is there any alternative option available to dipaly without having SP 2010. else please advise can we use other server.
regards,
Vinoth Govindaraj
Hi Rob,
I heard about Cloud power pivot which wil eleminate installation of sharepoint 2010, but my question is if we have SP 2007 is it possible to use else we should go for cloud power pivot.
awaiting u r reply.
Hello Rob,
I have upgraded to Windows 8 and trial version of Office 2013. I want to upgrade a table created in Excel 2010 to Powerpivot in 2013. When I click on Data > Manage. I get the message that Powerpivot is unable to upload the data model. Any help in this regard will be highly appreciated. Thanks
Hi Jasmine
Post this over at the MrExcel forums if you would, more people will see it there. My first question is whether the workbook was created with PowerPivot v1 or v2, by the way.
http://www.mrexcel.com/forum/powerpivot-questions/
Awesome Site! I have a question about using PowerPivot with other SharePoint Services: Suppose that, within a single SharePoint site, I have an Excel workbook that is published via Excel Services, an Access Database that is published via Access Services, and a PowerPivot file published to the PowerPivot gallery. Can I use the published Excel and Access files as data sources for my PowerPivot file?
Thanks!
Hi Chris. I believe all Access Services db’s are SharePoint Lists under the hood, and all SharePoint lists are available as data feeds – which PowerPivot can directly consume.
The Excel workbook published on SharePoint, by contrast – I do NOT think that can be used as a data source. 99% certain. But a workbook on a normal file share COULD be used.
I have just upgraded from office 2010 to office 2013 Professional and there’s no PowerPivot add-inn in it ( the “COM add-ins” is empaty and I also looked for it on the disabled items and it’s empty too).
my office.my office version number is 15.0.4454.1004.
PowerPivot is great but if it’s not on 2013 then I downgrade to 2010
Hi Yossi. Microsoft is only including PowerPivot in certain “flavors” of 2013. I know that it IS in “Pro Plus” but I guess it is not in “Pro.”
I wish this weren’t the case, but I believe it is.
Others seem to be successfully getting refunds for 2013 versions that lack PowerPivot:
http://www.mrexcel.com/forum/powerpivot-questions/683298-cant-enable-powerpivot-excel-2013-a.html
I am to purchase Home and Student version of Office 2013. Is PowerPivot included in that version or not ? Do I need to pay separately for that and whether it is separately available ?
Nice summary of PowerPivot! I forgot about the web application feature. I’d like to use that feature. Sending 20 Mb Excel files through emails seems so 2003.