Yes, Erica, and it is so helpful that the PivotTable can do these tricks, as compared to the many other more complicated methods. Each has there place, though. Thanks for your support : )
@@EricaDyson Yes you can thank me enough: just a comment and thumbs up on each video. Since the vast majority of viewers do not complete this simple task, when you do, it is like a HUGE and meaningful Thank You : ) : )
Spooky you should post this video this weekend; it's a pivot table function I rarely use but have used it 3 times in the last week for quick reconciliations. Thanks Mike.
ExcelIsFun I know but it was funny that when it first came out, I was excited but some of my co workers and higher ups didn’t like because they couldn’t see how it worked - where are your formulas? Now it’s the opposite - what else can you do?
ExcelIsFun whoever invented the pivot table revolutionized financial reporting and analysis. That programmer should be enjoying the good life on a cayman island beach by now.
@@dougmphilly It was programmers at Lotus 1, 2, 3 back in late 1980s early 1990s. They sold it as a separate program called Improv. Then Microsoft copied it and gave it away for free in Excel in 1990s.
Thanks Mike.. another excellent lesson.. fun with Pivot Tables. Thumbs up! PS - Just for fun, you can get auto updating of a Pivot Table with a simple macro on the Worksheet_SelectionChange event of the subject worksheet as follows: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("B:C")) Is Nothing Then ActiveWorkbook.RefreshAll End If End Sub The above will force a Refresh All with any changes made in column B or C.. so.. makes the Pivot Table dynamic with the help of a little VBA. Might create a performance issue for large data set. But, it's an option for certain circumstances.
The PivotTable is so very easy : ) Thanks for your support, Ogwal, and try the Teespring and see if you can get a Mug or shirt : ) Let me know if they do the shipping you want.
Hi Sure pivot tables are useful; but in a scenario like this; I prefer IFS variant functions for their ability to instantly update the summary. In pivot as you correctly said if the user forgets to refresh, the pivot table will carry the same old result. Thanks for sharing video. I have a request will you please post a video on LOOKUP (ARRAY and VECTOR form) function or if you already have please share the link.
Yes, formulas update instantly - and it is the only feature that does that : ) Yes, I have many videos on the marvelous LOOKUP function. Here is a great one: ruclips.net/video/N24cBM-CTw4/видео.html
Those pivot table features sure are super-handy! Although, I’m not used to those features anymore since I make all formulas as explicit measures. Where is the time when all data came in just one table?... doesn’t seem to occur anymore. Since I need that function so often the last months I’ve put the Refresh All button on my QAT-walk. Because: pushing that button will refresh all pivot tables in my dashboards at once. BTW, Mike is there any reason why you did these two videos? They seem like re-iterations of previous videos to me (I’m sure you’ve done this before - must be, right?...).
If you download the Excel file for this video you'll see it applies for EMT 1556 to EMT 1560. So it is the same problem just solved differently each time. I think Mike said that he's going to do some bite-sized videos which I'm sure is what he's doing here. The focus on one technique in a video is probably good as it helps most of us to focus on just the technique in question. Too many methods presented all at once can be overload for some people, especially if you're not comfortable with what's being discussed.
Just changing it up for fun. Sometimes we do it all in one, sometimes in multiple videos. Plus, I am just trying to do Excel Quickies right now ; ) Also, if you do have it all in one table, as so many humans on the planet do, then this it just a great helpful tick : )
@@brianxyz You are right, most RUclipsrs just want a quick solution; they do not want the many methods and comparisons to help get the full understanding.
@@brianxyz brianxyz and Geert, I have done over 3000+ videos, and I have done the videos in many, many different ways and different formats. Some short and to the point, and some in depth and with all the details. The truth is, that so far in RUclips's history, the short and to the point videos get the most support and views. But much of what I have done at RUclips has gone against that grain, because I want to make videos that cover the hows and whys and full details in all directions possible. My long and detailed videos that cover the full topic and go on for an hour or two (what I like to call one-stop-shopping), are rarely watched in full or promoted by the RUclips algorithm. There are few other resources on the internet that have the full story like I like to tell. But the truth is that most humans that come to RUclips (and I think most humans on the planet) do not want this. Since I have posted most of what I know in full detail format, I figured I should do some quick and to the point shorter video for this summer : ) If I can keep sustaining the energy after 11 years, I will keep making these Excel Quickies and then a few more full detail classes, or more : ) : ) : ) : )
@@excelisfun For problems that involve a lot of steps and/or a deep understanding of the techniques used, the longer videos are ideal and much appreciated. Watching a two hour or so long video numerous times might take up a lot of time but in the process you learn so many things that it's worth it in the end. Your use of time stamps is a lifesaver for when one needs to go back and brush up on certain features/techniques.
Sir, how could we change the data format from row to columns in power query? I mean unpivot columns to pivot columns. Your help in this regard is highly appreciated.
My data has 8 qtr worth of data for 4 different LOB. I created my pivot to show % diff from the previous qtr, but need to also display it in a chart. When I use the insert Pivot chart feature, it only shows my sales, not the % growth. Can you help?
PivotTables and PivotCharts are limited to what is shown in the PivotTable. This is different than a formula solution where you can have many formulas, and show only one or two in the chart.
Kindly consider the data as given. Col A (amount )-22,45,45,68...30 value Col B (Dates)- DD/mm/yy Required day("ddd") of which maximum sale has done are - 1. No helper column allow 2. Formula required only in one cell . Say only C1 has formula in it . Like total sale on Monday is 67, Tuesday-98, Sunday-109 and so on... Our answer is Sunday. Sir please help...
Actually, I think it is more steps in Power BI... But if you want the interactive visualizations and share-ability of Power BI then it is worth the extra steps.
Thanks Mike. Simple and fast. Beautiful staff. :)
You are welcome, John : )
Thank you, I learn from every video whether it's 4 minutes or 40.
Yes!!! I love to hear that the short and long ones are helpful! Thanks for your support, Sally Lee : )
Thank you Mike. Quick and easy!
You are quick and easy welcome, enrique : )
Good refresher on Pivot table capabilities, nice video Mike!
For someone as Excel-Smart as you, Chris, it is a refresher : )
Thank you again for the great video Mike
You are welcome again, most awesome Katerina : )
Dear Michael,
Always love to watch and learn from your master teaching.
Glad that I can help and that you like the videos, Vincent!!!
Truely magician .....Awesome
Glad it is awesome for you, dhun !!!!
Thumbs up as always!
Thanks as always, Teammate!!!!!
I have been missing your videos ...
@@excelisfun I've been VERY busy last 3 months. I have been working hard so I can have very long holidays. My videos will be back again soon :)
@@pmsocho We miss you : )
The power of excel...many alternatives to solve problem.
Wow Great Help From this Amazing channel.
Thanks, Phone Excel!! Thanks for your amazing channel also : )
Great. Didn't know about the options for previous, next etc. I do now!
Yes, Erica, and it is so helpful that the PivotTable can do these tricks, as compared to the many other more complicated methods. Each has there place, though. Thanks for your support : )
@@excelisfun Well I can't thank you enough. You really help me 'move on' .. and I so enjoy your sessions!
@@EricaDyson Yes you can thank me enough: just a comment and thumbs up on each video. Since the vast majority of viewers do not complete this simple task, when you do, it is like a HUGE and meaningful Thank You : ) : )
Spooky you should post this video this weekend; it's a pivot table function I rarely use but have used it 3 times in the last week for quick reconciliations. Thanks Mike.
You are welcome for the spooky : )
When you want Resulting information to automatically refresh.... Use formulas.
Other wise.... use Pivot Tables and manually Refresh.
Perfectly said!!!! That is the way I should have said it in my books I wrote ; ) Next time you can be my editor : ) : )
@@excelisfun
For auto refresh create macro through which you can do
brilliantly simple
Yes, the PivotTable is truely one of the best inventions ever!!!! Thanks for your support, doug, with your comment, thumbs up and Sub : )
ExcelIsFun I know but it was funny that when it first came out, I was excited but some of my co workers and higher ups didn’t like because they couldn’t see how it worked - where are your formulas? Now it’s the opposite - what else can you do?
ExcelIsFun whoever invented the pivot table revolutionized financial reporting and analysis. That programmer should be enjoying the good life on a cayman island beach by now.
@@dougmphilly It was programmers at Lotus 1, 2, 3 back in late 1980s early 1990s. They sold it as a separate program called Improv. Then Microsoft copied it and gave it away for free in Excel in 1990s.
@@dougmphilly That is funny : )
Thanks Mike.. another excellent lesson.. fun with Pivot Tables. Thumbs up!
PS - Just for fun, you can get auto updating of a Pivot Table with a simple macro on the Worksheet_SelectionChange event of the subject worksheet as follows:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B:C")) Is Nothing Then
ActiveWorkbook.RefreshAll
End If
End Sub
The above will force a Refresh All with any changes made in column B or C.. so.. makes the Pivot Table dynamic with the help of a little VBA. Might create a performance issue for large data set. But, it's an option for certain circumstances.
Thanks for the VBA, Wayne : )
And my favorite :). Thank you :)
PivotTables? Your are welcome!
Mike, There is a macro to automatically update Pivot Table ?
I am sure there is, but is it really faster than right-click refresh?
Should use it.
Shouldn't*
@@excelisfun Thank you, mike!
Beautiful...
Glad it is beautiful for you, Romesh!!! Thanks for your support on each video and your Sub : )
Thanks Mike, i better use the pivot table and refresh. Thank you
The PivotTable is so very easy : ) Thanks for your support, Ogwal, and try the Teespring and see if you can get a Mug or shirt : ) Let me know if they do the shipping you want.
Clicking Ctrl+Alt+F5 is not very difficult, thank you so much Mr. Mike
Hi Sure pivot tables are useful; but in a scenario like this; I prefer IFS variant functions for their ability to instantly update the summary. In pivot as you correctly said if the user forgets to refresh, the pivot table will carry the same old result. Thanks for sharing video. I have a request will you please post a video on LOOKUP (ARRAY and VECTOR form) function or if you already have please share the link.
Yes, formulas update instantly - and it is the only feature that does that : ) Yes, I have many videos on the marvelous LOOKUP function. Here is a great one: ruclips.net/video/N24cBM-CTw4/видео.html
Could you sort the years as well.
Those pivot table features sure are super-handy!
Although, I’m not used to those features anymore since I make all formulas as explicit measures.
Where is the time when all data came in just one table?... doesn’t seem to occur anymore.
Since I need that function so often the last months I’ve put the Refresh All button on my QAT-walk.
Because: pushing that button will refresh all pivot tables in my dashboards at once.
BTW, Mike is there any reason why you did these two videos? They seem like re-iterations of previous videos to me (I’m sure you’ve done this before - must be, right?...).
If you download the Excel file for this video you'll see it applies for EMT 1556 to EMT 1560. So it is the same problem just solved differently each time. I think Mike said that he's going to do some bite-sized videos which I'm sure is what he's doing here. The focus on one technique in a video is probably good as it helps most of us to focus on just the technique in question. Too many methods presented all at once can be overload for some people, especially if you're not comfortable with what's being discussed.
Just changing it up for fun. Sometimes we do it all in one, sometimes in multiple videos. Plus, I am just trying to do Excel Quickies right now ; ) Also, if you do have it all in one table, as so many humans on the planet do, then this it just a great helpful tick : )
@@brianxyz You are right, most RUclipsrs just want a quick solution; they do not want the many methods and comparisons to help get the full understanding.
@@brianxyz brianxyz and Geert, I have done over 3000+ videos, and I have done the videos in many, many different ways and different formats. Some short and to the point, and some in depth and with all the details. The truth is, that so far in RUclips's history, the short and to the point videos get the most support and views. But much of what I have done at RUclips has gone against that grain, because I want to make videos that cover the hows and whys and full details in all directions possible. My long and detailed videos that cover the full topic and go on for an hour or two (what I like to call one-stop-shopping), are rarely watched in full or promoted by the RUclips algorithm. There are few other resources on the internet that have the full story like I like to tell. But the truth is that most humans that come to RUclips (and I think most humans on the planet) do not want this. Since I have posted most of what I know in full detail format, I figured I should do some quick and to the point shorter video for this summer : ) If I can keep sustaining the energy after 11 years, I will keep making these Excel Quickies and then a few more full detail classes, or more : ) : ) : ) : )
@@excelisfun For problems that involve a lot of steps and/or a deep understanding of the techniques used, the longer videos are ideal and much appreciated. Watching a two hour or so long video numerous times might take up a lot of time but in the process you learn so many things that it's worth it in the end. Your use of time stamps is a lifesaver for when one needs to go back and brush up on certain features/techniques.
Sir, how could we change the data format from row to columns in power query? I mean unpivot columns to pivot columns. Your help in this regard is highly appreciated.
I am not sure. Did you try Pivot Columns?
My data has 8 qtr worth of data for 4 different LOB. I created my pivot to show % diff from the previous qtr, but need to also display it in a chart. When I use the insert Pivot chart feature, it only shows my sales, not the % growth. Can you help?
PivotTables and PivotCharts are limited to what is shown in the PivotTable. This is different than a formula solution where you can have many formulas, and show only one or two in the chart.
Kindly consider the data as given.
Col A (amount )-22,45,45,68...30 value
Col B (Dates)- DD/mm/yy
Required day("ddd") of which maximum sale has done are -
1. No helper column allow
2. Formula required only in one cell .
Say only C1 has formula in it .
Like total sale on Monday is 67,
Tuesday-98,
Sunday-109 and so on...
Our answer is Sunday.
Sir please help...
I have no idea. I am sorry. Try this great Excel Question site for back and forth dialog to get Excel solutions: mrexcel.com/forum
This should also be this quick in powerbi
I think so : )
Actually, I think it is more steps in Power BI... But if you want the interactive visualizations and share-ability of Power BI then it is worth the extra steps.