Completed
10. 16:28 Example 1: GROUPBY & Excel Chart to create frequency distribution that updates instantly when source data changes.
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
