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

  Рет қаралды 15,661

BCTI

BCTI

Күн бұрын

Пікірлер: 85
@JM-mb6tf
@JM-mb6tf 5 ай бұрын
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
@bcti-bcti 5 ай бұрын
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
@DanKnight 11 ай бұрын
Brilliant! Looking forward to when this function becomes live.
@bcti-bcti
@bcti-bcti 5 ай бұрын
I think it's finally been universally released.
@karenjoy511
@karenjoy511 10 ай бұрын
Love the step-by-step presentation and excellent explanation.
@bcti-bcti
@bcti-bcti 10 ай бұрын
Thank you for taking the time to say such nice things, and for watching.
@stevereed5776
@stevereed5776 10 ай бұрын
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
@bcti-bcti 10 ай бұрын
We really appreciate you helping us grow the channel.
@fabianmolyneux2308
@fabianmolyneux2308 11 ай бұрын
Fantastic function. I can see I might use this more commonly while working through data analysis. Brilliant stepped out explanation Thankyou
@bcti-bcti
@bcti-bcti 10 ай бұрын
Thank you for taking the time to watch.
@joukenienhuis6888
@joukenienhuis6888 10 ай бұрын
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
@bcti-bcti 10 ай бұрын
Thank you so much for watching!!
@Officeinstructor
@Officeinstructor 9 ай бұрын
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
@bcti-bcti 9 ай бұрын
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
@IlaPatel811 11 ай бұрын
Loved the detailed explanation, Thank you!
@bcti-bcti
@bcti-bcti 11 ай бұрын
Thank you for saying so and taking the time to watch. (big smile)
@chiengsaby2867
@chiengsaby2867 11 ай бұрын
Thanks. This will certainly eliminate VBA to do the pivot refresh. Great! Perspicuously explained.
@bcti-bcti
@bcti-bcti 11 ай бұрын
Thank you so much! (great and accurate use of the word "Perspicuously")
@rahulsosonawane
@rahulsosonawane 10 ай бұрын
It's really though explanation. Thank you mentor
@bcti-bcti
@bcti-bcti 10 ай бұрын
Thank you for thinking us us a a mentor. That is high praise.
@adreamer9999
@adreamer9999 10 ай бұрын
Great video. Thank you!
@bcti-bcti
@bcti-bcti 10 ай бұрын
My pleasure. Thanks for watching.
@tibibara
@tibibara 10 ай бұрын
Excellent tutorial!💯
@bcti-bcti
@bcti-bcti 10 ай бұрын
Thank you for taking the time to watch and say nice things.
@JoseAntonioMorato
@JoseAntonioMorato 11 ай бұрын
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
@bcti-bcti 11 ай бұрын
It's always great to see how other people solve the same problem. Great job!
@kennethstephani692
@kennethstephani692 Ай бұрын
Great video!
@bcti-bcti
@bcti-bcti Ай бұрын
@@kennethstephani692 Two comments in one afternoon. I’ll take that as a win. Thanks.
@CAKPRashique
@CAKPRashique 11 ай бұрын
You are amazing, Mr.❤
@bcti-bcti
@bcti-bcti 11 ай бұрын
Thanks for saying such nice things.
@rleigh5204
@rleigh5204 9 ай бұрын
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
@bcti-bcti 9 ай бұрын
Good to know. I'll have to try that. Thanks for watching!
@maciejkopczynski55
@maciejkopczynski55 10 ай бұрын
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
@bcti-bcti 10 ай бұрын
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
@iankr 10 ай бұрын
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
@bcti-bcti 10 ай бұрын
Yeah; only about 50% of the BETA Channel users get the test features. You'll have it eventually.
@budams4668
@budams4668 11 ай бұрын
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
@bcti-bcti 11 ай бұрын
Regarding "Values" header: not that I know of. That's one reason I ALWAYS VSTACK my own headings.
@matlholelosaba4977
@matlholelosaba4977 9 ай бұрын
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
@bcti-bcti 9 ай бұрын
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
@hankgrimes1806 10 ай бұрын
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
@bcti-bcti 10 ай бұрын
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
@hankgrimes1806 10 ай бұрын
Dya think?🤔@@bcti-bcti
@hankgrimes1806
@hankgrimes1806 10 ай бұрын
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
@bcti-bcti 10 ай бұрын
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
@hankgrimes1806 10 ай бұрын
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
@yousrymaarouf2931 11 ай бұрын
Fantastic
@JonathanDyer651005
@JonathanDyer651005 10 ай бұрын
Checking daily for weeks now to see if I have GroupBy & PivotBy functionality yet...
@bcti-bcti
@bcti-bcti 10 ай бұрын
Yeah; no official word yet, but hopefully... soon.
@UtuDudas
@UtuDudas 9 ай бұрын
Do you have any idea as to when it would be available to all 365 users?
@bcti-bcti
@bcti-bcti 9 ай бұрын
No, sorry. They don't tell me things like that. Hopefully, soon.
@UtuDudas
@UtuDudas 9 ай бұрын
​@@bcti-bcti, thank you for your reply
@adbapatla940
@adbapatla940 9 ай бұрын
how can get group by function in office 365? i am already subsriber of one drive
@bcti-bcti
@bcti-bcti 9 ай бұрын
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
@bunmi4164 9 ай бұрын
i need to add goupby function how can i achieve ds
@bcti-bcti
@bcti-bcti 9 ай бұрын
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
@curious1731 10 ай бұрын
How did you get this function..
@bcti-bcti
@bcti-bcti 10 ай бұрын
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
@curious1731 10 ай бұрын
@@bcti-bcti how to become part of beta channel userbase..
@bcti-bcti
@bcti-bcti 10 ай бұрын
GO to this link: insider.microsoft365.com/en-us/join/windows @@curious1731
@ianl1052
@ianl1052 6 ай бұрын
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
@bcti-bcti 6 ай бұрын
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
@ianl1052 6 ай бұрын
@@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
@bcti-bcti 6 ай бұрын
@@ianl1052 I'll put that on the "short list". Thanks for the suggestion.
@bcti-bcti
@bcti-bcti 5 ай бұрын
Check out today's video post. I answer your question about how I performed all of the automatic artwork using Conditional Formatting.
@ianl1052
@ianl1052 5 ай бұрын
@@bcti-bcti Watched, liked and commented. Thanks.
@adbapatla940
@adbapatla940 9 ай бұрын
how can i get it office 365
@bcti-bcti
@bcti-bcti 9 ай бұрын
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
@adbapatla940 9 ай бұрын
i am alreay paid one drive subsriber,sir @@bcti-bcti
@adbapatla940
@adbapatla940 9 ай бұрын
you first how and wher eit find plz do this first
@bcti-bcti
@bcti-bcti 9 ай бұрын
I’m sorry; I don’t understand your request. Can you please restate? Thank you.
@adbapatla940
@adbapatla940 9 ай бұрын
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
@KevinBuchanan66 Ай бұрын
Worthless. Microsoft hasn’t released it yet.
@bcti-bcti
@bcti-bcti Ай бұрын
Here's hoping it will be released sooner than later.
@brianxyz
@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.
Microsoft Excel - Unthinkable Lookup Tricks
11:10
BCTI
Рет қаралды 4,7 М.
UFC 308 : Уиттакер VS Чимаев
01:54
Setanta Sports UFC
Рет қаралды 849 М.
The Excel Functions Almost Everyone Overlooks (Better Than SUMIFS)
10:29
MyOnlineTrainingHub
Рет қаралды 46 М.
Use slicers with PIVOTBY, GROUPBY & FILTER in Excel | Excel Off The Grid
8:07
Try The DGET Function Instead of INDEXMATCH & XLOOKUP
12:29
Kenji Explains
Рет қаралды 54 М.
Try This Instead of the XLOOKUP
10:06
Kenji Explains
Рет қаралды 124 М.
LET Function Transforms Messy Formulas - Comprehensive Guide
18:25
MyOnlineTrainingHub
Рет қаралды 184 М.
PivotTable Tricks That Will Change the Way You Excel (Free File)
17:47
MyOnlineTrainingHub
Рет қаралды 145 М.