Basics of Excel Spilled Array Formulas to create Monthly Sales Report. Excel Magic Trick 1702.

Download Excel File: https://excelisfun.net/files/EMT1702.xlsx
Learn how to create an employee month sales report with Microsoft Excel 365 Spilled Array Formulas. Learn the basics of these new spilled array formulas and build a useful report.
Topics:
1. (00:00) Introduction
2. (00:17) Review of three videos in series
3. (00:51) Microsoft 365 Worksheet Calculation Engine
4. (01:04) Create sorted unique list of names from data set.
5. (01:37) Create INDEX function formula to repeat names for report.
6. (03:10) Create dynamic array of row numbers for INDEX.
7. (03:18) Count unique names.
8. (03:40) Count unique months.
9. (04:10) Use Clipboard to save formula elements that we can paste into larger formula.
10. (04:41) SEQUENCE function to create numbers 1 to 12.
11. (05:00) Divide by COUNT and use INT function to get repeating numbers like: 1;1;1;2;2;2…
12. (06:38) Formula to create min date from data set. using DATE, YEAR and MONTH functions.
13. (07:41) How EOMONTH function works.
14. (08:18) Use MOD function to create repeating numbers like: 0;1;2;0;1;2…
15. (09:10) Formula to get array of end dates.
16. (09:54) Formula to get array of end dates.
17. (10:21) How EDATE function works.
18. (10:53) Create SUMIFS function with column of sales totals.
19. (12:29) Add conditional formatting for spilled array formula with Cell not empty.
20. (13:40) Test dynamic report. Add new data.
21. (14:03) Overview and what comes in next video with LET function.
22. (14:37) Summary, Closing and Video Links
Basics of SUMIFS & PivotTables for Monthly Sales Report. Excel Magic Trick 1701
Excel Spilled Array Formulas to create Monthly Sales Report. Excel Magic Trick 1702
LET Function to create Monthly Sales Report in Single Cell with Total Row. Excel Magic Trick 1703

Видео Basics of Excel Spilled Array Formulas to create Monthly Sales Report. Excel Magic Trick 1702. канала ExcelIsFun