fbpx
Search
Close this search box.

What is Power Pivot – an Introduction

Share

Facebook
Twitter
LinkedIn

Today, lets talk about Power Pivot & understand it.

What is Power Pivot?

Power Pivot is an Excel add-in to connect, analyze & visualize massive amounts of data.

Lets take a closer look at the definition.

Connect: You can use multiple tables of data & set up relationships between them using Power Pivot. For example, you can connect customer details to sales transactions so that you can summarize sales by customer location or gender easily.

Analyze: You can create simple pivot table style reports or create something exceedingly complex by defining your own calculated fields for values area of the pivot. There is a whole set of formulas defined for exactly this purpose, called as DAX formulas.

Visualize: Instantly filter your reports using slicers, time lines (Excel 2013 or above only), conditional formats, pivot charts etc. You can even define KPIs and see the performance in bands.

Massive Amounts of Data: Although your typical Excel worksheet contains a million rows, if you tried to load even half of those with any data, Excel would quickly become slow & lazy. Power Pivot can take a million rows for breakfast and would be hungry for more. It can processes millions of rows of data very quickly and easily, all from the comfort of a standard desktop or laptop.

Excel Add-in: Power Pivot is an optional free add-in that works with Excel 2010 or above. If you are running Excel 2010, you need to download and install it. Users of Excel 2013 have it easy. Power Pivot comes pre-packaged with Excel, you just need to enable it.

A brief introduction of Power Pivot

It would not be an understatement to say Power Pivot is the most awesome data analysis feature you will ever come across in Microsoft Excel. Since you would not take something short of awesome for this introduction, I recorded a 25 minute video explaining what Power Pivot really is and how it works. Watch it below:

[Watch this video on our Youtube Channel]

Looks interesting, what else can it do?

The video just scratches the surface of Power Pivot. You can do so much more by using & mixing various features of Excel & Power Pivot. Few possibilities are,

  • Define your own measures (calculated fields for power pivot reports) that can summarize data the way you want
    • Sum of sales YTD
    • Count of distinct customers per product
    • Ratio of sales made to single moms vs. single dads
    • % of products made by top 10 employees in all etc.
  • Slice and dice data anyway you want
    • Filter all reports & charts based on multiple linked slicers
    • Slice reports based on values in another (non) related table
  • Fetch data from different data sources & integrate
  • Set up Power View sheets to create rich visualizations of your data (Excel 2013 only)
    • Create maps, rich interactive charts & more
  • Integrate with Excel features to create stunning reports & dashboards
    • Combine Power Pivot outputs with Excel features like sparklines, conditional formats, charts & form controls
    • Use references to fetch pivot table values & present them in dashboards

and so much more.

How can I learn more?

If all this sounds interesting, you would enjoy our upcoming online course on Power Pivot. If you want to know more about our class, please enter your name & email below. I will update you as we make progress.

[Click here in case you are not able to see the sign-up form.]

More resources on Power Pivot:

Power Pivot is a vast & interesting area. Since this is a relatively new technology, many possible applications of it are emerging every week. For those of you starting afresh, I suggest below resources:

Do you use Power Pivot?

I first heard about Power Pivot in 2010, But I did not learn it then. Since October, last year, I have started learning and using Power Pivot and have been in love with it ever since. I think it is really powerful and capable. As I am learning new things about it, I am very eager to share them with you on this blog & thru our upcoming course.

What about you? Are you using Power Pivot? What is your experience like? Please share using comments.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

