In the old days this was really hard with a formula! PivotTables are easy. But with the new GROUPBY and PIVOTBY functions, how do you group by month!?!?! This video has the answer : )
Still finding new ways to construct and use GROUP/PIVOTBY. Biggest easy step forward was discovering that the Filter argument works just like the ToInclude argument in the traditional FILTER function which makes multiple filters a breeze! I really like this narowly focused and very succinct video format. Great video!
Darn, always forget that double negative things! Thanx for the reminder. That’s one of the many things I really dig in all your tutorial’s, there is always hidden gems on top of the main subject. Than
Thanks for the great video! We waited years for a groupby type function and now it's here it makes life so much easier. Why the Excel team waited so long to implement such a needed and useful function is a complete mystery to me. Looking forward to the next video.
In fairness, until they got the dynamic array formulas, such a function wouldn't have made much sense. Since dynamic arrays, the new functions have taken off, and as soon as lambdas entered the fray it was only going in one direction.
@@ricos1497 Precisely, this is functionality that has existed in likes of R and Python for several years, Excel should really have implemented methods for this kind of data manipulation many years ago when it became clear people were trying to use Excel as a cut down database application.
You know, that formula did look familiar lol. There has been so much Excel fun over the years I've lost track of how many of your videos I've watched. Plus Mr Excel, Leila, Mynda Treacy, Chandoo, Wyn, and Chandeep (Goodly).
Thanks Mike! 🙏 I haven't been on this super channel for a while and I've missed it ☺. Are you possibly planning new MECS series in the future? thanks...
Good evening sir Can you please help me the issue if i want to use filter formula with larger or smaller than with many criteria ? Appreciate your response
please i have a question i've made a bill to count the bill of patients in a hospital by just entering informations of the patient and choose from a list of data and i protected the sheet and after that i made a macro to transfer data to an archive in an other sheet (but it doesn't work) is it bcs the sheet is protected ?? and what shall i do
Hello, Mike. I tried downloading your excel files, and it gave me "HTTP 502 - Unable to Connect to the Origin Server". Do you know a way to get through this? Thanks a lot for your amazing videos!
I am impatiently waiting for MS Excel 365 to release these 3 formula functions: PY, GROUPBY and PIVOTBY. I am not a part of Beta program (insider preview). They are taking too long. 😢
Boy is that true!!!! The range for beta to release is about 2 months to 6 months. It has been four months. Microsoft won't tell the exact release time : (
@@excelisfun thanks! yeah, it is very confusing, becuase there has been a couple of announcement by microsoft at different times for those functions. Same problem with the new checkboxes - still not in main release.
@@keiranhoareThis confusing and lack of clear time line has always been the case with beta. In fact, as a leading expert on array formulas and the author of the book about them (and a new one coming out in a year), when the new Dynamic Array Formulas were released, I did not get them and was unable to blog about them. I was so mad. But what are we gunna do? In the long run we all get the goods : )
Why some functions such as sequence, filter, sort, unique and offset are not working displaying name error. Sometimes it works and sometimes not what the problem???pls help!!!!!!
You did not give me specific details, so it is hard to know. But: Name error means there is a series of characters that are incorrectly entered text because the characters are not contains in quotes, or the characters are not the name of a build in function, or the characters are not a defined name, or the characters are not a name of a variable, or the characters are not the name of a table, or the characters represent a function that is not in Excel.
Mr Girvin, I can not find group by or pivot by functions in my Excel although when i update the excel , it tells me that it is on its latest update , I do not know what to do
@@excelisfun, I have the Insider Current Channel (Preview) version, which I thought is an intermediate step between Beta and General Release. As far as I know, no one that has this version has the GROUPBY or PIVOTBY functions. And it is pretty bothersome. I've seen a plethora of videos discussing it but no one can practice using it unless they use the Beta version. And, from your comment it seems like the Beta users don't get information for when the new features will be promoted. I don't see it getting into standard 365 form many months. How can the general public get some information from the developers at Microsoft on what their challenges are?
The GROUPBY and PIVOTBY functions seem to be stable and have been in BETA for 4 months. When is MS planning to put them into the stable channel or at least preview? 😂😂😂😂
We NEVER know. It is always an excruciating wait... : ( But the good news is that in the long run Excel and Power BI just keep getting better and better : ) : )
@@excelisfunyou do wonder if they're considering the way in which those functions are written in some way. I'm not surprised they want to be very cautious with two fairly extensive functions. As I recall, lambda also took a long time to release. These two will be extremely heavily used I expect, and so they'll want to be certain with them. I've actually questioned whether they are complete in the way you'd expect (if they can work like DAX or PQ). For example, could you perform a HASONEVALUE() type function using pivotby? Is there any way of interacting with total or subtotal lines? I think that part is missing, and perhaps a rewrite of the way the function works could allow for this. Who knows.
@@ricos1497Actually we 100% do know that that is why it takes so long. They look at all the usage stats, feed back and MVP talk and make adjustments. Remember with XLOOKUP, they actually added a new argument before the beginning of beta and when it was released to the full public. I think there are missing pieces, but we are limited in that it is a function and does not have a dialog box, but I guess that could be overcome. I think of that in regards to grouping. That seems to be the missing piece to me - in regards to comparisons against the PivotTable. This video shows a way to group, but most would not know that. I love your idea of a HASONEVALUE. We will see : )
In the old days this was really hard with a formula! PivotTables are easy. But with the new GROUPBY and PIVOTBY functions, how do you group by month!?!?! This video has the answer : )
Excel is really becoming fun to learn. New formulas are unmatchable 👍
You got that right : )
Tons of Gratitude to Our Mentor!
Thank you very much for the donation : ) : ) It helps a lot, Ankur Sharma!!!!
Still finding new ways to construct and use GROUP/PIVOTBY. Biggest easy step forward was discovering that the Filter argument works just like the ToInclude argument in the traditional FILTER function which makes multiple filters a breeze! I really like this narowly focused and very succinct video format. Great video!
Thanks, O Masterful Word Smith And Super Smart Analyst Richard Hay!!!!!
Darn, always forget that double negative things! Thanx for the reminder. That’s one of the many things I really dig in all your tutorial’s, there is always hidden gems on top of the main subject. Than
You are welcome for the hidden gems!!! The gems (those small details) are what make Excel so much fun : ) : )
👍👍👍 thumbs up! Gratuluję trick, thank you, Mike!
: ) : ) : )
Your videos get smaller as Excel gets smarter. Thanks Mike.
Smaller means we can have more different individual small nuggets of fun, rather than just big and long boulders of fun lol
Thank you much for this EXCELlent share amazing Mike.
You are welcome, Fellow Teacher : ) : ) : ) : )
Great trick Mike, i love those short format.
Short format is easy when formula is short lol
Super improvement.
The GROUPBY function is a real savior for Excel users. Thank you Mike.
Especially now that we can group by month : ) : ) : )
Thanks Mike. I love it when it becomes so easy with new functions, although the old version was fun building it. :) :)
Yup, the old formulas were whole day projects some time: the whole day was filled with fun : ) : )
Thanks for the great video! We waited years for a groupby type function and now it's here it makes life so much easier. Why the Excel team waited so long to implement such a needed and useful function is a complete mystery to me. Looking forward to the next video.
Mystery for sure : ) : )
In fairness, until they got the dynamic array formulas, such a function wouldn't have made much sense. Since dynamic arrays, the new functions have taken off, and as soon as lambdas entered the fray it was only going in one direction.
@@ricos1497 Precisely, this is functionality that has existed in likes of R and Python for several years, Excel should really have implemented methods for this kind of data manipulation many years ago when it became clear people were trying to use Excel as a cut down database application.
Wow amazing
Yup, we can group by month in GroupBy : ) : )
Bamm!! Thanks Mike...
You are welcome, Matt!!!!!
Wow!
: ) : ) : )
Amazing topics as usual Mike, can't wait for the 365 upgrade so i can start using this mind blowing functions
I can't wait for you to start getting your mind blown. It's fun!!!!
Always found useful tips from your videos and great thing is we can practice along. i got 365 but don't have Groupby formula yet.
Thanks MIke! That LET formula at 0:18 was crazy. I'm still waiting to get GROUPBY, wish Micrsoft would push it out to everyone soon!
I 100000000% wish they would push it out soon, like tomorrow : )
Sorry that it has to be such a long wait... : (
You were there for that video back five years ago, so you can really appreciate the new improvement that will be out to all soon : )
You know, that formula did look familiar lol. There has been so much Excel fun over the years I've lost track of how many of your videos I've watched. Plus Mr Excel, Leila, Mynda Treacy, Chandoo, Wyn, and Chandeep (Goodly).
@@chrism9037, Lucky we have such a great Team!!!!!
Thanks Mike! 🙏
I haven't been on this super channel for a while and I've missed it ☺. Are you possibly planning new MECS series in the future? thanks...
You are welcome!!!! I always have new ideas for future videos and classes : ) : )
Awesome Mike, on what date is the groupby function being released to all 365 users
We don't know. Microsoft won't say. But the range for beta to release is about 2 months to 6 months. It has been four months.
Good evening sir
Can you please help me the issue if i want to use filter formula with larger or smaller than with many criteria ?
Appreciate your response
please i have a question i've made a bill to count the bill of patients in a hospital by just entering informations of the patient and choose from a list of data and i protected the sheet and after that i made a macro to transfer data to an archive in an other sheet (but it doesn't work) is it bcs the sheet is protected ?? and what shall i do
Hello, Mike. I tried downloading your excel files, and it gave me "HTTP 502 - Unable to Connect to the Origin Server". Do you know a way to get through this?
Thanks a lot for your amazing videos!
Hi,
F.Y.I.: I was able to successfully download the workbook.
I am impatiently waiting for MS Excel 365 to release these 3 formula functions: PY, GROUPBY and PIVOTBY. I am not a part of Beta program (insider preview). They are taking too long. 😢
Boy is that true!!!! The range for beta to release is about 2 months to 6 months. It has been four months. Microsoft won't tell the exact release time : (
Are these functions still in preview? I don't have access to these on my m365 excel...I feel like these functions were announced 3-4 months ago
They were. But the range for beta to release is about 2 months to 6 months. It has been four months. Microsoft won't tell the exact release time : (
@@excelisfun thanks! yeah, it is very confusing, becuase there has been a couple of announcement by microsoft at different times for those functions. Same problem with the new checkboxes - still not in main release.
@@keiranhoareThis confusing and lack of clear time line has always been the case with beta. In fact, as a leading expert on array formulas and the author of the book about them (and a new one coming out in a year), when the new Dynamic Array Formulas were released, I did not get them and was unable to blog about them. I was so mad. But what are we gunna do? In the long run we all get the goods : )
Why some functions such as sequence, filter, sort, unique and offset are not working displaying name error. Sometimes it works and sometimes not what the problem???pls help!!!!!!
You did not give me specific details, so it is hard to know. But:
Name error means there is a series of characters that are incorrectly entered text because the characters are not contains in quotes, or the characters are not the name of a build in function, or the characters are not a defined name, or the characters are not a name of a variable, or the characters are not the name of a table, or the characters represent a function that is not in Excel.
@@excelisfun shows that it's not in Excel.
@@user-qs5xb6sd3x That is because you have an older version of Excel. You must have Microsoft 365 Excel.
Mr Girvin,
I can not find group by or pivot by functions in my Excel although when i update the excel , it tells me that it is on its latest update , I do not know what to do
They have been in beta in the Insider Edition of Excel for a few months. It should be released to all of Excel really soon!!!
@@excelisfun, I have the Insider Current Channel (Preview) version, which I thought is an intermediate step between Beta and General Release. As far as I know, no one that has this version has the GROUPBY or PIVOTBY functions. And it is pretty bothersome. I've seen a plethora of videos discussing it but no one can practice using it unless they use the Beta version. And, from your comment it seems like the Beta users don't get information for when the new features will be promoted. I don't see it getting into standard 365 form many months. How can the general public get some information from the developers at Microsoft on what their challenges are?
The GROUPBY and PIVOTBY functions seem to be stable and have been in BETA for 4 months. When is MS planning to put them into the stable channel or at least preview? 😂😂😂😂
We NEVER know. It is always an excruciating wait... : ( But the good news is that in the long run Excel and Power BI just keep getting better and better : ) : )
@@excelisfunyou do wonder if they're considering the way in which those functions are written in some way. I'm not surprised they want to be very cautious with two fairly extensive functions. As I recall, lambda also took a long time to release. These two will be extremely heavily used I expect, and so they'll want to be certain with them. I've actually questioned whether they are complete in the way you'd expect (if they can work like DAX or PQ). For example, could you perform a HASONEVALUE() type function using pivotby? Is there any way of interacting with total or subtotal lines? I think that part is missing, and perhaps a rewrite of the way the function works could allow for this. Who knows.
@@ricos1497Actually we 100% do know that that is why it takes so long. They look at all the usage stats, feed back and MVP talk and make adjustments. Remember with XLOOKUP, they actually added a new argument before the beginning of beta and when it was released to the full public. I think there are missing pieces, but we are limited in that it is a function and does not have a dialog box, but I guess that could be overcome. I think of that in regards to grouping. That seems to be the missing piece to me - in regards to comparisons against the PivotTable. This video shows a way to group, but most would not know that. I love your idea of a HASONEVALUE. We will see : )
@@excelisfun yes, that was actually me that suggested the additional "if_not_found" argument for xlookup! It's my claim to fame.