PIVOTBY & GROUPBY to Create Year by Month Sales Report. Grouping Trick! Excel Magic Trick 1859
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1857-...
Learn how to group in the GROUPBY and PIVOTBY functions to create Year by Month Sales Reports.
Topics:
1. (00:00) Introduction
2. (00:21) Shout Out to Excel Instructor: Radosław Poprawski at / @excelinstructor
3. (00:33) EOMONTH column, YEAR column inside HSTACK
4. (01:31) Custom Number Format for month
5. (01:42) GROUPBY
6. (02:17)PIVOTBY
7. (02:39) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp
Thank you amazing Mike for this EXCELlent video.
You are welcome, Fellow Teacher : ) : )
Excellent Mike !!! You resolved my problem Thanks so much!!
You are welcome!!!!
Mike Thank you so, so much for the shout out!,
once i deal with new job and moveing ill start making more videos!
Cool!!!!!
Fantastic Mike!!!! Come on Microsoft, push it out to all 365 subscribers!
Come on. MS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
@@excelisfun Yes ...MS, it's time ... time is up !!!
Mike eres un monstruo, gracias hermano.
: ) : ) : )
Thanks Mike!! :) :)
Thanks, First Member Ever!!!!
I hope that others will follow by large numbers.
We followers have been benefiting from your videos for mamy years. Now its about time for all of us to show support. :)
@@johnborg5419 Thanks you very much : ) : )
Great video. I haven't played about with it too much as can't access on my work computer yet. I use a lot of these types of tables so am looking forward to it.
Yes!!!! : ) : )
Mike: Any idea when these new functions will be released to us common folk??
MS has not said. They are being bad because this is one of the longest waits we have ever had : (
until When these functions available for not beta users
MS has not said. They are being bad because this is one of the longest waits we have ever had : (
I am still very confused about the HSACK and VSTACK function, I just try anyone and see which one gives me my desired result.
I think stacking stuff side by side (column) should be VSTACK....
I think of V as Vertical and H as Horizontal!!!
I have a question and I am far from an expert in excel. So I am trying to figure out what is the best function to use to look up some data. I am with an association and we deal with a # of individual businesses. I have a list of employees who work with those businesses, and I am looking to create a sheet for them to check in at our quarterly meetings and then have the function just automatically look up what company they are with so to help eliminate some typing and time during our check-in process. I currently have an Excel sheet that has a Company name and then an ender that has the employees listed, and this continues down the sheet. but it does not have the company name by each individual's name. Would it be best just to copy and paste all that data, or is there a way to find their name and have it return the company name? Thanks
If you have a look up table with names and company names, then use XLOOKUP. But if you don't have a proper lookup table with all the data, I am not sure how.
This is just could used in 365 ?
Yes, only 365.
I stil don't quite get what the Groupby function does that you can't also do with Pivotby? Why the need for the two functions?
GROUPBY only does row conditions. PIVOTBY can do row and column conditions to create cross tabulated tables.
@@excelisfun ok; but if Pivotby can do both, and I assume you can omit the column condition? Does it not do everything Groupby does and more? In which case, why 2 functions and not just the 1; i.e. Pivotby? Groupby seems somewhat redundant...
@@Bradrackas My guess is that the term groupby is perhaps the most well know term in all of data analysis and has been used for well over 50 years. So they just wanted it in there so people who felt comfortable with it, use it. I use Groupby almost all the times and I appreciate not having to skip column. I use Pivotby when I do cross tables, which is much less often.
@@BradrackasGood question.
I somehow just found this video. Didn't get notified! This is a common grouping situation but this while IS good solution you can also do in within GROUPBY by wrapping the date column reference with the YEAR funtion to group by year reference or with the TEXT functiin witj a format argument like "mm-yyyy" to group by month and year and avoid either helpers or EOM.
Great Video Mike 📹 👍. Can we use the floor function to group the years say 200-2004 then 2005-2009 etc.
I bet we can! What a great idea, Nader!!!