Try This New Formula Instead of Pivot Tables 

🚀Get the FREE Data Analytics Guide from Hubspot: clickhubspot.com/g8g

PivotTables will NEVER go away. Many of us analysts still need ability to drill down on data subsets.

Nobody does it better, Kenji. Thank you for the instruction.

what a great video, very practical to follow, this helps my work a lot, greetings from Timor-Leste

I enjoy watching your videos. Your clarity and the fact that you add a sample document to follow along.

I like your content a lot. Please keep up the good work going. Thank you 😊

I love the versatility of pivot tables if I’ve been asked to present data analysis, however, I’ve been using excel a lot more to create simple “dashboards” where I can return data from a few sources based on the entry of a reference, pivot tables and slicers were never friendly for this (and to be fair it’s not what they were designed for) but I can see this functionality being really useful to me , thanks for the no nonsense explanation 👍 thanks to the algorithm too 🙌

To be honest, Pivot table is best.

You are doing very noble job and helps a lot in education people. Your style of teaching is very very fluent easy to understand

I love all your videos, It helps me a lot. Thanks Kenji! 😀💯

Good to see such features, let's see if this could go beyond PV tables, which sometimes seems infinite but boundaries are plenty.

Seen this one before but now know the logic of it ...Thanks K ...keep posting dost 😊

Why so many pivottable haters, when they are so easy.

Really enjoy your videos…straight forward and easy to absorb. Looking forward to using this feature, when available. I envision this will help me track total number of job classifications across branches to keep executive managers informed of where job vacancies may begin to affect job performance and service delivery.

Looks good and very clearly explained. Two questions: 1. Presumably if you use Slicers on your table, GROUPBY only shows visible data? 2. Given the result is a dynamic array, how do you format the results so they always show the same format regardless of how big the array ends up being? I only saw you manually format the cells based on the current size.

Thanks for the videos. I like how you explain to understand.

Why is this function not available on latest version of excel..

Thank you for your valuable contribution, it's an excellent tutorial. Thank you! 😊

Great video and thanks for the clever formula. I tried to use it on my Excel but the Groupby function does not exist. Do you know how to install this function? Is there an "add on"? Thanks

Love this! Going to redesign one of my dashboards today!

love this function and thanks for the demonstration. how long does it take to get access to this stuff in excel? i just set myself up as a beta user and the function still isn't available to me. I'm also hoping to be able to use the REGEX functions that are in beta. Does is take a couple days to kick in??

That's awesome. Thanks for letting us know. Really useful

Good going kenji

Very nice information and teaching sir.. from India North East

Question is how can it be controlled when I'm using a slicer

thank you so much for everything 🌹

Can you tell me which version available in groupby function...

Very educative video Sir can you please share which software do you use for video editing Please Share

You can just click in the pivot table and press refresh. Do not need to go to the ribbon.

Thanks for sharing. Awesome!

No doubt it is a very good function but pivot tables are better than this. In this video, you made many changes in the formula to get different results according to your requirements but in the pivot table, it is straightforward to make the changes very easily. ☺

Thanks Sir 👍🏻👍🏻

Hello kenji nice explanation. Will your playlist from beginers to expert , will help in understanding excel from scratch? And then we can proceed to more higher levels ?

Hi Thanks a lot for this information, also please can shear the practice file

Can you make videos related to google sheets also. Please

Thanks. Way simpler than pivot table.

Cool. Thanks for detail video. I can retire Pivot table in my simple excel.

@KenjiExplains please explain why my Excel file doesn't have this "Groupby" function. My one is MS office 2021

Excellent 👌 briefing

Hi, if one row data is required Unique and sumif also can be used

Awesome as always 👍 However, Why can't I find it in my excel 365 version or are there any method that I have to add in, pls give me suggestions?

Thanks!

Wow this looks nice compare to pivot! Thanks to this new formula ❤

Can we add slicers to tables made from Groupby?

Really this is Amazing, Keep going.

I really hate videos that promote functions that aren't GA'ed yet. Microsoft still hasn't release the GroupBy function - similarly, they haven't release a lot of other Beta functions. I get it - the functions/features need to be tested/validated - but they don't have their priorities in order! this function looks great...will be glad when they FINALLY DO RELEASE it!!!

Thanks brother

