This 51-minute tutorial explores the powerful PIVOTBY and GROUPBY functions in MS 365 Excel that can accomplish tasks PivotTables cannot. Discover 10 comprehensive examples demonstrating dynamic spilled array formulas, instant data updates without cache delays, and advanced reporting techniques. Learn to create frequency distributions with charts, implement conditional formatting for spilled reports, use LAMBDA functions within GROUPBY, calculate percentages of totals, and build cross-tabulated reports. Master techniques for adding custom headers with VSTACK, combining calculations with HSTACK, creating payroll reports without helper columns, and even handling large datasets of 990,000 rows efficiently. Download the accompanying Excel file, PDF notes, and access additional resources to enhance your Excel data analysis capabilities. Available with multilingual subtitles and audio tracks.

Syllabus

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