PivotTables but better, using new Excel function: GROUPBY PIVOTBY

Excel launched two functions that emulate what PivotTables can do that are easier to write than SUMIFS. GROUPBY aggregates a value in rows and PIVOTBY aggregates values by rows and columns. Superior to PivotTables, they refresh automatically and can do more functions such as MEDIAN and my favourite ARRAYTOTEXT to create a comma-separated list of a text field: e.g.
France: Lyon, Marseille, Paris
UK: London, Bristol.
There are optional fields like headers, totals, sort, and filter plus I also walk through how to only aggregate unique values like DISTINCTCOUNT or distinct comma-separated lists, here is the formula for the latter:
=GROUPBY({Categorical column},{Text_value column},LAMBDA(x,ARRAYTOTEXT(SORT(UNIQUE(x)))))
I reference my other videos in this one:
LAMBDA: • Excel's custom functio...
Dynamic array functions: • 24 Dynamic array funct...
You can download the example files here: www.xlconsulting-asia.com/you...
Follow Solve & Excel's monthly newsletter for monthly Excel updates: solveandexcel.ca/excel_news/
00:00 Introduction
00:55 Group by
02:26 Group by non-adjacent columns
03:46 Groupby to make comma-separated list
04:53 Row and column pivot by with PIVOTBY
05:59 Filter
06:45 Count unique and text unique

[@dougmphilly]

best explanation of lambda as well.

[@learnspreadsheets]

Glad you liked it! I have another longer video on lambda too (but honestly I feel google sheets implemented named functions in a better way)

[@thebhaskarjoshi]

Loved it!

[@learnspreadsheets]

Thank you!!

[@nigilv.d4237]

Wow great news sir

[@learnspreadsheets]

Game changing!!

[@romany4all]

Great

[@learnspreadsheets]

Game changing!!!

[@user-lw8cg8uv9f]

excellent :) will it be possible to format total rows etc. (rows will change position if data is added)

[@learnspreadsheets]

Hi you can put sub totals at top or bottom, & if new rows get added to the data the totals move automatically!

[@Kriya40]

Perfect! Would it be able to do the distinct count by Pivotby or Groupby?Could you please advise?

[@learnspreadsheets]

I show that in the video! 😃 have a watch again!

[@adbapatla940]

where i foun this group by function in Excel? i could not find in office 365

[@learnspreadsheets]

Hi, it’s only released for the beta channel for now. That’s indicated on the thumbnail, description & in the video itself, it’ll come to the other channels soon

[@nkn108]

is it possible to apply different functions to each value?

[@learnspreadsheets]

It’s not possible with groupby nor is it possible with pivot tables, you can do separate calca with sumifs countifs if you want that

[@nkn108]

@@learnspreadsheets possible. with hstack for valuesand function. i saw it on another video and tried, it works

[@OrkunAkar]

I updated my excel 365 and I stıll cannot see these new functions such as groupby pivotby ... Does any one know how to resolve this issue? thanks

[@learnspreadsheets]

It’s only been rolled out to the Beta channel for now, hence I have “beta” on the thumbnail & I explain that in the last 20 seconds of the video. It’s also not yet available to all beta users but will be soon

[@OrkunAkar]

@@learnspreadsheets okay. thank you very much for the update. I panıcked for a sec ))

[@PiggyBankAccountantFR]

I dont have them either!!! Even if im in the beta channel! We have to be patient 🥹

[@darkavenger100]

what's the date that it will be available for all?

[@learnspreadsheets]

I can’t know, Microsoft take things out of Beta a while after, so it could be 3 months or a year, unlikely more I would say

[@JoseAntonioMorato]

Dear David,
I really liked the video, but I was sad because the function is giving an error in my PT-BR version. 😒🤗

[@learnspreadsheets]

Thanks, oh no sorry to hear, report it to Microsoft! It’s probably a bug that will be fixed soon

[@patricklonski]

@@learnspreadsheets Pivotby causes workbook crashes

[@shadow_gaming_sk]

Why in my excel not showing in this function, i am using Excel 365

[@learnspreadsheets]

It’s beta channel only. I mention this in the last 20s, how to check which channel you’re on

[@shadow_gaming_sk]

Thank you

[@agnesc4768]

Thanks for sharing, David. Can anyone download a beta version?