Groupby is only available in beta version?

Wow, this is gold! Thanks!

I use VBA to update pivot tables automatically when source data has changed.

Hello Kenji, i can't find =Groupby Function on my Excel ! can u Assist me if you please

How will it impact in terms of performance if we use formulae instead of pivot tables?

This is the query equivalent in GSheets. But with limitations. PivotTables will stay because they can drill down.

What is the use case for why one wouldnt just use pivot tables?

If i use groupby instead of pivot tables, then how to create data visual like graphs and charts for data analysis with this?

1st view I like your videos and more informative

Good teacher👍

How about for the repeated items? What if we dont want to see repeated items

in which version onwards this formula is available?

Excel just need to send an update that allows Pivot table to auto update. If that happens then what would be the use of group by function?

Great formula!! Thanks

Cool new formula 🙏🏻

We can use unique and sumif functions to do the same.

I couldn't find the GROUPBY function. I even updated my Excel version but don't know why don't find it :/

How about if i need to filter? That funtion does not allow me to do that. It's harder to perfom the "Pivot" at all.

One scenario. Same item sailed with different prices in different days. In table mentioned below data: Date, Item Name, Quantity, Price, Value. To find total average price of item in Pivot Table you can go & put function in Fields items & sets like =Value / Quantity. In new functions like Groupby or Pivotby can’t get it yet. Or I can’t get it up to now.

Very nice

How to do the group by date/daily?

Hi Kenji, like your videos but please make it clear that these formulas are only available to insiders for now and will be rolled out to the general public slowly at a lated date.

What if you had annual data and needed to add a date filter to get monthly?

Pivot tables are static once laid out and will not be affected by other formulas in the workbook so with a very large table and other calculations involved, I wonder how GROUPBY can affect performance.

I am using Microsoft professional plus 2021 and i didn't find both formulas in my excel sheet

So if these formulas do everything that a pivot table does... What additional thing do we get other than self updating?

Thanks! 👍🏻

Hello. I'm using office 365, I didn't see groupby Function so what's the problem?

Dear Kenji, With slices, the filter gets much better: =GROUPBY(CHOOSECOLS(Table1[#All],3,2),Table1[[#All],[Sales]],SUM,3,,,,BYROW(Table1[Product],LAMBDA(a,AGGREGATE(3,5,a)))) - filter by product =LET(header,PIVOTBY(Table13[[#All],[Country]],Table13[[#All],[Quarter]],Table13[[#All],[Sales]],SUM,,,,,, BYROW(Table13[Quarter],LAMBDA(a,AGGREGATE(3,5,a)))), IF(SEQUENCE(ROWS(header),COLUMNS(header))=1,"Country",header)) - filter by quarter🤗

So many formula to remember only to replace a simple pivot

I like what you showed. However refreshing a pivot table is much easier than what you demonstrated. All you do is right click and select refresh. That is faster than going to analyze and then refresh

I have like 2lacks of data how can I solve it I want all the different values and name and I”d ..if I use the pivot table .the results are not good bcos all the duplicate value are deleted..what can I done the data

From where we can get groupby function. In my excel sheet, it is not appearing.

which ms excel version?

Do these functions work only when the base data is structured in TABLE format?

Is this a new update or add in? I just tried it and I dont have it.

Can i use this formula in 2019 excsl version

Sir here slicer are not working with groupby and pivot by

Can you make a video on Knime?

I m not getting Groupby functions in my Excel 365

I can't find this groupby function on my excell

*groupby function is not showing in ms office 2024 and not in google sheet? how can i get this function? Pls. Respond.*

Help, i don't have this kind of function in my Excel

GROUPBY IS NOT AVAILABLE IN ALL VERSIONS. I HAVE 365 APPS (2406) AND IT DOES NOT AVAILABLE.

cool formula thanks

Is the function available on Microsoft 365.. using Microsoft 365 but not able to get these functions

What is the language here? Is that DAX?

why groupby is not showing in my excel?

But when i need slisers?

how can i don't have that formula in my excel (=GROUPBY)

Nice.

Sir please make one video on how fresher get their first data Analytics job without any past experience, also who also want video on this topic please like 👍🏻👍🏻

you can just right click on the pivot table and select refresh. It isn't that big of a deal. Great lesson though.