How on earth is it possible that you don´t have +4 million subscribers ?!?! Each and every tip/video you show is at a level that other channels just don´t (can´t?) provide. I dare to say that EVERYONE will have use of at least half of your lessons. Hat of and respect from Sweden.
Thanks so much. We've only been around (on UA-cam) for just over a year. We're still the new kid on the block. Hopefully, we'll grow big and strong like our mentors. Thanks for watching.
Great explanation of this new function. I'm a big user of pivot tables so I can see this being used quite a bit. Let's hope Microsoft doesn't take too long in getting this function out to everyone.
Great tutorial and indeed best to see in rerun a few times at a slower speed 😁 I don't get PivotTables much, but the GroupBy function makes it lots clearer. Thank you fir the explanation
You can sometimes see it in the upper-right corner of the apps because I'm using my own account when recording these videos. Bryon Smedley - nice to meet you.
Very nice. Being on the MS 365 Insider (beta) channel let me get GROUPBY about a month ago. Just an FYI, you can add a filter column and link it to Slicers which is kind of cool but it can also crash Excel.
Looking forward to this! Is there a specific reason, that the formula refuses to populate any header for the value column? Would be great if that could be either blank if you add nothing or a custom value, like “sales in ” without having to head down the Vstack path .
Great video! I have one question - how did you toggle off the filter indicators on the header row of the pivot table at the beginning? I can switch them off by turning off the headers for row labels, but I also want to keep these labels. What is the workaround?
I achieved that look with a little VBA wizardry. I usually hide those filter buttons because I give the users Slicers for filtering. See code below: Sub DisableSelection() Dim pt As PivotTable Dim pf As PivotField On Error Resume Next Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.PivotFields pf.EnableItemSelection = False Next pf End Sub
This is great. Many thanks for explaining it so clearly. I have MS 365 and am on the Beta Channel, but I don't yet have this function (nor the related PIVOTBY). I guess it will come through soon!
My dear, In versions "6" of the two files available for download, you can also include a header for the value column, using the following technique: =LET(Array;GROUPBY(HSTACK(Sales[[#All];[ChannelName]]; Sales[[#All];[ProductName]]); Sales[[#All];[Amount]]; SUM; 3; 2; -3); IF(SEQUENCE(ROWS(Array);COLUMNS(Array))=3;"Sales";Array)) 🤗
In the last conditional formatted display, how can one clear out the unnecessary repetition of the values in the first column? For example, Under Channel, the value Online is repeated for all Product items. I would want it listed for the first one (3/4 Sleeve) and for it to remain blank for the remaining Product items. That way, the report becomes less cluttered.
This is a case where you would still want to use the "real" Pivot Tables. This function has but a fraction of the customization features of actual Pivot Tables.
Groupby function is really good and it is very helpful but it can return the values for only one column but in case in the abouve example if we need to add values for Sales amount and qty then is there any method to accomodate my requirement
You can define multiple aggregations by declaring them within an HSTACK function. For example: =VSTACK({"Channel","Product","Sales"}, GROUPBY(HSTACK(Sales[ChannelName], Sales[ProductName]), Sales[Amount], HSTACK(SUM, AVERAGE, COUNT), 0, 2, -3) ) Let me know if this makes sense to you. Cheers.
@@bcti-bcti Thanks for your prompt reply.But my question is if am having a tabe of Channal, Product, Sales Qty and Sales amount and contribution amount , how to i make a formula using groupby function to show the sum of Sales Qty, Sales amount and contiribution amount
@@Aasmv2013 I now understand. The formula is a bit complex. I have created a sample file with the formula so you can reverse engineer my solution. If you are familiar with HSTACK, VSTACK, SORT, and UNIQUE... you should be able to understand it and change it for your needs. Let me know if you need assistance. Cheers. www.bcti.com//wp-content/YT_Downloads/GROUPBY_Totals.xlsx
@@bcti-bcti it is really awesome now i can access your downloaded file thanks a lot but could you please share the separate videos for this case so that lot of users will be used
Public Service Announcement: Pay no attention to that loud "Boom" you just heard - it was just my head exploding while watching this video.😆 Now I'm gonna have to channel my inner "bovine" and ruminate for a minute, or two, on your explanations. This is masterful stuff, and I'm already imagining applications of this function in my spreadsheets. In the meantime, though, could you please explain, however briefly as needed, why these formulas are returning single and double square brackets all over the place? Just finished the vid on structured references which made plenty of sense, but I'm not doing so well on the bracket situation in these formulas. Any clarification will be a big help.
The square brackets are standard database nomenclature to account for field names that include spaces. For example: if a formula saw a reference to COUNTRY CODE, it could be interpreted as 2 fields, "Country" and "Code", or as a single field "Country Code". By including square brackets, we remove any ambiguity. It's either 2 fields, [Country] and [Code], or 1 field, [Country Code]. Proper Excel Data Tables make use of these square brackes for fields (i.e., columns). If the headings are to be included, [#ALL] refers to the heading. Since we need to know which heading is being targeted, the bracketed item that immediately follows [#ALL] is the target field data. These have to be treated as a single item, so an additional set of square brackets surrounds the set. I hope this helps. Maybe someone should make a video explaining this in greater detail 🤣.
Seriously, though, I'm pretty clear on the fixed references now, but the references to columns, say, [[ID], for example. Why two brackets on one side and just one on the other?
I'm uncertain where you are seeing [ [ID] in the example (or is that just your own example?) Where I have [ [some column name], there is usually following it another column reference with double brackes on the end, like [some other column name] ]. This would be treated as a single, multi-column reference like [ [ID]:[Emplyee Name] ] Did that help?
Apologies - that was my own example. Here is part of one of the GROUPBY formulas "GROUPBY(Sales[[#All],[ProductName]:[ChannelName]], Sales[[#All],[Amount]]" I'm most curious about how these are working.
Thanks for watching. The function has yet to be rolled out to the general public. If you'd like to try the function out while it is development, you can become a member of the insiders Beta program, so you can test out new features. Go to this link below. insider.microsoft365.com/en-us/join/windows
The function has yet to be rolled out to the general public. If you'd like to try the function out while it is development, you can become a member of the insiders Beta program, so you can test out new features. Go to this link below. insider.microsoft365.com/en-us/join/windows
If you’re talking about just getting office 365, you just need to buy a subscription from Microsoft. You can do that on their website office.com. If you’re asking about how to become a member of the insiders Beta program, so you can test out new features, go to this link below. insider.microsoft365.com/en-us/join/windows
April 2024. =GROUPBY is up and running (at least on Beta). I don't know when it was actually included, but who cares. OK, so, using your file (saved once for info and again, deleting everything except the base table for practice) I'm going to comment as I go instead of waiting for the end of the video. The first thing I've noticed is no dropdown for functions in my version. There was, however a dropdown for the header name generator and again later for totals/subtotals. It didn't format the numbers either (Version 1)... OK, I formatted the Amount column in the original table to accounting (no currency sign). Now it auto formats the result accordingly (Version 2). Yay!...Argh! Version 3 onwards, no number format again. How strange. Oh well, onwards and upwards....No further issues. Did GROUPBY auto format the subtotals and totals or did you do that yourself for visual effect for our benefit?...Ah, OK, just got to the Conditional Formatting part of the video so ignore my question. Still, at least it shows I watched to the end and paid attention.😉 In conclusion, a great video on a great function. I can see me having some fun with it. As you said, no good/bad, just options. Many thanks.
@@bcti-bcti When you record your video on the advanced filtering, any chance of a quick explanation of the formulas you used for your conditional formatting please? Even as I looked at them as I copied and pasted them, I was completely baffled. No need for an explanation of the conditional formatting itself. There are plenty of videos about that.
@@bcti-bcti Current channel people got GORUPBY and PIVOTBY on 9/25. By the way, your video was anything but worthless. Lots of people had this function a year ago and were making use of it as soon as it was released.
@@aremoteforu Yes. Suppose your table has 4 columns (A thru D) where the data starts on row 2 and has 50 rows. The first 3 columns are "Country, State, Product" and the 4th column is "Sales". You want to group by "Product" then subgroup by "Country". The formula would be as follows: =GroupBy(ChooseCols(A2:C51, 3, 1), D2:D51, SUM) The ChooseCOls function will select the 3rd and 1st columns to be used by GroupBy. Hope this makes sense. hanks for watching.
![Lp. Сердце Вселенной #60 РОЖДЕНИЕ ЛОЛОЛОШКИ [Финал] • Майнкрафт](http://i.ytimg.com/vi/YoR0pAV9FVQ/mqdefault.jpg)
How on earth is it possible that you don´t have +4 million subscribers ?!?! Each and every tip/video you show is at a level that other channels just don´t (can´t?) provide. I dare to say that EVERYONE will have use of at least half of your lessons. Hat of and respect from Sweden.
Thanks so much. We've only been around (on UA-cam) for just over a year. We're still the new kid on the block. Hopefully, we'll grow big and strong like our mentors. Thanks for watching.
Brilliant! Looking forward to when this function becomes live.
I think it's finally been universally released.
Great explanation of this new function. I'm a big user of pivot tables so I can see this being used quite a bit. Let's hope Microsoft doesn't take too long in getting this function out to everyone.
We really appreciate you helping us grow the channel.
Love the step-by-step presentation and excellent explanation.
Thank you for taking the time to say such nice things, and for watching.
Fantastic function. I can see I might use this more commonly while working through data analysis. Brilliant stepped out explanation Thankyou
Thank you for taking the time to watch.
Great tutorial and indeed best to see in rerun a few times at a slower speed 😁 I don't get PivotTables much, but the GroupBy function makes it lots clearer. Thank you fir the explanation
Thank you so much for watching!!
Amazing tutorial, thumbs Up.
I tried to find your name in the video, or the channel or even the website but I couldn't.
You can sometimes see it in the upper-right corner of the apps because I'm using my own account when recording these videos. Bryon Smedley - nice to meet you.
Loved the detailed explanation, Thank you!
Thank you for saying so and taking the time to watch. (big smile)
Thanks. This will certainly eliminate VBA to do the pivot refresh. Great! Perspicuously explained.
Thank you so much! (great and accurate use of the word "Perspicuously")
Excellent tutorial!💯
Thank you for taking the time to watch and say nice things.
Very nice. Being on the MS 365 Insider (beta) channel let me get GROUPBY about a month ago. Just an FYI, you can add a filter column and link it to Slicers which is kind of cool but it can also crash Excel.
Good to know. I'll have to try that. Thanks for watching!
It's really though explanation. Thank you mentor
Thank you for thinking us us a a mentor. That is high praise.
Great video!
@@kennethstephani692 Two comments in one afternoon. I’ll take that as a win. Thanks.
Great video. Thank you!
My pleasure. Thanks for watching.
Looking forward to this! Is there a specific reason, that the formula refuses to populate any header for the value column? Would be great if that could be either blank if you add nothing or a custom value, like “sales in ” without having to head down the Vstack path .
Regarding "Values" header: not that I know of. That's one reason I ALWAYS VSTACK my own headings.
Great video! I have one question - how did you toggle off the filter indicators on the header row of the pivot table at the beginning? I can switch them off by turning off the headers for row labels, but I also want to keep these labels. What is the workaround?
I achieved that look with a little VBA wizardry. I usually hide those filter buttons because I give the users Slicers for filtering. See code below:
Sub DisableSelection()
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.PivotFields
pf.EnableItemSelection = False
Next pf
End Sub
You are amazing, Mr.❤
Thanks for saying such nice things.
This is great. Many thanks for explaining it so clearly. I have MS 365 and am on the Beta Channel, but I don't yet have this function (nor the related PIVOTBY). I guess it will come through soon!
Yeah; only about 50% of the BETA Channel users get the test features. You'll have it eventually.
My dear,
In versions "6" of the two files available for download, you can also include a header for the value column, using the following technique:
=LET(Array;GROUPBY(HSTACK(Sales[[#All];[ChannelName]]; Sales[[#All];[ProductName]]); Sales[[#All];[Amount]]; SUM; 3; 2; -3);
IF(SEQUENCE(ROWS(Array);COLUMNS(Array))=3;"Sales";Array)) 🤗
It's always great to see how other people solve the same problem. Great job!
In the last conditional formatted display, how can one clear out the unnecessary repetition of the values in the first column? For example, Under Channel, the value Online is repeated for all Product items. I would want it listed for the first one (3/4 Sleeve) and for it to remain blank for the remaining Product items. That way, the report becomes less cluttered.
This is a case where you would still want to use the "real" Pivot Tables. This function has but a fraction of the customization features of actual Pivot Tables.
Groupby function is really good and it is very helpful but it can return the values for only one column but in case in the abouve example if we need to add values for Sales amount and qty then is there any method to accomodate my requirement
You can define multiple aggregations by declaring them within an HSTACK function. For example:
=VSTACK({"Channel","Product","Sales"}, GROUPBY(HSTACK(Sales[ChannelName], Sales[ProductName]), Sales[Amount], HSTACK(SUM, AVERAGE, COUNT), 0, 2, -3) )
Let me know if this makes sense to you. Cheers.
@@bcti-bcti Thanks for your prompt reply.But my question is if am having a tabe of Channal, Product, Sales Qty and Sales amount and contribution amount , how to i make a formula using groupby function to show the sum of Sales Qty, Sales amount and contiribution amount
@@Aasmv2013 I now understand. The formula is a bit complex. I have created a sample file with the formula so you can reverse engineer my solution. If you are familiar with HSTACK, VSTACK, SORT, and UNIQUE... you should be able to understand it and change it for your needs. Let me know if you need assistance. Cheers.
www.bcti.com//wp-content/YT_Downloads/GROUPBY_Totals.xlsx
@@bcti-bcti it is really awesome now i can access your downloaded file thanks a lot but could you please share the separate videos for this case so that lot of users will be used
@@Aasmv2013 I'll put it on the list, but it may be a few weeks before it runs through the queue. Thanks.
Public Service Announcement: Pay no attention to that loud "Boom" you just heard - it was just my head exploding while watching this video.😆 Now I'm gonna have to channel my inner "bovine" and ruminate for a minute, or two, on your explanations. This is masterful stuff, and I'm already imagining applications of this function in my spreadsheets. In the meantime, though, could you please explain, however briefly as needed, why these formulas are returning single and double square brackets all over the place? Just finished the vid on structured references which made plenty of sense, but I'm not doing so well on the bracket situation in these formulas. Any clarification will be a big help.
The square brackets are standard database nomenclature to account for field names that include spaces. For example: if a formula saw a reference to COUNTRY CODE, it could be interpreted as 2 fields, "Country" and "Code", or as a single field "Country Code". By including square brackets, we remove any ambiguity. It's either 2 fields, [Country] and [Code], or 1 field, [Country Code].
Proper Excel Data Tables make use of these square brackes for fields (i.e., columns). If the headings are to be included, [#ALL] refers to the heading. Since we need to know which heading is being targeted, the bracketed item that immediately follows [#ALL] is the target field data. These have to be treated as a single item, so an additional set of square brackets surrounds the set.
I hope this helps. Maybe someone should make a video explaining this in greater detail 🤣.
Dya think?🤔@@bcti-bcti
Seriously, though, I'm pretty clear on the fixed references now, but the references to columns, say, [[ID], for example. Why two brackets on one side and just one on the other?
I'm uncertain where you are seeing [ [ID] in the example (or is that just your own example?) Where I have [ [some column name], there is usually following it another column reference with double brackes on the end, like [some other column name] ]. This would be treated as a single, multi-column reference like [ [ID]:[Emplyee Name] ]
Did that help?
Apologies - that was my own example. Here is part of one of the GROUPBY formulas "GROUPBY(Sales[[#All],[ProductName]:[ChannelName]], Sales[[#All],[Amount]]" I'm most curious about how these are working.
Fantastic
i need to add goupby function how can i achieve ds
Thanks for watching. The function has yet to be rolled out to the general public. If you'd like to try the function out while it is development, you can become a member of the insiders Beta program, so you can test out new features. Go to this link below.
insider.microsoft365.com/en-us/join/windows
Do you have any idea as to when it would be available to all 365 users?
No, sorry. They don't tell me things like that. Hopefully, soon.
@@bcti-bcti, thank you for your reply
How did you get this function..
I am part of the "Beta Channel" user base. It's about a 50/50 on which new features/functions we get to test before the general user base.
@@bcti-bcti how to become part of beta channel userbase..
GO to this link:
insider.microsoft365.com/en-us/join/windows
@@curious1731
Checking daily for weeks now to see if I have GroupBy & PivotBy functionality yet...
Yeah; no official word yet, but hopefully... soon.
how can get group by function in office 365? i am already subsriber of one drive
The function has yet to be rolled out to the general public. If you'd like to try the function out while it is development, you can become a member of the insiders Beta program, so you can test out new features. Go to this link below.
insider.microsoft365.com/en-us/join/windows
how can i get it office 365
If you’re talking about just getting office 365, you just need to buy a subscription from Microsoft. You can do that on their website office.com.
If you’re asking about how to become a member of the insiders Beta program, so you can test out new features, go to this link below.
insider.microsoft365.com/en-us/join/windows
i am alreay paid one drive subsriber,sir
@@bcti-bcti
April 2024. =GROUPBY is up and running (at least on Beta). I don't know when it was actually included, but who cares.
OK, so, using your file (saved once for info and again, deleting everything except the base table for practice) I'm going to comment as I go instead of waiting for the end of the video.
The first thing I've noticed is no dropdown for functions in my version. There was, however a dropdown for the header name generator and again later for totals/subtotals. It didn't format the numbers either (Version 1)... OK, I formatted the Amount column in the original table to accounting (no currency sign). Now it auto formats the result accordingly (Version 2). Yay!...Argh! Version 3 onwards, no number format again. How strange. Oh well, onwards and upwards....No further issues.
Did GROUPBY auto format the subtotals and totals or did you do that yourself for visual effect for our benefit?...Ah, OK, just got to the Conditional Formatting part of the video so ignore my question. Still, at least it shows I watched to the end and paid attention.😉
In conclusion, a great video on a great function. I can see me having some fun with it. As you said, no good/bad, just options. Many thanks.
So glad to see you're enjoying the content. I got a smile regarding your play-by-play commentary on the video. Thanks for watching!
@@bcti-bcti When you record your video on the advanced filtering, any chance of a quick explanation of the formulas you used for your conditional formatting please? Even as I looked at them as I copied and pasted them, I was completely baffled. No need for an explanation of the conditional formatting itself. There are plenty of videos about that.
@@ianl1052 I'll put that on the "short list". Thanks for the suggestion.
Check out today's video post. I answer your question about how I performed all of the automatic artwork using Conditional Formatting.
@@bcti-bcti
Watched, liked and commented. Thanks.
you first how and wher eit find plz do this first
I’m sorry; I don’t understand your request. Can you please restate? Thank you.
I sould not fidn groupby function in office 365 even though i am subcriber of One drive which offers free of office 365@@bcti-bcti
Worthless.
Microsoft hasn’t released it yet.
Here's hoping it will be released sooner than later.
@@bcti-bcti Current channel people got GORUPBY and PIVOTBY on 9/25. By the way, your video was anything but worthless. Lots of people had this function a year ago and were making use of it as soon as it was released.
Does the actual group function allow you to do two columns the way you did it in this video?
@@aremoteforu Yes. Suppose your table has 4 columns (A thru D) where the data starts on row 2 and has 50 rows. The first 3 columns are "Country, State, Product" and the 4th column is "Sales". You want to group by "Product" then subgroup by "Country". The formula would be as follows:
=GroupBy(ChooseCols(A2:C51, 3, 1), D2:D51, SUM)
The ChooseCOls function will select the 3rd and 1st columns to be used by GroupBy.
Hope this makes sense. hanks for watching.
@@bcti-bcti so no need to hstack it first