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

Using Excel, learn how to make an ENTIRE Pivot Table using a single formula. This brings automatically updating data Pivot Table-style reports. PLUS: 2 BONUS features!!!

File Download Link:

File with Conditional Formatting for Artwork:

00:09 Purpose of the GROUPBY Function
01:13 Release Announcement
01:23 Syntax for GROUPBY (Overview)
02:03 Syntax for GROUPBY (Details)
03:53 GROUPBY - Example Objectives
04:45 Single Column Groups without Headings
06:34 Single Column Groups with Headings
07:41 Grouping by Multiple Columns
08:27 Multi-Column Groups with Subtotals
09:39 Multi-Column Groups with Subtotals Sorted
14:06 Multi-Column Groups with Custom Headings
17:48 BONUS: Filtering Groups
18:37 BONUS: Apply Artwork with Conditional Formatting
20:08 Wrapup & Summary

[DanKnight]

Brilliant! Looking forward to when this function becomes live.

[JM-mbtf]

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.

[stevereed]

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.

[karenjoy]

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

[fabianmolyneux]

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

[joukenienhuis]

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

[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:
Sales[[#All];[ProductName]]); Sales[[#All];[Amount]]; SUM; 3; 2; -3);
🤗

[chiengsaby]

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

[IlaPatel]

Loved the detailed explanation, Thank you!

[rleigh]

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.

[rahulsosonawane]

It's really though explanation. Thank you mentor

[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!

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

[budams]

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 <currency>” without having to head down the Vstack path .

[hankgrimes]

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.

[maciejkopczynski]

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?

[matlholelosaba]

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.

[bunmi]

i need to add goupby function how can i achieve ds

[JonathanDyer]

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

[ianl]

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.