Pivot Table vs a Power Pivot based Pivot Table

This video is going to show you three big differences between an Excel-based Pivot Table and a Power Pivot-based Pivot table. The three differences we will emphasize are different calculations on Totals, Pivot Table Sets, and inserting a formula column into the Pivot Table. I find these the most influential differences between the two pivot tables but will discuss others along the way. You can also find resources to many more differences if you click the links below.

This tutorial was made using Excel 365, but all methods work with Excel 2010 and above.

Link to a blog post that are relevant:

Chapters:
0:00​ Excel Olympics Animation
0:08 Introduction
1:03 some info about Power Pivot
2:08 Introduction to the Data
2:36 How much space does it Use
3:51 Three differences emphasized in an Excel Pivot Table
7:17 Creating a Power Pivot Based Pivot Table
9:02 Connecting two tables in Power Pivot
9:36 Creating a Measure
10:46 Measure that calculates differently on Totals
13:09 Pivot Table Set based on Column Items
14:59 Inserting a Column with custom formulas in a Pivot Table
15:42 Convert to Formulas command in Pivot Tables
16:32 Using the Add this Data to the Data Model command
17:32 More sources on differences between the two Pivot Tables
18:14 A final comparison of file sizes
19:25 Conclusion
19:51 Outro

Gear Used:

US Links

EU Links



Disclaimer: Some links included in this description might be affiliate links. If you purchase a product or service with the links that I provide, I may receive a small commission with NO additional charge to you! I appreciate your support!

#Excel #ExcelOlympics #gasperkamensek

[davorercegovac]

Master black belt excel👍 Thank you Gašper🙂

[davidlinton]

Thanks Gasper, I always perk up when I see one of your new videos. I have been aware of power pivot but never really dug in, now I will. 😎

[jazzista]

Gasper.. that was a great refresher on PowerPivot. I use PowerPivot a lot in my office and like you said in the video one of the great features of PP is the asymmetric reporting capabilities thanks to the SSAS tabular running behind the scenes inside excel. I will go and read your article. Happy new year! Thanks for posting

[wayneedmondson]

Awesome Gasper! Was not aware of managing sets. Power Pivot really opens up new possibilities. Thanks for demonstrating. Looking forward to more! Thumbs up!! PS - Thanks for providing the sample data. Always fun and instructive to follow along :))

[Lena-ksni]

Thank you, with your videos, I always learn something new and interesting...keep on rocking, Gasper!

[JJ_TheGreat]

0:43 And I can name 2 of those 3 differences:
1) You can create a PivotTable from multiple worksheets with a PowerPivot. With a traditional PivotTable, you can only use data from the current worksheet.
2) With a PowerPivot, you get the option to calculate a Distinct Count. With a traditional PivotTable, you do not have that option.

[tracywilliams]

Wow! thank you, what a very informative demo. I stopped using excel a long time ago because of how restrictive it was and opted for a tool called Monarch (which I love) but now I’m heading back to excel using all the powder features and thinking Monarch and I could be breaking up real soon. 😅

[dougmphilly]

17:00 best description of the data table

[ExcelWithChris]

Only came across this today. Attended one of your workshop in Amsterdam in 2019. I see the link to the exercise files does not exist anymore. Where can I find the LARGE file. Want to use when training. Thanks. Best wishes from South AFrica.

[DK_]

Great. Thanks a lot! Are there new things to come, Gasper?

[edimathomas-crkm]

The OLAP tool "Convert to formulas" worked differently for me. It converted the power pivot to a range. Pls help. Been trying to multiply column Y by column Z in my power pivot table and have the result column be part of my power pivot, and I've been unable to do so. Super Frustrating.

[ExcelWithChris]

Sorry, my mistake. I see if I click on the link it starts downloading automatically.

[TheLaopi]

Great job from France, Can you share the data ? I'm doncic fan 👍👍👍

[mmbcampus]

Need one suggestion. In the raw data/source datasheet, I have one column for Birthday information in Date number format. Furthermore, Some data are missing and some cells are mentioned with non-number values (i.e "N/A", "Not update" etc.). Now the problem is when I add this Table into power pivot, Dates are stored as text. That's why I could not add any function. Can you please how to solve this?

[AlizSmith]

Wondering, what is the best way to combine 2 different datasets that has slightly different fields? I am trying to make a file that has actual sales for the closed months, but future months need to added from a forecast table to make my outlook.

[Maldive]

Hi there, this is amazing. I do have a question. i have already loaded 9000 records into the power pivot and built a dashboard based on it, it is a monthly report. but now problem the is, each month i have new sales data. in that case, how to just load or add the data to the table that 's already in the power pivot??? cause i tried many ways, it would just add the data to a different tab, like a new table, but that is not how i want it. please help. thanks in advanced.

[dougmphilly]

piqued my interests. i have huge report that uses power query into a pivot table but its size is 85mg and takes 15 minutes to refresh the data from the server. i'm going to translate all to power pivot.

[AUXA_]

I find power pivot always causes excel crash.😂