Try This New Formula Instead of Pivot Tables
HTML-код
- Опубликовано: 25 июн 2024
- 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
🚀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.
correct, and the ability of create measure when data store in DATA MODEL
Nobody does it better, Kenji. Thank you for the instruction.
I enjoy watching your videos. Your clarity and the fact that you add a sample document to follow along.
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.
I like your content a lot. Please keep up the good work going. Thank you 😊
Thank you for the comment! It means a lot :)
Why so many pivottable haters, when they are so easy.
If you’re dealing with data model you should learn some dax in order to get your data consistent and ready for exploration.
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 ?
Seen this one before but now know the logic of it ...Thanks K ...keep posting dost 😊
That's awesome. Thanks for letting us know. Really useful
Love this! Going to redesign one of my dashboards today!
Thank you for your valuable contribution, it's an excellent tutorial. Thank you! 😊
Thanks for the videos. I like how you explain to understand.
Thank you for your comment :)
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 🙌
Great formula!! Thanks
Thanks. Way simpler than pivot table.
Wow, this is gold! Thanks!
Thanks for sharing. Awesome!
You can just click in the pivot table and press refresh. Do not need to go to the ribbon.
Really this is Amazing, Keep going.
Cool. Thanks for detail video.
I can retire Pivot table in my simple excel.
Good teacher👍
Very nice information and teaching sir.. from India North East
Cool new formula 🙏🏻
Good going kenji
Thank you!
thank you so much for everything 🌹
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! 👍🏻
Thanks brother
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 ?
Thanks Sir 👍🏻👍🏻
Thanks!
Wow this looks nice compare to pivot! Thanks to this new formula ❤
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??
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
Hi Thanks a lot for this information, also please can shear the practice file
Very educative video
Sir can you please share which software do you use for video editing
Please Share
We can use unique and sumif functions to do the same.
Question is how can it be controlled when I'm using a slicer
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?
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
Nice!
Amazing
Can i use this formula in 2019 excsl version
How will it impact in terms of performance if we use formulae instead of pivot tables?
Why is this function not available on latest version of excel..
Newly released functions that are only available to insiders. I just double-checked Google Sheets, and they are not available there either.
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.
@@jtmh31to his credit (somewhat) he does at least mention it about 90 seconds in. But I agree with your sentiment.
Can you make a video on Knime?
1st view I like your videos and more informative
Thanks so much!
Can you tell me which version available in groupby function...
What if you had annual data and needed to add a date filter to get monthly?
in which version onwards this formula is available?
the best
How about for the repeated items? What if we dont want to see repeated items
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.
So many formula to remember only to replace a simple pivot
cool formula thanks
Awesome!
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 👍🏻👍🏻
I use VBA to update pivot tables automatically when source data has changed.
Sir here slicer are not working with groupby and pivot by
How to do the group by date/daily?
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.
Osu!!! master! Bow!
Is this a new update or add in? I just tried it and I dont have it.
I m not getting Groupby functions in my Excel 365
From where we can get groupby function. In my excel sheet, it is not appearing.
Hello. I'm using office 365, I didn't see groupby Function so what's the problem?
Help, i don't have this kind of function in my Excel
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
So if these formulas do everything that a pivot table does... What additional thing do we get other than self updating?
You get another set of data that would can manipulate
Do these functions work only when the base data is structured in TABLE format?
No they also work without the table format :)
I can't find this groupby function on my excell
why groupby is not showing in my excel?
But when i need slisers?
Is the function available on Microsoft 365.. using Microsoft 365 but not able to get these functions
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.
*groupby function is not showing in ms office 2024 and not in google sheet? how can i get this function?
Pls. Respond.*
isn't pivot table more easy and convinence?
"groupby" is still not available in my excel
❤
It is not available
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.
Hey thanks I said that towards the start of the video
@@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
how can i don't have that formula in my excel (=GROUPBY)
Why is using the STACK in Group by formula
Discount on all courses?
Hi thanks for your interest, contact info@careerprinciples.com for course related questions! :)
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🤗
I think using of new technic needs to more experience.but Pivotable is simple.
Just refresh and see
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
GROUPBY IS NOT AVAILABLE IN ALL VERSIONS. I HAVE 365 APPS (2406) AND IT DOES NOT AVAILABLE.
=GROUPBY function is not available in my Office 365. I'm from Bangladesh
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
Its only in Beta at the moment.
why does my Excel dont have GroubBY Function?
Only in Beta now(((
Pivot tables are so much easier than groupby. Don’t think groupby obeys slicers, for instance. A refresh macro isn’t difficult to write.
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.
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.
I would rather just use the pivot table. Nice video though.
Fair enough!
Whoops. Cannot find my original comment to delete, I was referring to another video.
Add a button to refresh the pivot table
Now do this in company that uses 10 year old excel program.
This is only available to Beta users
Why not put this in a sharepoint list so you don’t have to refresh and just create filter views.
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.