Excel's New GROUPBY Function (w/ Bonus Features)

[@JM-mb6tf]

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.

[@bcti-bcti]

Thanks so much. We've only been around (on KZbin) 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.

[@DanKnight]

Brilliant! Looking forward to when this function becomes live.

[@bcti-bcti]

I think it's finally been universally released.

[@karenjoy511]

Love the step-by-step presentation and excellent explanation.

[@bcti-bcti]

Thank you for taking the time to say such nice things, and for watching.

[@stevereed5776]

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.

[@bcti-bcti]

We really appreciate you helping us grow the channel.

[@fabianmolyneux2308]

Fantastic function. I can see I might use this more commonly while working through data analysis. Brilliant stepped out explanation Thankyou

[@bcti-bcti]

Thank you for taking the time to watch.

[@joukenienhuis6888]

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

[@bcti-bcti]

Thank you so much for watching!!

[@Officeinstructor]

Amazing tutorial, thumbs Up. I tried to find your name in the video, or the channel or even the website but I couldn't.

[@bcti-bcti]

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.

[@IlaPatel811]

Loved the detailed explanation, Thank you!

[@bcti-bcti]

Thank you for saying so and taking the time to watch. (big smile)

[@chiengsaby2867]

Thanks. This will certainly eliminate VBA to do the pivot refresh. Great! Perspicuously explained.

[@bcti-bcti]

Thank you so much! (great and accurate use of the word "Perspicuously")

[@rahulsosonawane]

It's really though explanation. Thank you mentor

[@bcti-bcti]

Thank you for thinking us us a a mentor. That is high praise.

[@adreamer9999]

Great video. Thank you!

[@bcti-bcti]

My pleasure. Thanks for watching.

[@tibibara]

Excellent tutorial!💯

[@bcti-bcti]

Thank you for taking the time to watch and say nice things.

[@JoseAntonioMorato]

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)) 🤗

[@bcti-bcti]

It's always great to see how other people solve the same problem. Great job!

[@kennethstephani692]

Great video!

[@bcti-bcti]

@@kennethstephani692 Two comments in one afternoon. I’ll take that as a win. Thanks.

[@CAKPRashique]

You are amazing, Mr.❤

[@bcti-bcti]

Thanks for saying such nice things.

[@rleigh5204]

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.

[@bcti-bcti]

Good to know. I'll have to try that. Thanks for watching!

[@maciejkopczynski55]

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?

[@bcti-bcti]

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

[@iankr]

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!

[@bcti-bcti]

Yeah; only about 50% of the BETA Channel users get the test features. You'll have it eventually.

[@budams4668]

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 .

[@bcti-bcti]

Regarding "Values" header: not that I know of. That's one reason I ALWAYS VSTACK my own headings.

[@matlholelosaba4977]

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.

[@bcti-bcti]

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.

[@hankgrimes1806]

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.

[@bcti-bcti]

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 🤣.

[@hankgrimes1806]

Dya think?🤔@@bcti-bcti

[@hankgrimes1806]

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?

[@bcti-bcti]

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?

[@hankgrimes1806]

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.

[@yousrymaarouf2931]

Fantastic

[@JonathanDyer651005]

Checking daily for weeks now to see if I have GroupBy & PivotBy functionality yet...

[@bcti-bcti]

Yeah; no official word yet, but hopefully... soon.

[@UtuDudas]

Do you have any idea as to when it would be available to all 365 users?

[@bcti-bcti]

No, sorry. They don't tell me things like that. Hopefully, soon.

[@UtuDudas]

​@@bcti-bcti, thank you for your reply

[@adbapatla940]

how can get group by function in office 365? i am already subsriber of one drive

[@bcti-bcti]

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

[@bunmi4164]

i need to add goupby function how can i achieve ds

[@bcti-bcti]

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

[@curious1731]

How did you get this function..

[@bcti-bcti]

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.

[@curious1731]

@@bcti-bcti how to become part of beta channel userbase..

[@bcti-bcti]

GO to this link: insider.microsoft365.com/en-us/join/windows @@curious1731

[@ianl1052]

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]

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!

[@ianl1052]

@@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]

@@ianl1052 I'll put that on the "short list". Thanks for the suggestion.

[@bcti-bcti]

Check out today's video post. I answer your question about how I performed all of the automatic artwork using Conditional Formatting.

[@ianl1052]

@@bcti-bcti Watched, liked and commented. Thanks.

[@adbapatla940]

how can i get it office 365

[@bcti-bcti]

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

[@adbapatla940]

i am alreay paid one drive subsriber,sir @@bcti-bcti

[@adbapatla940]

you first how and wher eit find plz do this first

[@bcti-bcti]

I’m sorry; I don’t understand your request. Can you please restate? Thank you.

[@adbapatla940]

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

[@KevinBuchanan66]

Worthless. Microsoft hasn’t released it yet.

[@bcti-bcti]

Here's hoping it will be released sooner than later.

[@brianxyz]

@@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.