GROUPBY Excel Worksheet Function. Single Cell Reports Made Easy! 11 Examples. EMT 1844
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1844....
Learn about how to use the GROUPBY function to make single cell formula reports that can update instantly when source data changes.
Topics:
1. (00:00) Introduction to GROUPBY, PIVOTBY and the new Lambda Replacement Functions
2. (00:58) Compare arguments in GROUPBY and PIVOTBY functions.
3. (01:58) GROUPBY and the 7 arguments.
4. (01:58) 16 New Lambda Replacement Functions.
5. (05:43) New Lambda Replacement Functions as replacement for LAMBDA.
6. (06:17) PERCENTAGE of in GROUPBY Function.
7. (07:04) Why use formulas rather than PivotTables.
8. (07:17) Two Fields in Row Area of Report.
9. (08:05) Problem: No Label for Calculation Column. Look at field_header argument.
10. (08:21) Two Columns of calculations in report.
11. (08:55) Add Custom Header Labels to Report with VSTCK Function and Array Constant.
12. (10:26) Subtotals
13. (11:00) Conditional Formatting for dynamic Report.
14. (13:25) F5 Go To Trick to find Conditional Formatting in Report.
15. (14:44) How to create multiple columns with different calculations with GROUPBY, LET, DROP and TAKE Functions.
16. (18:04) Filter GROUPBY Report with Contains Criteria.
17. (19:17) Filter GROUPBY Report with Criteria from a list.
18. (21:18) Using LAMBDA in the GROUPBY Function: two examples.
19. (23:25) Creating Array Calculation in values argument of GROUPBY Function.
20. (24:06) Create Fully Dynamic GROUPBY Report with Formulas Inputs from Worksheet Cells.
21. (26:55) Summary
22. (28:12) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #conditionalformat #conditionalformatting #subtotal #groupby #excelformula #excelfunctions #excelfunctions #excelformulasandfunctions #lambda
Another amazing class! Thanks Mike!
So excellent - so rich with content & new learning! Thanks !!
Dear Mike, Thanks for this Video
Thanks for the donation, Teammate!!!!! I have been away for 4 months taking care of my mom, so I missed your comment and many others...
Nice Video Mike, at 20:51 we can use in filter argument (C3:C31=Y17)+(C3:C31=Y18)+(C3:C31=Y19) to pick every region which is in the list and ((C3:C31=Y17)+(C3:C31=Y18)+(C3:C31=Y19))=0 to pick every region which is not in the list.
Professor, without your help, I would never be able to figure out how to fully use these new functions. As always, THANK YOU!
As always, you are welcome, Kevin!!!!!
Another great video Mike - I haven't got my head around these new functions yet, but I'll get there.
After the full length of an interesting movie time video
I came to groupby function video to learn more
There were few more examples than the full length video but it did not took you time to resolve the "Function" argument lookup problem.
This shows that for any sort of excel issue, only excelisfun is the first to sought of
I am highly delighted to watch your videos and learn new and exciting things in excel
I am so happy you enjoy the videos : ) : ) It is fun to make them and hang out with the Online Excel Team!!!
Welcome back excel genius. Great video.
I am only at home for a few days. Then I go back for a few more months. But at least I got to have a small bit of fun making videos for the Team!!!!
Thanks Mike, very rich content with a lot of information
Excel has changed the way we think :)
Gracias Mike... Todo esto es increíble. Eres grande, gracias por tanto en cada nuevo tutorial. Espero que su querida mamá este muy bien, espero que usted también este muy bien. Gracias de corazón.
Glad you like it : )
Mike, thanks for sharing this awesome solution! One of the best Excel solutions I’ve seen so far. I wish I had these formulas many years ago for setting up worksheets to perform a series of calculations to help our staff quickly analyze large sets of fisheries data for environmental reporting. We’re still doing things the hard and time consuming way. This video will solve that problem.
Yes, the sooner MS releases to all, the better : ) : )
Thanks a lot for this session mike
You are welcome!!!!
Thank you Mike for teaching how to use new Excel functions. Your explanation is great. 💚
Another awesome vid. Once this hits 365 for everyone it will simplify so much.
Yes indeed, simplify so much : ) : )
Another great video with great examples
Glad it is great for you!!!!
Hi Mike, you always amaze me . Thanks a lot for this session 🎉
You are welcome a lot!!!!!
OMG! So many magics are happening in Excel! Thank you a lot!
You are welcome a lot!!!!! : )
These are truly "Next Level". Thanks Mike
You are welcome for the next level fun : ) : )
Awesome! Thank you for GRoupby tricks 👍👍👍
What well-explained material! Thanks, Mike. Finally we have 'GROUPBY' available directly in Excel, that is, in Dax the function was already available and in Power Query using the GUI it is also possible to do the same using the Table.Group function.
Yes!!! Now all three functional languages have groupby: Worksheet, M, DAX!!!!
Thanks Mike for sharing. Very powerfull new function ;)
Yes, NS is so good to us : ) : )
Many thanks sir you make my life easier 🙏🏼
Many you are welcomes!!!!!
Just awesome! Quite enriching. Looking forward to getting access to these new powerful functions.
I can't wait for you to get them either!!!
That was amazing, I now need to watch and do.
Watch and do is how we learn. The do part is the key!!!
@@excelisfun I'm due to go over msptdaa 19, I need a refresher in complex filter reduction errors, got in a bit of a mess with DAX Summarize. 😕😕
Hi Mike!
I hope you and your Mother are doing better / good.
Wow, soon Excel will rule the world with those changes.
this is perfect!
Thank you for covering this!
You are welcome! Thanks for the well wishes! Yes Excel already rules the world because it is The Only App that Matters : )
Brilliant function💯, brilliant tutorial💯, thank you very much Mike!🙏
You are welcome very much!!!!
Ok Mike tomorrow i know what i have to study.... Amazing
You will have to study all three becasue together they have so many tricks : ) : ) Sounds like a fun Sunday!
@@excelisfun yess💪💪💪
Great Stuff, as always. Thank you Mike!
You are welcome as always : ) : )
That's Amazing Mike ... i cann't wait to get this update
I am amazed too by the GROUPBY and PIVOTBY Functions : ) : )
This is awesome!! Thanks MIke, I am so looking forward to getting this in Excel!!
Yes!!!!!! I can't wait for you to get them, Chris M!!!!!
It is very interesting to be able to use HSTACK to select multiple columns as "row_fields" or even to select multiple "functions" at the same time !!!!
So true : ) : )
Thanks amazing Mike for this EXCELlent video.
You are welcome, Most Awesome Fellow Teacher!!!!
Brilliant.
Great video thanks Mike
You are GROUPBY welcome!!!
This is a brilliant comprehensive Video 📹 👏 Thank you very much Mike for illustrating all these features in this new Groupby function.
You are welcome, the PIVOTBY one is even more mind blowing and it comes out Monday.
@@excelisfun looking forward to it :) 😀
Very interesting we will see when this available thanks for sharing 😊
Hopefully soon since they are so great : ) : )
@@excelisfun Hey may I ask what version this is in? I am having Version 2312 Build 16.0.17031.20000) 64-bit and here it is not available yet.
@@dirkstaszak4838 You have to have Insiders Edition. Google it and sign up. Then you will have all the beta stuff. However, even with beta, a random 50% get it the first week or two and then the rest of beta gets it later.
Another interesting and informative video! The groupby function looks very useful for report building, I can't wait till it rolls out to my account. It is about time that Excel had a function to perform basic grouping. Hopefully the addition of these functions will make it more likely that Excel will start to more strongly enforce data typing.
I look forward to seeing the video on Pivotby. Hopefully I will have the new functions by then.
I hope your mother is contimuin g to recover from her recent ill-health.
I hope you get it soon too : ) : )
Great tutorial!
Glad it is great for you!!!!
Excellent 🎉.. specially last one was. Thanks for multiple examples of this new functions...😊
You are welcome for multiple fun examples : ) : )
DOUBLE BAM BAM ❤
Riiiiight: Bam Bam : ) : )
I knew you'd be covering this haha, good stuff
These functions are just so much fun!!!!!
Thanks Mike. Can'yt wait to try them out later.
I'm on the Beta channel, so hopefully i have them. :) :)
Yes!!!! You will love them, Formula Guy John : ) : )
Fantastic explanation! Would be great if MS could improve conditional formatting so it works with such reports w/o having to include extra rows.
That is our dream : ) : ) : )
Again, these are great Excel times!
Thanks Mike for this comprehensive introduction to GroupBy. Good stuff and well done! (Although I prefer my steak medium rare ;-)
BTW: Mr.Excel taught us that those abbreviated functions are called eta-lambdas (in the end we’ll need the entire Greek alphabet to indicate something ;-).
Like you pointed out they seem to have a data type of their own (not sure what), just like those new checkboxes (a mixture of 2 things in the case of the checkboxes).
If the Excel Team wants to keep them, they should extend their functionality and access to them so we can dynamically use them in formulas like you tried. Power Query is like that, but Excel Classic not so much, which introduces some annoying restrictions like “no arrays in SUMIFS”, “no table refs in data validation”, etc.
Ahhh... I watched his video but missed that. eta-lambdas. But I probably will mispronounce eta lol
Thanks for your smart comment, Geert : )
@@excelisfun Mike the sound you’re looking for is the “a” in “bad”. Then just make it long.
That’s the sound you should use in pronouncing the “e” in eta and beta.
@@GeertDelmulle Having Good Teammates is the best : ) : ) : ) : )
Hi Mike, thank you for this comprehensive tutorial. For the a selectable function LAMBDA and SWITCH allows you to do that, but it can be a lot of work to build the formula for all the functions. If your selections are limited than you can do a work around like this for example where you just want SUM and AVERAGE but can be expanded to what you want.
=VSTACK(
HSTACK(AH1,AH3&" OF "&AH2),
GROUPBY(XLOOKUP(AH1,C2:E2,C3:E31),XLOOKUP(AH2,F2:G2,F3:G31),LAMBDA(r,SWITCH(AH3,"SUM",SUM(r),"AVERAGE",AVERAGE(r))),0,,-2))
Regards, Lester
Lovely formula, but comment pinned to top has an internal way. I will have to make a video about this new trick soon : )
@@excelisfun I see that it could work to run 2 function at the same time but curious to see how it can make it a selectable function by having a dropdown list in a cell like you had in cell AH3. Not sure if we referring to same problems.
@@lesterpotts6142 They are 2 different problems.
@@excelisfun for dynamic solution for Function argument at 25:25 old school CHOOSE is the the most simplistic I have tried.
CHOOSE(XMATCH(AH3,AR2:AR14),SUM,PERCENTOF,AVERAGE,MEDIAN,COUNT,COUNTA,MAX,MIN,ARRAYTOTEXT,CONCAT,STDEV.S,STDEV.P,VAR.S,VAR.P,MODE.SNGL)
The PRODUCT function is the only one that causes a error, therefore the exclusion.
I guess this is better than the LAMBDA and SWITCH combination as I suggested previously.
@@lesterpotts6142 You got that to work in PIVOTBY? Wow!!! So Cool : ) : )
I tried:
=PIVOTBY(Region,Product,COGS,CHOOSE(XMATCH(K6,R2:R16),HSTACK(SUM,PERCENTOF,AVERAGE,MEDIAN,COUNT,COUNTA,MAX,MIN,ARRAYTOTEXT,CONCAT,STDEV.S,STDEV.P,VAR.S,VAR.P,MODE.SNGL)),3)
But no luck yet...
I was using a different range than you.
Wow great sir thsnks a lot master
You are masterfully welcome!!!!
Awsome video. Many thanks for the details. I feel like excel functions are becoming as powerful as DAX. Maybe in future we will see DAX measures outside power pivot. 😅
They are becoming similarly as powerful as DAX. But most Excel array formulas have to make calculations across full arrays, where as, DAX is built for big data and uses Filter Context, Columnar Database and the Data Model Engine to make quick calculations across big data without have to traverse the full array. But we sure are getting a lot of amazingly power functions in worksheet : )
Super video👍
All I need now is for my beta to update; AND to find an AI to convert all my pivot tables to formulas 😆
Thanks Mike. Excel really needs to spill formatting down in a spilled array from the top row. Also would have been nice if there was a header value to take the headers from above the data (i.e. you're using Table1[Column1] as your data so create the header from the cell above (i.e. the column header).
Thank uou
You are welcome!!!!
Mind blowing.
I am not able to find this option in 365
the same in my version. maybe that is previes version?
Are you using the beta insider program?
It's in Microsoft 365 Beta and so far only 50% of such users have these three new functions.
@@excelisfun I see. I saw the update today but didn’t get the “percentof” function.
@@SeyPras. I hope soon!!
❤❤👍👍
: ) : ) : ) : )
Thanks Mike for this good tutorial,
I hope your mother's health is improving.
What I wonder about these developed functions:
1- Can GorupBy and PivotBy be used together?
2- Can results be found with char characters?
3- Is there any need for pivot tables after these functions?
4- Can we import the values calculated with these functions into power Query?
5- Can we use dynamic array symbols in functions (e.g. {})?
Great video!
I've got both the GROUPBY and PIVOTBY functions but not PERCENTOF. Strange that I didn't get all three new functions at the same time.
Yes, PERCENTAGEOF appeared for first two days, then MS took it away... ?????? Why?
Thanks Sensei Mike! These are great and long overdue. I have 365 Business Insider but unfortunately do not have access. Do we know how creating charts will work with this? Dynamic? Or are we still needing to define OFFSET formulas? Still one big problem in Excel.
Great Amigo! Another amazing tutorial!!!! can you check my last questio in the other video related to this topic? i see that these fucntons apparently are not available for excel home edition.. so i assum that are just for excel enterprise.
Curious about something: in the example (a little sooner than halfway through) where you showed about making subtotals and the subtotal row is blank in that 2nd column, what happens if the original data had blanks in that 2nd column? Do you have two rows with blank columns?
Yes, blanks will appear in row area as a row. But no extra blank.
Great stuff! The time I've spent creating custom LAMBDAs to get these types of summaries.....
For the "Fully Dynamic GROUPBY Report" and not being able to add the type of calculation, can you not use the index number 1 - 16 instead of the relevant SUM or COUNT or.....whichever one? If so then can you (we, once we get the functions!) do an XMATCH on the function name from the drop down list in AH3 and return the position in an array, similar to SUBTOTAAL...? Seems like a missed opportunity from MS
Using SUM or AVERAGE is easier to interpret the formula. Only aggregate functions, so I think XMATCH would not work?
That would seem easy for them to implement, but just using the function names w/o having to always create a lookup table would be great. That might require a new TEXTTOFUNCTION capability.
So AGGREGATE in a LAMBDA then?
you are amazing as usual , i cannt find the fuction ( i am using 365 office )
any advice ?
Hello Mike! Thank you for video! But I still don't have this functions in my 365, in Beta-channel either. In what number of version do you have these functions?
Hope your mom get well soon! ❤
It's in Microsoft 365 Beta and so far only 50% of such users have these three new functions.
Hello Mike, Perfect video, but I can't find groupby function in my excel365
Haha! Remember my comment in a previous video saying I don't understand why you can't just use BYROW() without the LAMBDA function?
Microsoft must have heard you and added this lol
I have been away for far too long. My company is still on Office 2016 and I got lost in the intro. There's that much I missed by not having access to 365 professionally.
Yes, any entity who does not expend the resources to get M 365 is making a clear economic mistake.
When would you use these formulas when you can simply create a pivot table?
Great Functions. However in the function argument I only have 15 choices not 16. The PercentOf is missing from my version. Anyone down here knows why??
Yes, PERCENTAGEOF appeared for first two days, then MS took it away... ?
@@excelisfun very strange!!!
Wondering, why do you "ctrl enter", when "enter" works just fine?
Sorry. A question. I have the office 365 but i can see the groupby or pivotby functions . What is the reason?
Mike, at 2:00 , the 'GROUPBY' function does not work. I tried typing =GROUPBY(C2:C31;F2:F31;sum) and It results in #NAME? . What should I do to fix this problem ?
This means that you do not have these functions yet. They are beta right now. You can sign up for Insider Excel (free, just google) and get beta. Otherwise, it should be released to all of Microsoft 365 Excel in a few months : )
Is it possible to have the source data in one workbook and the group by report in another one ?
Absolutely : )
Hi Mike, for some reason, Groupby and pivotby function is not available on my worksheet. Do I need to add - in that feature or any other reason?
It is in the Insider version. It should be out to all M 365 in a few months.
I happens to have office 365 at work, but these new functions don’t seem to appear. How would i access them?
Hey mike, my excel don't show this function, how can i add this?
It's in Microsoft 365 Beta and so far only 50% of such users have these three new functions.
@@brianxyz brianxyz is correct. But sometime soon it will be released on the world : ) : )
Is there any advantage to using these as opposed to using a pivot table other than the instantaneous update when using formulas?
Nope. That's it.
@@excelisfun there's so many in the comments that are stoked about this new functionality... If a pivot table could do it all anyways why is everyone so excited about being able to write these complicated (for someone who is new to them) spill enabled array formulas to get the same result? Asking because I love learning new tricks and I'm just really trying to understand when I'd opt to break these out when it's so much simpler to use pivot.
@@Alex50095 Much of "what if" analysis requires instant update. When formula inputs from cells drive the analysis, these functions are preferred. For regular reporting, Pivot is still best.
@@excelisfun thank you! I appreciate you replying very much. Let me also say (because it needs to be said, always) thank you for the work you're doing to provide all of this training content for free. It's actually unbelievable that such a things exists for free and I can't believe I've only just discovered it.
@@Alex50095 Free and good is rad : )
I don't see this function available in Excel 365
Hi Mike, i have office 365 but this formula is not displayed. Groupby.
It's in Microsoft 365 Beta and so far only 50% of such users have these three new functions.
Sir i have a Question.
i have to sheets
1- sales
2-invoice
on sales sheet i have a table with name "Sales"
i want invoice # from the end of table from sales with macro button when i press new invoice button number automatically called from the end of sales table
hope u will reply me
It's very strange, but I've been subscribed to MS 365 Insiders for over 1 month and still the new features are not available.
Am I making any mistakes, Mike?
No. It is Microsoft. They are not consistent in how the new items are released.
@@excelisfun Many thanks ... ...so all I have to do is wait for these new features to be released to me as well. What a sorry ...
@@sscire If you go to File, Account, you can try to refresh to check for latest update. By The Way, About two years ago, I was also left out of the latest update and to wait months... It appears to be random.
I found strange behavior in the function if you a range of cells for the first two parameters and do not use defined names or a table instead, then employing parameter 3 after the function statement..
I reported it but maybe you have better contacts than me in MS.
I am an Insider Beta subscriber. Why do I not see these yet?...
It is the crazy thing that microsoft does: they release only to 50% and then a week later the next 50%
@@excelisfun thanks! I thought it was probably something like that but never noticed it before.
Hi, i am using office 365. , i am unable ro this new funxtions, can any one guide me
It's in Microsoft 365 Beta and so far only 50% of such users have these three new functions. Soon it will be in all of M 365.
Thank you
one emoji: 🤯
Perfect!!!!!
I don't have groupby or pivotby with excel
It's in Microsoft 365 Beta and so far only 50% of such users have these three new functions.
How I use in office 2016
Microsoft only makes it available in Microsoft 365 Excel. The whole formula engine is completely different in the new Excel.
most awaited function in Excel
Still query or power query is missing
Yes, indeed : )
how can we push toword ( the onw million subscriber ? )
you deserve it man i learn every thing about excel from your channerl )