Completed
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.
Class Central Classrooms beta
YouTube videos curated by Class Central.
Classroom Contents
PIVOTBY & GROUPBY Functions: 10 Mind-Blowing Examples - MS 365 Excel Basics #9
Automatically move to the next video in the Classroom when playback concludes
- 1 1. 00:00 Introduction
- 2 2. 00:46 Topics in video
- 3 3. 01:48 Full list of Array Functions seen in this video
- 4 4. 02:10 Fundamental of Dynamic Spilled Array Formulas. Learn about the SEQUENCE array function.
- 5 5. 06:17 Conditional Formatting for Dynamic Spilled Array Formulas
- 6 6. 07:40 GROUPBY & PIVOTBY arguments
- 7 7. 08:07 History of Single Cell Reporting Formulas
- 8 8. 10:07 When to use a PivotTable and when to use GROUPBY & PIVOTBY functions.
- 9 9. 14:19 Example of Instant Update for GROUPBY & PIVOTBY functions and compare it to the PivotTable Cache.
- 10 10. 16:28 Example 1: GROUPBY & Excel Chart to create frequency distribution that updates instantly when source data changes.
- 11 11. 20:29 Description of field_relationship argument in the GROUPBY Function.
- 12 12. 22:09 Add conditional formatting to spilled report.
- 13 13. 23:02 Connect Excel Chart to Dynamic Spilled Array Formula
- 14 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 …
- 15 15. 26:44 Example 2: Use Eta-LAMBDA ARRAYTOTEXT inside the function argument in the GROUPBY reporting function.
- 16 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 functi…
- 17 17. 30:40 Example 4: Use XLOOKUP to calculate sales in the values argument of the GROUPBY function.
- 18 18. 32:33 Use the VSTACK function to add custom report headers to the GROUPBY generated report.
- 19 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 20. 34:21 How to use the DROP array function.
- 21 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 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 formu…
- 23 23. 38:32 compare the arguments in GROUPBY and PIVOTBY.
- 24 24. 42:28 Example 8: PIVOTBY function to create a cross-tabulated report.
- 25 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: …
- 26 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 27. 45:17 Example 10: Test GROUPBY and PIVOTBY array functions on 990,000 rows of data. Then perform well.
- 28 28. 45:47 Conditional Formatting with an AND Logical Test to apply dynamic formatting to a Dynamic Spilled Array Formula Report.
- 29 29. 49:49 HW Practice Problems for you!!!
- 30 30. 49:54 Summary
- 31 31. 51:07 Closing
