Try This New Formula Instead of Pivot Tables

Рет қаралды 222,799

Күн бұрын

Learn the GROUPBY Function in Excel to replace Pivot Tables.
🚀Get the FREE Data Analytics Guide from Hubspot: clickhubspot.com/g8g
🆓 DOWNLOAD Free Excel file for this video: careerprinciples.myflodesk.co...
Stop using pivot tables, you can use the new GROUPBY function in Excel instead. This function is better because it updates automatically. For example, when the original data changes, the pivot table doesn't update. Instead, you need to press the refresh button every time. However, because the GROUPBY is a function, it updates automatically. We'll also cover the PIVOTBY function, which unlike the GROUPBY also allows for column input values. In this video we'll go over the GROUPBY and the PIVOTBY step by step through all the conditions such as sorting, filtering, adding field headers, and total depth.
LEARN:
🔥Power BI for Business Analytics: www.careerprinciples.com/cour...
📈 The Complete Finance & Valuation Course: www.careerprinciples.com/cour...
👉 Excel for Business & Finance Course: www.careerprinciples.com/cour...
🚀 All our courses: www.careerprinciples.com/courses
SOCIALS:
📸 Instagram - careerprinc...
🤳 TikTok - / career_principles
🧑‍💻 LinkedIn - / careerprinciples
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Chapters:
0:00 - The Problem with Pivot Tables
1:06 - GROUPBY Function
2:45 - GROUPBY with % of Total
5:18 - GROUPBY Adding Headers and Totals
6:45 - Sorting with GROUPBY
7:35 - Adding More Than One Row
8:40 - Filtering with GROUPBY
9:49 - PIVOTBY Function

Пікірлер: 147

@KenjiExplains Ай бұрын

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

@davidferrick 25 күн бұрын

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

@nevermore17011990 10 күн бұрын

correct, and the ability of create measure when data store in DATA MODEL

@roberth.9558 20 күн бұрын

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

@jeffersonsumbobo4167 Ай бұрын

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

@JonesFamilyRanch Ай бұрын

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.

@ramblermaddy7691 Ай бұрын

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

@KenjiExplains Ай бұрын

Thank you for the comment! It means a lot :)

@rickswineberg 29 күн бұрын

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

@dvdlog 27 күн бұрын

If you’re dealing with data model you should learn some dax in order to get your data consistent and ready for exploration.

@hermask815 11 күн бұрын

Hitting refresh puts mental stress on some people,it seems. Knowing the plausibility of your data should be natural on some levels of company hierarchy. Is that too much asked for ?

@MrDhunpagla Ай бұрын

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

@manindranirmal 29 күн бұрын

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

@monicacalvo1006 21 күн бұрын

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

@sheet-chat 18 күн бұрын

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

@olaolaitan7834 Ай бұрын

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

@KenjiExplains Ай бұрын

Thank you for your comment :)

@thomastaylor6595 14 күн бұрын

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 🙌

@camlex6310 Ай бұрын

Great formula!! Thanks

@patrickp8315 22 күн бұрын

Thanks. Way simpler than pivot table.

@bongperez6266 23 күн бұрын

Wow, this is gold! Thanks!

@aicx40 26 күн бұрын

Thanks for sharing. Awesome!

@aremoteforu 26 күн бұрын

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

@casonalimudgal7986 18 күн бұрын

Really this is Amazing, Keep going.

@ezkul95828 21 күн бұрын

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

@tastyehealth 27 күн бұрын

Good teacher👍

@manjit.sarania 12 күн бұрын

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

@nishanth4323 Ай бұрын

Cool new formula 🙏🏻

@UtkarshKumar-mp2ls Ай бұрын

Good going kenji

@KenjiExplains Ай бұрын

Thank you!

@maher_a._alzoubi 3 күн бұрын

thank you so much for everything 🌹

@iduncanw 19 күн бұрын

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.

@btbb3726 Ай бұрын

Thanks! 👍🏻

@harshdas7009 25 күн бұрын

