Download files: https://people.highline.edu/mgirvin/AllClasses/348/348/2024/Content/Week06/Video04Files.zip
Free YouTube Data Analysis Class about Microsoft Power Tools in 2024 taught by Excel MVP and Highline College Professor, Mike “excelisfun” Girvin.
In this video learn about when Excel worksheet formulas are the perfect tool for data analysis. An hour lesson in the power of Excel worksheet formulas and when they beat PivotTables, Power Query and Power BI.
Topics:
1. (00:00) Introduction
2. (00:40) 4 scenarios where worksheet formulas are better than other tools.
3. (02:33) Example #1: Enter Data In Worksheet & Need Analysis Off To Side.
4. (03:22) MOD function for hours worked formula
5. (05:19) Line Chart
6. (06:37) 7-Day Moving Average formula AVERAGE, IF, NA and ROWS functions
7. (11:12) TEXT function for day of the week formula
8. (13:00) Dynamic Spilled AVERAGEIFS function to calculate average hours worked per day
9. (14:08) Column chart
10. (15:11) Create Dynamic Spilled Array Single Cell Reporting Formula. Formula to create report that shows Total Hurs by Project Address
11. (16:36) LET function
12. (21:05) Array syntax
13. (22:17) HSTACK and VSTACK functions to join arrays
14. (23:42) Bar chart
15. (25:00) Conditional Formatting for Dynamic Spilled Array formulas
16. (26:53) Test Dynamic Spilled Array formulas with new data
17. (27:25) Example #2: GROUPBY function for single cell reporting. Basics.
18. (31:12) GROUPBY with two row conditions and two values columns
19. (33:23) Create an array syntax with F9 key
20. (34:08) Conditional formatting for GROUPBY with subtotals. Add bold to subtotal row and double-underline for grand total row
21. (36:08) GROUPBY with two functions
22. (37:09) DROP function
23. (37:50) PIVOTBY function
24. (41:02) Example #3: LAMBDA function to create re-usable reporting formula
25. (43:30) Text LAMBDA function
26. (44:28) TAKE function
27. (45:14) Load LAMBDA Function into Defined Name: Name Manager
28. (46:15) Test re-usable function
29. (47:03) Example #4: Build Lambda for Statistical Data Analysis to create a model
30. (51:21) Summary
31. (51:59) Conclusion