Download Excel File: https://excelisfun.net/files/EMT1651.xlsx
Learn how to create a Yearly Sales Report with Excel’s Dynamic Spilled Array Formulas. Learn about the new Dynamic Array Functions: UNIQUE and SORT, and see how the old standby Excel Functions YEAR and SUMIFS to build a Dynamic Report that updates when new data is added.
1. (00:01) Introduction
2. (00:27) Extract Unique List of Years from Data Column with the Excel Functions: YEAR, UNIQUE & SORT.
3. (01:20) Formula to Calculate Yearly Sales using the Excel Functions SUMIFS and DATE. Learn about Comparative Operators, the Join Operator and the # Symbol to refer to a Spilled Array. Learn about how to use a lower and upper limit in the SUMIFS function to add only sales for a given year.
4. (02:40) Add Conditional Formatting to Spilled Arrays. Learn how to use a Logical Formula and the ISNUMBER Excel Function.
5. (03:34) Add Number Formatting to Spilled Arrays.
6. (03:40) Add new data and test formulas.
7. (03:52) Summary and End Video Links