Thanks brother

@anuragkhatri8104 24 күн бұрын

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 ?

@bijaytanty8434 10 күн бұрын

Thanks Sir 👍🏻👍🏻

@UpForDebate-999 18 күн бұрын

Thanks!

@tomr.5217 15 күн бұрын

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

@mikelawton6053 26 күн бұрын

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??

@alm9707 21 күн бұрын

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

@rampalkhatak3213 16 күн бұрын

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

@kameshsharma5164 8 күн бұрын

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

@md.alamgirhossain905 29 күн бұрын

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

@PeterNwachineke 29 күн бұрын

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

@nongartinunt2014 21 күн бұрын

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?

@amosnliz 5 күн бұрын

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

@TayZarLin-kt8td 15 күн бұрын

Nice!

@rajeshkadam1602 6 күн бұрын

Amazing

@narendrakumarchauhan8928 29 күн бұрын

Can i use this formula in 2019 excsl version

@shivavenna315 25 күн бұрын

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

@rasheednayakkan6750 Ай бұрын

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

@waynez8136 Ай бұрын

Newly released functions that are only available to insiders. I just double-checked Google Sheets, and they are not available there either.

@jtmh31 28 күн бұрын

Because very few content creators mention this is in beta/preview for Microsoft 365 Office Insiders ONLY. When XLOOKUP was announced, it took a year before it was available. LAMBDA took two years. GROUPBY and PIVOTBY were announced back in November 2023, so you may see them both before years end. I'm to the point I'm going to downvote every video that doesn't mention preview at the beginning.

@dondgc2298 27 күн бұрын

@@jtmh31to his credit (somewhat) he does at least mention it about 90 seconds in. But I agree with your sentiment.

@Babyk4315 29 күн бұрын

Can you make a video on Knime?

@suvarnareddy6866 Ай бұрын

1st view I like your videos and more informative

@KenjiExplains Ай бұрын

Thanks so much!

@vasanthkumarm55 13 күн бұрын

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

@justinspencer-young3935 25 күн бұрын

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

@kapilanawarathne5746 9 күн бұрын

in which version onwards this formula is available?

@aspectoflife 26 күн бұрын

the best

@ArvinMartinez-of7tj 18 күн бұрын

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

@araratmaf 18 күн бұрын

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.

@MrAhmedadem 24 күн бұрын

So many formula to remember only to replace a simple pivot

@Gamerz05681 25 күн бұрын

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 👍🏻👍🏻

@willzinner8813 Ай бұрын

cool formula thanks

@KenjiExplains Ай бұрын

Awesome!

@mannymany4423 9 күн бұрын

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

@pandharinathjoshi6565 28 күн бұрын

Sir here slicer are not working with groupby and pivot by

@cg_yeomans9472 2 күн бұрын

How to do the group by date/daily?

@Heybat1 18 күн бұрын

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.

@marcymcflybv1558 11 күн бұрын

Osu!!! master! Bow!

@user-sy5rc6rb5j 14 күн бұрын

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

@MUHAMMADAzimKhan-jh3hm 19 күн бұрын

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

@diinitutor6379 13 күн бұрын

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

@rajkumarjain3697 20 күн бұрын

I m not getting Groupby functions in my Excel 365

@johnthedataanalyst 23 күн бұрын

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

@Washulaundrymats 2 күн бұрын

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

@user-dy6iz2ny6l Ай бұрын

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

@andreimurariu 21 күн бұрын

You get another set of data that would can manipulate

@gauravloya193 25 күн бұрын

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

@KenjiExplains 25 күн бұрын

No they also work without the table format :)

@khimseily 17 күн бұрын

I can't find this groupby function on my excell

@8126haseeb 12 күн бұрын

why groupby is not showing in my excel?

@VR-ie8xy 10 күн бұрын

But when i need slisers?

@juzershabbir6199 29 күн бұрын

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

@SeyPras. 29 күн бұрын

