What is PowerPivot?

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:

PowerPivot Snaps Into Excel As a Native Part of the Toolset

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:

Bill Jelen (Mr. Excel) Speaks His Mind About PowerPivot

Click to See the Rest of His Post

3. A Much More Agile & Cost Effective Approach to Business Intelligence

image

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:

  1. Part One:  Overview of PowerPivot’s Hybrid Approach
  2. Part Two:  A Cost Comparison of a Single Project Implemented Via Traditional Methods, Subsequently Re-Implemented in PowerPivot
  3. 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:  Excel Workbooks Turned into Industrial Strength Applications and Reports

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.

Picture of Normal PivotTable and a PowerPivot PivotTable

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:

image

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.”

How Do I Add a Formula Next to a Pivot?  In PowerPivot, You Just Add the Formula TO the Pivot!

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:

Formulas in PowerPivot Automatically Adjust When Your PivotTable Changes Size or Shape

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 Smile

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:

  1. Build a workbook. 
  2. Publish it to a secure server with one click. 
  3. Send the link to colleagues and/or partners and customers.
  4. 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.

PowerPivot Book Written Specifically for Excel Users, BY an Excel User

Click the Image for More Info, Sample Pages, and Reviews

55 Responses to What is PowerPivot?

  1. José Quinto says:

    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

  2. Mario says:

    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

  3. 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?

      • ymac says:

        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.

  4. Mandeep says:

    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

  5. Bryce says:

    Rob you know you’re my hero… but with all do respect your sizzle reminds me of Sizzler :-)

  6. Samuel MREJEN says:

    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 ?

  7. Kate says:

    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

  8. valerie says:

    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

  9. Thomas Abel says:

    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?

  10. Christopher says:

    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.

  11. Sunflowers says:

    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.

    • powerpivotpro says:

      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.

      • Sunflowers says:

        Thanks for the quick response. It’s impressive that these dashboards can be done without programming. I’m in.

  12. vaughn says:

    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

    • powerpivotpro says:

      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.

  13. Salim Virani says:

    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 ?

    • powerpivotpro says:

      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”).

      • Salim Virani says:

        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.

  14. Pau says:

    Hi Rob! Is it possible to use powerpivots without installing sharepoint? Thanks!

    • powerpivotpro says:

      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 :)

  15. Christopher says:

    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

  16. johanes salim says:

    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

    • powerpivotpro says:

      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).

  17. Jesse says:

    Hello,

    Is it possible to use SharePoint Foundation or do I need the full SharePoint Server 2010?

    Thank You,
    Jesse

  18. Vinoth Govindaraj says:

    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

  19. Vinoth Govindaraj says:

    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.

  20. Jasmine says:

    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

  21. Chris Policastro says:

    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!

    • powerpivotpro says:

      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.

  22. Yossi says:

    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

  23. brahamastra says:

    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 ?

  24. Vincent says:

    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.

  25. Alex says:

    Nice summary! I have been searching everywhere to understand if there is a setting in PowerPivot so that if you have a batch job updating your SQL tables if you can set PowerPivot to read uncommited(almost like a From dbo.### with(nolock). Is there some setting to set up PowerPivots isolation level so that it does not stop other transactions taking place in SQL?

  26. pmgottsc says:

    I run crazy formulas across over 1 Million cells and my computer takes 12-14 hours (at 100% CPU usage) to calculate all of them… typically I run INDEX(MATCH type formulas with cell references built in to copy over and down.
    Will PowerPivot help with the speed with which these formulas are calculated?

    • powerpivotpro says:

      Oh my goodness yes. Relationships are so much better (faster!) for data linkage than VLOOKUP and INDEX(MATCH()). Assuming your input data is in “unpivoted” format rather than cross-tab-style of course.

  27. Robert Mohammed says:

    Thanks! as a newbie to PowerPivot I’ve read all the comments above and I’ve got a couple questions of my own:
    1). How is it that Microsoft has offered this addin as free?…it’s so powerful!
    2). Where can I retrieve a previously created query (with a friendly name…) for a datasource connection?

    Regards,
    Robert M.

    • powerpivotpro says:

      1 – the idea is that you will eventually want a server, and they charge for the server. I think it’s brilliant, because things you ultimately end up WANTING (rather than being forced to buy) are a win for everyone.

      2 – Existing Connections button on the second ribbon tab in the Power Pivot window?

  28. Seth Strandin says:

    Hi Rob.
    I disagree with your description of Power Pivot being an extension to Excel. I have attempted to write my own definition, but it will need some tweaking to reach the top.

    “Power Pivot is a new and extremely powerful addition to the Microsoft Office Suite. It is an all new addition with its own formula language, data modeling and capacity to deal with extreme amounts of data while making it possible to analyze the information from basically any angle. Power Pivot offers all this in a very familiar environment – Power Pivot utilizes Excel as it’s interface! Make no mistake! Power Pivot IS NOT EXCEL, it is NFOLD more powerful, the Data-Analysis-eXpressions (DAX) formula language looks similar to Excel, but it is NFOLD more powerful. The Power Pivot Professional will be the most sought after professional in the next decade as more and more executives discover the advantages of having access to analysis based on Power Pivot, and each and every Power Pivot Professional will contribute to the growing demand for more Power Pivot Professionals.”

    All right, I have donned my hardhat and my protective jumpsuit so I am prepared for a barrage of tomatoes and looking forward to some exciting discussions.

    Best regards,

    Seth

  29. sandip says:

    ———————————————————————————————————-
    ———————————————————————————————————-
    Separate Power Pivot Data Model and Power Pivot Report from Single Workbook
    ———————————————————————————————————-
    ———————————————————————————————————–
    Initially, We have created Power Pivot report in workbook, containing Source Data Model as well.

    Now, we want to convert workbook having model as Shared Data Model, and all report needs to be part of single workbook.

    I want to implement one of the below solution, but know how to do it.

    Separate and Export all Power Pivot report sheet into single Excel Workbook such that it will have all report sheets.

    Use Same Model as it is and delete reports from existing one after export to new excel workbook.

    OR

    Is there is any other way to avoid re-creation of reports again? I want to use same report but instead of Embedded Excel data source, I want to use shared data Source in excel.

  30. zengujju says:

    Hello … I installed Office2010. I noticed that I don’t have the PowerPivot addin. So, when I tried to download and install, it gives me an error saying that I should install the right version. I have downloaded the 64bit but I still keep getting errors. Anyone run into this before? Thanks so much!

  31. Waldo says:

    Hello, I am working with Excel 2013 and PowerPivot and throws me the following error:
    “Could not get data from the data model, this error message is obtained. Invalid pointer array”

    Then when you click “OK” the following message appears: “. PowerPivot can not load the data model”

    Does anyone know the solution to this problem?.

    regards,
    Waldo.

  32. Andy says:

    Hello Rob,

    Thank you for the website, a lot of great stuff here! I have a couple of questions, one of the features you’ve showed that pertains to powerpivot is the high data storage capability. I’m curious exactly how much storage space does a workbook containing a data model in powerpivot which contains 160+ millions of rows takes up? I’ve tried to speculate, currently using the 32bit version of excel 2010 my data model with 5 million rows (and maybe 50 columns) takes up around 30 mg of storage space…with this as a base line, would a 160 million row model then take of 960 mb (30/5 *160 = 960)?! Note my data model only contains numerical and text values and has no calculated columns. Additionally the spreadsheet it self is completely empty, thus the 30mg is only coming from the data model. If my baseline logic is correct, how would sharepoint then handle such a massive document? If I’m recalling this correctly sharepoint has an upload limit of 250mb…how would one use sharepoint to share such a massive doc? Any advice you can provide would be greatly appreciated.

    Regards,
    Andy

  33. Randy says:

    Does time the Timeline filter in power pivot give you the ability to compare data from differnt years side by side or would I need to use a program like Access?

  34. Sir, i created a blog n want to show a blank sheet or table or excel sheet r google spread sheet in that blog to copy paste data into that table and button (macro recorded) just click and want to see result is it possible ? says:

    Sir,
    i created a blog n want to show a blank sheet or table or excel sheet r google spread sheet in that blog to copy paste data into that table and button (macro recorded) just click and want to see result
    is it possible ?

  35. poorvi says:

    Hello, Great info. I have a question about deploying Power Pivot files. We will have a master file that will have the query to retrieve all the data. But we want our users to create their own pivots – ultimate flexibility. However, when that query changes in the event of a new column addition – how do we deploy that to all Power Pivot users? They can get a new copy of the master file, but that means they have to redo all their reports. Suggestions? Thanks.

Leave a Comment or Question