61 Responses to “What is Power Pivot – an Introduction ”

  1. willem says:

    I use power pivot as ideal connection between our databases and my reports.
    One of the most powerfull add-ins I have seen so far.

  2. Rahim Zulfiqar Ali says:

    YouTube is blocked in Pakistan, Pakistani viewers are unable to watch videos of Youtube. Can you please share some other link ? Or Download link please.

  3. Very nice tutorial Chandoo. In my company, I am able to bring in important data from the "customer" table and the "product" table using our IBM showcase query tool. The real power will be combining non-traditional data from outside our data warehouse.
    Ex: How do weather patterns effect sales? 
    Another key area where Power Pivots can shine is Digital Marketing. With so many touch points (Cell/Website/In Store/Email) there will be a huge need to link this data. How about a dual post with Avinash? 🙂
     
     

  4. Gijs says:

    Sadly our company is stuck at Excel 2007 - and no plans to upgrade.
    Are there any features of PowerPivot that can be used in 2007, or with an optional add-in?

  5. Oz says:

    Thanks for this. It was certainly mind-blowing AND you use an example that I get all the time.
    I've heard about PowerPivot since 2010 and made various efforts to find a use for it. Recently someone told me that it's great but it's not worth the trouble if you don't have a massive amount of data.
    What you showed isn't massive, it's more like a jigsaw puzzle. All the pieces are there, and it can be assembled with VLOOKUPs and helper columns but PowerPivot is much more elegant.
     
    I see!
     

  6. Vaslo says:

    This looks awesome, but one question - is there power using this if you can pull all the data into one sheet?  That is, what if my database HAS all the information I need, and I can just dump it into one tab?
    Thanks!

    • Hui says:

      @Vaslo
      If your data is of the size that it can fit in one worksheet and can be processed quickly enough then PowerPivot may not be for you
      Where PowerPivot starts to excel is in processing more than 1,048,576 rows of data or doing complex queries which take a while to process normally in Excel.

    • Chandoo says:

      Although you may be tempted to ditch PowerPivot in such cases, I still advice trying it for below reasons:

      • Dumping all data in one table often increases your file size and makes it slow. Power Pivot is built to handle even such data very fast & makes your files small.
      • Even when everything you want is in one place, you still need certain things like aggregations by hierarchy or time intelligence (group by month etc.). Such things are very tedious to do with formulas or pivots where as power pivot makes it easy.
      • Not all calculations are possible with regular formulas / pivots. DAX measures make it possible to construct almost any value for your reports.
      • You may have tough time publishing your dashboards / reports to web. Power Pivot makes this easy (of course you need Sharepoint or Excel webservices enabled server)
      • Slicers, time lines & other interactive features.

       

  7. Tim B says:

    Hi Chandoo, I have only this week been asked to upgrade my work pc from Windows XP to Windows 7 as well as install SSAS 2012, Office 2013 so that we can start to leverage the capabilities of Excel 2013, Powerpivot, PowerView, Sharepoint. 
    We have an Enterprise Data Warehouse including many disconnected data marts, sources etc.  We will be using these tools for express reporting solutions that with investment can turn into full enterprise reporting solutions.  The money saved in liscencing other products i.e Cognos to do similar at twice the development time does not stack up with what can be achieved with Excel PowerPivot. 

  8. peter allen says:

    Hi Chandoo. Love your newsletters, please keep them coming. Yours is one of the services that rank as 'Most Important' along with open source. Thank You.
    My question is if I add PowerPivot to my Excel 2010 at work. Can I still create macros to add to reports to send to people that don't have PowerPivot?
    My job is to manage data generated at a remote site and create reports that run automatically to present a filtered dashboard.
    Peter

    • Chandoo says:

      The correct way to share powerpivot reports / dashboards with end users is to publish them on a sharepoint server or similar. This lets your users interact with your data model thru slicers, timelines etc. and get the insights they want right from browser.

      I am not aware of a power pivot viewer (ala tableau viewer) that lets your end users use powerpivot workbooks with out the add-in (or Excel 2013).

      Another option is to use VBA in your computer to turn Power Pivot reports to static ranges or convert all power pivot tables to formulas (using Analyze ribbon > OLAP tools > Convert to formulas)

  9. purvi says:

    Hi Chandoo...We usually use an array formula on a large amount of data - since excel hangs we split the data into several files and then run the formula - do you think power pivot can help in this case in any way?  All the data is exactly the same and can be easily combined in one file - only deterrant is the fact that excel cannot process it that way

  10. Wow!  I love PowerPivot.  Thank you

  11. Please add me to the PowerPivot notification list.

  12. Brett Ellingson says:

    Are the 3 tables of data from the YouTube video available?
    I was scared or indimidated at least, to learn PowerPivot. Thanks for helping me see the light 🙂

  13. Jeremy says:

    I have started to use PowerPivot very recently, as I currently query our SQL server and store this data in separate 'excel databases' (just tables really) which are used for client reporting & dashboard reporting. However recently, due to the physical size of these files, I have had to look for an alternative (without using Access) - this is where PowerPivot came to the rescue. Love the fact that it recognises relationships between tables - currently I have had to force this with INDEX(MATCH(COLUMN)) or similar within the tables themselves (which doesn't help my file size issue!)
    Quite early days but it certainly looks like a vast improvement, and of course will be much quicker as excel will not be working as hard. 
    Looking forward to learning more about PowerPivot......

  14. Swami says:

    Wonderful tool and thanks for publishing it... awaiting for the course

  15. Zilla37 says:

    You showed how to link a power pivot file to an excel file. The more data added to the excel file would then be added to the power pivot file because they are linked. What if you imported the excel data into power pivot without linking the files. Is the only way to add more data to the power pivot file, is to import a new sheet tab of data to the power pivot file and create a relationship?
    I ask because a linked file I have is 14mb and non linked file is 1.4mb.
    Both power pivot files have the same data. I can see the ease of the linked files but the benefit of a smaller file.
     
     

  16. [...] week, you saw an Introduction to Power Pivot for Excel. Today, lets talk about DAX formulas & measures for Power [...]

  17. Andrew says:

    Any chance of being able to download the original data worksheet?

  18. Harish says:

    Hi Chandoo,
    how we will import data in power pivot 2010

  19. Sam says:

    Wow! Our company was considering buying an expensive data analysis suite like Tableau (which would have cost thousands of dollars), but this does everything Tableau does, and it works right inside of Excel.

    Thank you! You saved us a lot of headaches and money!

  20. Mahdi Haris says:

    Nice tutorial chandoo,it help me a lot
    by the way,can you give me excel file in these tutorial,so I can practice with it,please send to mahdi.haris@outlook.com

    Thanks very much

  21. Iqbal says:

    Im in Dubai we use dd/mm/yy format however poverpivot reads some dates as months and mnths as dates. Pls help me to solve this prblm
    Thanks.
    Iqbal

  22. edserra says:

    i am new in Power pivot , but i have many excel book with more than a 1,000,000 records
    thanks...

  23. […] so much more, that that’s a subject for another post. Fortunately Chandoo already wrote it: What is Power Pivot – an Introduction. (Chandoo, that title is way too descriptive. You’ll never make Class 1 Geek unless you learn […]

  24. shree says:

    Sorry for commenting on wrong post , I can't enable powerpivot to excel 2010. Please help me
    Thanks in Advance.
    Shree

  25. […] late 2012, I started learning PowerPivot. Although, PowerPivot has been around for a few years, I never used it well until then. I bought a […]

  26. Kdu B. says:

    Hi Chandoo, what a terrific feature it is!

    I was hoping to update my Excel version here at work and build some awesome dashboards using PowerPivot, but I believe that not just the authors but also the readers of a PowerPivot-based file must have 2010 or above version of Excel, right?

    Since I create stuff for multiple persons across the globe, that would be a expansive solution (#sad) that here, in this International Budget Maker (hope you get it), will never be accepted.

    Thanks for the tip, I'll definitely try it at home ;D

    Is there any way

  27. Zaf says:

    Can you please share the file you use in the demo so that we can try it by ourselves? thank you!

  28. Manjunath says:

    Hi Chandoo,
    I need in depth knowledge about Power Pivot. Can you help pls?

    Regards,
    Manju

  29. Faisal Shafi says:

    I found your video clip regarding Power Pivot very impressive but I'm confusing how to get this Add-In as I've Excel 2010 in the office and Excel 2013 in the home I've tried but failed kindly guide me.

  30. Kebaili says:

    Please upload the file that you used in the demo so we can practice thanks 🙂

  31. Mahaty says:

    Great insights on PowerPivot!! 🙂

    Thanks Chandoo

  32. Artieboy says:

    Hello Chandoo
    New to your website and power pivot.

    One quick question regarding connecting sheets in power pivot. It turns out, at least in 2010 version, that you connect sheets only with one field.

    Example I have two files that have badge #s and date fields. I couldn't set up a relationship using both fields. And because the same badge # appears on different dates I couldn't set up any relationship at all, as badge # by itself wasn't a unique field.

    I cheated by concatenating badge and day(date) in a new column. Then it worked 🙂

    Is excel 2013 the same?

    Just wondering...cheers 🙂

    • Chandoo says:

      Welcome to our website & thanks for your comments Artieboy.

      Even in 2013, you can only set relationships on single column. So you should continue with the concatenate approach.

  33. Jess says:

    It's worth noting here for the people who are having trouble finding the add-in, that the Power Pivot Add-in is only available in specific versions of Excel/Office.

    It is not available in Office Home or Home and Business - if purchasing now you'd need to go for at least Office Professional or Office 365 Small Business Premium.

    I believe the standalone version of Excel has it, too.

    Sadly I did not find this out until after getting excited by this article! Oh well!

  34. Brian W says:

    I was using PowerPivot with Excel 2010 extensively until 2015 when we upgraded to Office 2013.

    At that point, I was unable to open my source file due to "missing providers". I posted the problem on the Microsoft site and so far no one has provided an meaningful approach to recovering my files.

  35. Steven says:

    Hi
    Where can we download the demo excel?
    Thx

  36. Mr. Snrub says:

    Please send me the sample data workbook used in this video. Thanks.

  37. Abdul says:

    We have MS Office Standard 2010 [Excel, OneNote, Outlook, PowerPoint, Publisher and Word]. I have two questions. Can we install the add-on from the following page and is it free:

    https://www.microsoft.com/en-us/download/details.aspx?id=43348

  38. Kailash says:

    Dear Sir,

    We are using the Office 365 we need to enable the power pivote in msoffice 2016

    Please guide us

    Regards,
    Kailash

  39. Sumanta Das says:

    Its a wonderful explanation of the powerpivot. Could you share the sample excel file with us so that we can practice the same as you have taught.

    Regards,
    Sumanta

Leave a Reply