Require insider access.. If you’re using 365 personal you can head to File > Office > Help > Check for updates > advanced.. and change preview to insider.

@atul2047 13 күн бұрын

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

@jhow8990 29 күн бұрын

isn't pivot table more easy and convinence?

@Arpitr1697 23 күн бұрын

"groupby" is still not available in my excel

@Hassan_MM. 18 күн бұрын

❤

@NaveenKumar-ic1ky 17 сағат бұрын

It is not available

@Phamousman 29 күн бұрын

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.

@KenjiExplains 29 күн бұрын

Hey thanks I said that towards the start of the video

@dondgc2298 27 күн бұрын

@@KenjiExplainsyou did say it roughly 90 seconds in. But you can tell by the comments that it was an easy disclaimer to miss…hopefully this gets rolled out before the end of the year

@jessicasauberer3540 15 күн бұрын

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

@i_m_manjunath 18 күн бұрын

Why is using the STACK in Group by formula

@JoseAntonioMorato 29 күн бұрын

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🤗

@craig3402 23 күн бұрын

Discount on all courses?

@KenjiExplains 23 күн бұрын

Hi thanks for your interest, contact info@careerprinciples.com for course related questions! :)

@parvizdinmohammadi3078 18 күн бұрын

I think using of new technic needs to more experience.but Pivotable is simple.

@venkatraman2011 8 күн бұрын

Just refresh and see

@deanphillips8408 21 күн бұрын

great video but honestly microsoft drives me mad - i've been learning about groupby and pivotby for months and still no sign of it in my excel. i am current channel on a corporate 365 subscription, i know i can get transferred to a different channel that may get updated quicker but what is the point? if i do that i can't share my model with other users in my organisation!!!!!! also agree with other comments on existing pivot tables - dont understand why people have a problem with them, they are my go-to tool in excel, that and sumifs

@Techno-kidsTechnoHelpu 14 күн бұрын

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

@rifteeridmee 29 күн бұрын

=GROUPBY function is not available in my Office 365. I'm from Bangladesh

@MuhammadIqbal-os7dr 29 күн бұрын

Open the Excel file of MS Office, go to Accounts and under Upgrade, your insider will be written meta channel selected and click update option and restart computer

@samta7496 28 күн бұрын

Its only in Beta at the moment.

@TavQ486 29 күн бұрын

why does my Excel dont have GroubBY Function?

@AlexB-wd6hz 18 күн бұрын

Only in Beta now(((

@mandygriffin5241 24 күн бұрын

Pivot tables are so much easier than groupby. Don’t think groupby obeys slicers, for instance. A refresh macro isn’t difficult to write.

@Jguthro Ай бұрын

Is that being read as python. I know you can use python in excel. I havnt done it thought. I write code against pd dataframes.

@kimyenchu 29 күн бұрын

Microsoft need to make the table columns lockable if you put a formula in the column. Right now you can't lock individual columns, it's either all or nothing.

@alexcolombu1025 29 күн бұрын

I would rather just use the pivot table. Nice video though.

@KenjiExplains 29 күн бұрын

Fair enough!

@JamesTavon-dz8cq 7 күн бұрын

Whoops. Cannot find my original comment to delete, I was referring to another video.

@len1ams 26 күн бұрын

Add a button to refresh the pivot table

@tononoinks5231 28 күн бұрын

Now do this in company that uses 10 year old excel program.

@DerekElliott-qn2kz 25 күн бұрын

This is only available to Beta users

@bitokay147 23 күн бұрын

Why not put this in a sharepoint list so you don’t have to refresh and just create filter views.

@leeching1 29 күн бұрын

isn't that pivot table supposed to be user-friendly for people who doesn't know how to use excel and are unfamiliar with formulas. I think Groupby is good for people who are experts in Excel formulas.

@apamwamba 23 күн бұрын

Not really GOODBYE.. Just learn the CORE SKIILS: DATA MODELLING and TRANSFORMATION , SQL , DATABASES , DAX [or something similar]..The all will be good