DAME 03: PivotTables Rule for Quick & Easy Reports! 34 Amazing PT Tricks.
HTML-код
- Опубликовано: 15 июл 2024
- Download files: people.highline.edu/mgirvin/A... Pdf notes to read online: people.highline.edu/mgirvin/A...
Alternative link for zipped folder: excelisfun.net/files/Video03F...
Free RUclips Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.
This video teaches you all the tricks for a PivotTable. It shows examples of when the PivotTable is the best tool as compared to worksheet formulas, Power Query, Power Pivot and Power BI..
Topics:
1. (00:00) Introduction
2. (00:26) Free PivotTable Cheat Sheet in workbook file or pdf notes
3. (00:39) Toy sales Data Set
4. (01:21) Why use PivotTables?
5. (01:58) Basics of PivotTables
6. (02:35) PivotTable Cache
7. (04:02) Change Default PivotTable Layout
8. (04:40) PT Calculations: Summarize Values By, Show Values As and Calculated Fields
9. (05:53) Cross Tabulated Tables and the AND Logical Test
10. (06:23) What a Filter or Sliver does to PT calculations
11. (07:45) Name PivotTable
12. (09:00) Connect 2 PT to 1 Slicer
13. (09:15) Calculated Field
14. (11:07) Sort PT by Values
15. (11:30) Extract records from a PT cell
16. (12:06) Create PivotTable Styles
17. (13:37) What happens if you copy a PT?
18. (14:45) Group By Inconsistent Data Error
19. (15:38) Fix Text Dates with Hack in worksheet
20. (17:07) Group By Feature for 1) Integers Numbers or 2) Decimal Numbers
21. (20:56) Grouping persists in the PivotTable Cache
22. (21:27) Create New Grouping in new PivotTable Cache using 3-step PivotTable Wizard
23. (23:21) Modify PivotTable Styles
24. (23:42) Show Values As Calculations: % of Column Total, % of Parent Total, % of Row Parent Total
25. (26:05) Show Values As Calculations: Difference From and % Difference From
26. (27:18) Show Values As Calculations: Running Total, % Running Total
27. (29:00) Summarize Survey Data
28. (29:41) Create Cross Tabulated Report and Visual
29. (31:20) Create and Use a Joint Probability Table
30. (34:42) Load 7 million rows of data to PivotTable Cache to make simple Pivot Report
31. (37:01) Append csv files into PivotTable Cache using From Folder and the C
32. (42:22) Create 5 reports with a single click: Show Report Filter Pages feature
33. (43:17) Summary
34. (44:11) Conclusion
At 25:15 I made a mistake: I forgot to change the aggregate Summarize Value By function from COUNT to SUM. So the percentages do no match up with earlier columns. I changed the result in the downloadable Finished file (solution file). The correct % values for the first year should be:
%Month/Year & %Year/Grand Total
9.01%
9.09%
7.66%
8.30%
8.04%
9.03%
7.45%
9.65%
7.94%
7.58%
7.67%
8.57%
34.72%
You are the greatest player in the RUclips Excel Categories
Just a guy having fun with Excel ; )
That was a complete review of pivot tables. Mike's learning resources are not only easy to follow, but they also go beyond the basics and are great for updating and improving our skills. God bless you men, you are doing a great job. Thank you for your efforts in providing us with this knowledge.
You are welcome for the complete review!!!!
Mike, with 100% Honestly, epic, or even Mythic does not even to begin to describe the true awsomeness of your videos!
I truly hope to be an excel teacher half as good as you are!
Firstly tho Ineed to settle in my new place..,
Thank you for the kind words, Fellow Excel Teacher!!!!
Thank you so much amazing Mike for this EXCELlent video.
You are welcome, fellow teacher!!!!
Excellent Mike! Pivot tables rule!
Yes they do!!!!!!!!
A lot of useful pivot table tricks. Thank you Mike 💚
You are welcome!!! No ghost in this one lol
Thank you so much for providing these numerous topics and notes at no cost!
You are welcome!!
Thanks Mike. Love these comprehensive videos, I can sit here for hours!!! :) :)
Glad you love them, Formula Guy John!!!!
thank you very much. i consider myself one of the pivot table/power query experts at work and there is plenty of stuff here that i did not know. those that don't educate themselves risk unemployment given the rise of AI.
Those who don't educate themselves risk unemployment is true. But I am not so worried about AI. They get technical stuff like what we do wrong a lot of the time, and REALLY stupidly wrong often too ; )
Great!! Very comprehensive summary of PivotTable options
Probably the most comprehensive summary of Pivot Table options ever made!! When PTs were first introdeced in the1990s they were truly revolutionary. Now they are just another Data Analysus tool. Still useful BUT with LOTS of competiton!
Lots of competition. It was revolutionary when they came out and it took 10 years for people to relive how amazing there are. Thanks to Lotus 1 2 3's Improv, which was the first : )
Hi Mike: Thank you again for a great, clear, powerful video. Every time I watch one of your videos it makes me wish I had been your student in college. (Guess In a sense I am.)
You are. That is why I have been posting for 16 years here. See you in the next class, Jim!!!
Very good Mike, for the first time I saw that regular Pivot Table can do calculations that are quite complex in DAX. For the Folder with CSV files I used "Combine and Transform" without the M code formula's and that worked as well ...😉
Yes indeed: the reason PTs are still relevant is because some types of calculations are so easy. Auto formatting is pretty good too : ) Combine and Transform is fast and easy. Even better if you are sure about structure: Combine and Load ; ) But, I get annoyed by all the query clutter and so often I just add that custom column and do it manually.
@@excelisfun Yes that is correct. I realize that this can be quite a lot (clutter), especially if you have more folders to use. I did not know your procedure and I certainly won't remember but I do remember going to DAME 03 😉
Awesome video. Thank you !!!
You are welcome!!!!!
Long Live Pivot Tables and Long Live Punk Rock!!! thank you Mike !!! ...wait... RAD Mike!!!
Long Live PT, PR, and Spilled Graphics!!!!!!
as for the filter and sorting - there is another "cheat way" but this can be applied to only 1 PT.
In your example set cell to P5 (attached to pivot table 1 row lower then the header) and press CTRL+shift+L - it will apply filter to PT headers.
Very good tip!!!
You can turn PT into Pure Treasure! 😉✌
: ) : ) : ) : ) : ) : )
16:30 this is great if thats the only problem with the data set, however I ran into so many different data set problems that I would simply go longer way around.
so first check if the column is number (with isnumber function) then Iwould add another column to see if +0 or -- fixes the issue. and I would inspect the results.
'Cos number 1 can also be displayed as date. So we have to verify if the date range has any reasonable sense for what we are actualy working on.
But I digress, my mind is automaticly finding issues where usualy there are none / few way less complex
Thanks for the tip!!!!
I shall finish watching this tomorrow 😊😊 , all set for the group by feature, which I have been known to get in a mess with. On the whole not a pivot fan, I like p pivot because of writing measures,
Shout out for the pivot table wizard hanging on in there.
Yes, this video has some very important grouping tips! Enjoy today and tomorrow!!!
Super useful tricks, tò use at work for sure
At work, at home, anywhere : )
@@excelisfun you are right :)
Pork Panko is a gamechanger!
Not sure what you are trying to communicate...
Kindly make a new separate Power BI playlist beginner to advanced including atleast one end-to-end Power BI project to showcase on a portfolio, much needed from you.
very good
me and My uncle are yours enormous fan
from Pakistan
Glad you and your uncle like this!!!!!
@@excelisfun Where are you live ?
@@AbdulRahman-yp6oy Seattle, WA, USA.
@@excelisfun Thanks Sir
Hi, I loved your Excel Statistical Analysia. But I do need help with percentile of grouped data. Any ideas?
Excellent video as usual wanna give you more than one like, but I cant :(
The video covors every trick in the book for PT which some were refreshing others were new to me.
Thanks for the >1 likes : ) : ) : )
What is the rule for Pivot Table Cache - do all pivot tables based off of the same data source always use the same cache by default? I thought it happens only when we copy an existing pivot table and adjust the new one that they are both using the same pivot table cache. I would be really thankful for clarification as I find many contradicting information on the web! Thank You and keep up the amazing work!
PS: I also found another thing. Minute 26. I wonder why the % of Month/Year Totals in two last columns are different. The Year totals do vary and that's fine but shouldn't the month level values stay the same? My Pivot shows the same percentages (and per my logic it should be so). I wonder what occurred in your case.
Yes: all pivot tables based off of the same data source always use the same cache by default!!! Just test it: Group, and then build new Pivot from data source and BAM: group persists and cache is the same every time you make a new Pivot. It is only when you access the 3-step Wizard that you can create a new cache.
Excel 2003 and before made a new cache each time.
What source on the web says otherwise?
Whoa!?!?!?!? Remember how I emphasized how to always check the aggregate Summarize Value By function before doing the Show Values As? When at 26:00 in the video, I forgot to change the aggregate function from COUNT to SUM. This is why the percentages do no match up with earlier columns. I changed the result in the downloadable Finished file (solution file). Thanks for the catch, and for helping the Team, mac!!!!
I pinned a note to the top so your observation will help the whole Team : )
At 42:35 I just wish to clarify one thing, we CAN use a slicer to filter the data, but the reason why you use the Filters part of the PivotTable builder is so that you can do Show Report Filter Pages correct? We cannot do Show Report Filter Pages with a slicer, correct?
Yes, the filed must be in filter area for that feature to work.
Could you focus on dynamic combining of “Tab separated values” in xls format with different columns in each sheet
I do not understand your statement. What does "dynamic combining" mean? Is that a formula? Tab separated values are usually in a text file, are they really in the old xls file format? What does "with different columns in each sheet" mean? Does it mean you want to load columns from a table, one to each sheet?
@@excelisfun yes they are in xls file format, and each file could have extra or missing columns, and I want to load all files data in one table
@@BaniMoniah I have never seen or heard of a tab delimited file in xls format. I have no experience with that. I wouldn't know where to use the Csv.Documnet file or the Excel.Workbook function... !?!?
I created a pivot table but it did not include all of the categories from the headers of the columns. It gave an option for more tables and instead created another separate pivot table. Is there a way to show all of the categories from the headers in one pivot table?
I do not understand your question. What does the source data look like, what is the structure, how many tables, what are the headers that are missing, what does "categories from the headers of the columns" mean?
It was one table with let’s say 20 columns of data but when I created the pivot table it only showed like the first 12 pivot table fields but not all of them
@@eddiemendez4766 I am not sure what the issue is, but it might be that the data going into the PivotTable cache only grabbed the first 13 columns. When you create a PivotTable, you can verify in the dialog box if the range for the source data is correct.
where is source data and notes for download
links are below video.
Sir, please, where are the files for download?
Sorry about that. It F5 to refresh the browser and you will see the files I just posted : )
@@excelisfun Thanks Sir. I love all your work. Your are a great teacher. 👌👌