MS 365 Excel Basics 09: PIVOTBY & GROUPBY Functions Beat PivotTable: 10 Mind-Blowing Examples!

Video Description

Download Excel File: https://excelisfun.net/files/ExcelBasics09.xlsx Full free YouTube class: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo Read (download right-click): pdf notes: https://people.highline.edu/mgirvin/AllClasses/218M365/Content/ExcelBasics09.pdf The Only App That Matters book by Mike Girvib at Amazon: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ccs_id=70eb6de0-afb4-4979-a9a7-a1bdc0089ad3 Link to LAMBDA video: https://www.youtube.com/watch?v=OxV-F0vXj8I&list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW&index=13 In this video learn about PIVOTBY & GROUPBY Functions: Topics: 1. (00:00) Introduction 2. (00:46) Topics in video 3. (01:48) Full list of Array Functions seen in this video 4. (02:10) Fundamental of Dynamic Spilled Array Formulas. Learn about the SEQUENCE array function. 5. (06:17) Conditional Formatting for Dynamic Spilled Array Formulas 6. (07:40) GROUPBY & PIVOTBY arguments 7. ( 08:07) History of Single Cell Reporting Formulas 8. (10:07) When to use a PivotTable and when to use GROUPBY & PIVOTBY functions. 9. (14:19) Example of Instant Update for GROUPBY & PIVOTBY functions and compare it to the PivotTable Cache. 10. (16:28) Example 1: GROUPBY & Excel Chart to create frequency distribution that updates instantly when source data changes. 11. (20:29) Description of field_relationship argument in the GROUPBY Function. 12. (22:09) Add conditional formatting to spilled report. 13. (23:02) Connect Excel Chart to Dynamic Spilled Array Formula 14. (23:37) Know Bug in GROUPBY & Excel Chart When you insert a column into the work sheet. At this minute mark in this video, I have inserted the 2-minute video I made to ask the YouTube audience for help. I also inserted a 30 second video with the replies about this known bug. 15. (26:44) Example 2: Use Eta-LAMBDA ARRAYTOTEXT inside the function argument in the GROUPBY reporting function. 16. (27:58) Example 3: This problem uses a boomerang company Baltic birch aircraft plywood product cost table of data. Use Eta-LAMBDA MEDIAN inside the function argument in the GROUPBY reporting function. Use the field_relationship in the GROUPBY function to sort the second column as a table, rather than in a way that respects the hierarchical relationship between the two columns. 17. (30:40) Example 4: Use XLOOKUP to calculate sales in the values argument of the GROUPBY function. 18. (32:33) Use the VSTACK function to add custom report headers to the GROUPBY generated report. 19. (33:20) Example 5: Use HSTACK function in the function argument of GROUPBY to have two columns in the report with two different calculations. 20. (34:21) How to use the DROP array function. 21. (35:33) Example 6: Create a payroll report that avoids a helper column and instead makes the hourly calculation directly in the values argument of the GROUPBY function. 22. (37:17) Example 7: PIVOTBY function to calculate the % of Grand Total and % of Parent Row Total. Learn about the related_to argument in the PIVOTBY function. Connect the PIVOTBY function to a formula input from a cell in the worksheet so that the user can switch between reports easily. 23. (38:32) compare the arguments in GROUPBY and PIVOTBY. 24. (42:28) Example 8: PIVOTBY function to create a cross-tabulated report. 25. (43:13) Example 8 (second part): Create a three-in-one report that allows a user to select the type of report from a cell and then the chart updates to reflect that new chart. The three reports are: % of Grand Total, % of Row Totals, % of Column Totals. 26. (44:57) Example 9: Use GROUPBY to help create an X-Y Scatter chart. PivotTables cannot be the source data for an X-Y Scatter Chart. 27. (45:17) Example 10: Test GROUPBY and PIVOTBY array functions on 990,000 rows of data. Then perform well. 28. (45:47) Conditional Formatting with an AND Logical Test to apply dynamic formatting to a Dynamic Spilled Array Formula Report. 29. (49:49) HW Practice Problems for you!!! 30. (49:54) Summary 31. (51:07) Closing Using the gear icon setting button below the video, you can watch subtitles in these languages: Afrikaans, Arabic, Bengali (some videos), Bangla, Dutch, Filipino, French, Hindi, Indonesian, Khmer, Malay, Malayalam, Nepali, Persian, Polish, Spanish, Swahili, Tamil, Telugu, Thai, Tibetan, Urdu, Vietnamese. Using the gear icon setting button below the video, you can listen to an audio track in these translated languages: French, German, Hindi, Indonesian, Italian, Japanese, Portuguese, and Spanish. The Hunter by Audionautix is licensed under a Creative Commons Attribution 4.0 license. https://creativecommons.org/licenses/by/4.0/ Link to LAMBDA video: https://www.youtube.com/watch?v=OxV-F0vXj8I&list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW&index=13 #excel #groupby #pivotby #dataanalysis #groupby #pivotby #excel #excel365 #excelisfun #highlinecollege #mikegirvin #freeexcellessons #excelformulas #excelfunctions #groupby #pivotby