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%
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.
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 : )
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 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 ; )
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 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 😉
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.
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.)
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.
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
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!!!!
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.
Thanks alot Mike! It is an excellent reference video for me! I have a question. In the video (16:50) you have shown that Pivot Table can group Dates into Year, Quarter etc. Can it be done for six monthly period? [Like from 01 Jan to 30 Jun and from 01 Jul to 31 Dec ] Thank you once again!!!
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?
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?
@@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.
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%
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!!!!
You are the greatest player in the RUclips Excel Categories
Just a guy having fun with Excel ; )
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 : )
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 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 ; )
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!!!!
Excellent Mike! Pivot tables rule!
Yes they do!!!!!!!!
Thank you so much amazing Mike for this EXCELlent video.
You are welcome, fellow teacher!!!!
A lot of useful pivot table tricks. Thank you Mike 💚
You are welcome!!! No ghost in this one lol
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!!!
Long Live Pivot Tables and Long Live Punk Rock!!! thank you Mike !!! ...wait... RAD Mike!!!
Long Live PT, PR, and Spilled Graphics!!!!!!
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 😉
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!!!
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!!!
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 : ) : ) : )
19:45 seems to be a poor oversight by Microsoft. Nice video.
I feel like a pivot table expert after that 😅
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!!!!
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 : )
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
Pork Panko is a gamechanger!
Not sure what you are trying to communicate...
Awesome video. Thank you !!!
You are welcome!!!!!
Super useful tricks, tò use at work for sure
At work, at home, anywhere : )
@@excelisfun you are right :)
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.
You can turn PT into Pure Treasure! 😉✌
: ) : ) : ) : ) : ) : )
Thanks alot Mike! It is an excellent reference video for me!
I have a question.
In the video (16:50) you have shown that Pivot Table can group Dates into Year, Quarter etc. Can it be done for six monthly period? [Like from 01 Jan to 30 Jun and from 01 Jul to 31 Dec ]
Thank you once again!!!
I will make a video for you and post tomorrow : )
A formula like this might work:
=YEAR(E5)&" Part "&(MONTH(E5)>6)+1
or
=YEAR(E5)&", "&IF(MONTH(E5)>6,"Jan 01 - Jun 30","Jul 01 - Dec 31")
The video will be posted for you tomorrow morning : )
@@excelisfun That is awesome Mike!
Thank you for your response and kind support.
Hi, I loved your Excel Statistical Analysia. But I do need help with percentile of grouped data. Any ideas?
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. 👌👌