Explore the powerful GROUPBY Excel function in this comprehensive 29-minute tutorial. Master creating single-cell formula reports that update instantly when source data changes. Dive into topics such as comparing GROUPBY and PIVOTBY arguments, understanding the 7 GROUPBY arguments, and utilizing 16 new Lambda replacement functions. Learn to create percentage calculations, add custom header labels, implement subtotals, and apply conditional formatting for dynamic reports. Discover techniques for filtering reports, using LAMBDA within GROUPBY, and creating fully dynamic reports with formula inputs from worksheet cells. Enhance your Excel skills and streamline your data analysis process with practical examples and expert guidance.
Overview
Syllabus
1. Introduction to GROUPBY, PIVOTBY and the new Lambda Replacement Functions
2. Compare arguments in GROUPBY and PIVOTBY functions.
3. GROUPBY and the 7 arguments.
4. 16 New Lambda Replacement Functions.
5. New Lambda Replacement Functions as replacement for LAMBDA.
6. PERCENTAGE of in GROUPBY Function.
7. Why use formulas rather than PivotTables.
8. Two Fields in Row Area of Report.
9. Problem: No Label for Calculation Column. Look at field_header argument.
10. Two Columns of calculations in report.
11. Add Custom Header Labels to Report with VSTCK Function and Array Constant.
12. Subtotals
13. Conditional Formatting for dynamic Report.
14. F5 Go To Trick to find Conditional Formatting in Report.
15. How to create multiple columns with different calculations with GROUPBY, LET, DROP and TAKE Functions.
16. Filter GROUPBY Report with Contains Criteria.
17. Filter GROUPBY Report with Criteria from a list.
18. Using LAMBDA in the GROUPBY Function: two examples.
19. Creating Array Calculation in values argument of GROUPBY Function.
20. Create Fully Dynamic GROUPBY Report with Formulas Inputs from Worksheet Cells.
21. Summary
22. Closing, Video Links
Taught by
ExcelIsFun
