Download Excel File: https://ift.tt/2QdaUkX
In this video learn about Spilled Dynamic Arrays & Conditional Formatting.
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)
Tag: How to
Dynamic Spilled Arrays: Show All Formulas For All Columns in Cells as Auditing Trail EMT 1524
Download Excel File: https://ift.tt/2QdaUkX
In this video learn how to take multiple columns filed with formulas and list all the formulas and the cell reference that the formula sits in in a Vertical Column with a single cell formula. See the Functions: ADDRESS, ROW, COLUMN, FOMULATEXT, IFNA and TRANSPOSE.
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)
Excel Dynamic Arrays: Data Validation List Dropdown: Unique, Sorted & No Blanks EMT 1523
Download Excel File: https://ift.tt/2O0QoBY
In this video learn how to create a Data Validation Dropdown List that contains only a Sorted, Unique List with No Blanks based on a column in a Table . See the New Office 365 Dynamic Arrays and the UNIQUE, SORT and FILTER Array Functions, as well as the Spilled Array Notation (Syntax) for the Data Validation Drop Down List Dialog Box.
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)
VLOOKUP: Step Aside! New Epic Lookup Function = FILTER Dynamic Array Function (EMT 1521)
FILTER Function: One or More Lookup Values Return One or More Returns Values EMT 1521
Download Excel File: https://ift.tt/2O0QoBY
In this video learn about THE Most Amazing Lookup function for dealing with Multiple Lookup Values or Multiple Returned values when doing lookup! Learn about the FILTER Function to filter data sets, columns, or to do complicated Lookups.
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)
Excel Dynamic Arrays: Fully Dynamic Cross Tabulated Reports With Totals!!!!! EMT 1528
Download Excel File: https://ift.tt/2SoQo2c
This video is a follow up to EMT 1520, and thanks to David Milbrandt, we now can have a fully Dynamic Cross Tabulates Report driven by formula inputs, and have totals at the Right and Bottom. This video will blow your mid with what the New Office 365 Spilled Array Formulas can do!!!!! See the functions: SUMIFS, UNIQUE, SEQUENCE, SORT, INDEX, MATCH, TRANSPOSE, COUNTA and IF. Learn how to Append Columns or Rows to an existing Array.
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)
Dynamic Array Discussions in excelisfun Community Tab: Charts?, Append Totals? Big Data?
This video poses a survey and some questions for discussion about Excel’s New Dynamic Arrays at the excelisfun Community Tab Link to excelisfun Community Tab: https://www.youtube.com/user/ExcelIsFun/community
Link to Excel Workbook: Download Excel File: https://ift.tt/2END9pd
Excel Dynamic Array UNIQUE with INDIRECT To Extract Unique Lists for Each Column (EMT 1522)
Download Excel File: https://ift.tt/2O0QoBY
In this video learn how to use The New Array Functions SORT & UNIQUE with the INDIRECT Function to extract a unique set of values for each column in a table.
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)
Excel Dynamic Arrays: Fully Dynamic Cross Tabulated Reports? Unbelievable! EMT 1520
Download Excel File: https://ift.tt/2O0QoBY
In this video learn about how to create a Fully Dynamic Cross Tabulated Report that can update when formula inputs are changed for Row Header Conditions or Column Headers Conditions and will instantly update when new data is added to the source data. This is all done with Formulas, NOT PivotTables, so everything updates instantly when source data or formula inputs change. See the New Array Functions SORT and UNIQUE, and also the older Array Function TRANSPOSE, but most fundamentally, we will see that the INDEX and MATCH Function can lookup columns of data based on formula inputs, and will be the secret ingredient that we use inside the SUMIFS function and also inside the UNIQUE Function.
Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516) , https://www.youtube.com/watch?v=7jJMDGZpjSk
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
Excel Dynamic Arrays or Mixed Cell References for Cross Tabulated Reports? EMT 1518
Download Excel File: https://ift.tt/2O0QoBY
In this video we embark into the New World of Spilled Arrays. Which will require us to re-think how we use Excel. In this video learn about The New Office 365 Dynamic Array Formulas and the Excel Calculation engine which will allow us to create a Budgeted Income Statement either with using Standard Formulas and Mixed Cell References or with a Dynamic Spilled Array Formula.
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516) https://www.youtube.com/watch?v=7jJMDGZpjSk
Excel Dynamic Arrays: Backward Compatibility? Forward Compatibility? What Happens? EMT 1519
Download Excel File: https://ift.tt/2O0QoBY
In this video learn about how Array Formulas create in the New Office 365 with the Excel Calculation engine will respond when we open the file in an Older Version. We also see what happens when we open on older file filled with Array Formula reacts when we open it in the new Version of Excel.
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)
Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516)
Download Excel START File: https://ift.tt/2ysCu6P
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
This video is a comprehensive video about the new Office 365 Dynamic Array Formulas, Array Functions and Excel’s Calculation engine.
Topics:
1. (00:06) Introduction to the new Excel Calculation Engine and Array Formulas in Excel
2. (05:53) OR Logical Test AVERAGE Array Formula. Delivers a single Answer. No Ctrl + Shift + Enter
3. (07:22) How Old Single Cell Array Formula Behaved
4. (08:20) How New Calc Engine Avoids Trouble with Array Formulas
5. (09:08) FREQUENCY Function
6. (09:32) How Old FREQUENCY Function Worked
7. (10:00) FREQUENCY Function. New Calc Engine. Spill Automatically
8. (10:32) Where Spilled Array Formula Lives. Refer to Spilled Array with F40#
9. (12:45) Spill Error
10. (13:20) Standard Deviation. Delivers a single Answer. No Ctrl + Shift + Enter
11. (13:45) OR Logical Test Adding formula with SUMIFS & SUM Function, rather than SUMPRODUCT
12. (15:29) SUMIFS and Function Argument Array Operation will Spill
13. (17:00) Array Formula to create a Formula Report that is EAISER than using a PivotTable
14. (17:51) Unique List using UNIQUE Function
15. (18:18) Distinct List using UNIQUE
16. (20:18) Unique List and FILTER to avoid zeroes
17. (21:22) Unique Count Formula using COUNTA, UNIQUE and OR
18. (22:37) FILTER Function as Lookup with One Lookup Value & Return Multiple Items in Single Column
19. (24:00) FILTER Function as Lookup with One Lookup Value & Return Multiple Items in Multiple Columns
20. (24:22) FILTER Function as Lookup with Multiple Lookup Values & Return Multiple Items (Boolean Logical Test)
21. (24:44) #CALC! Error and the third argument in FILTER
22. (26:08) SORT Function to sort a filtered list
23. (27:50) Extract Sorted & Unique List from Mixed Data using SORT and UNIQUE
24. (28:38) Extract Unique & Sorted List of Mixed Data in Single Cell using TEXTJOIN, SORT and UNIQUE
25. (29:43) Spill Direct for Dynamic Arrays using Array Syntax
26. (30:50) Variable Length Spilled Arrays using LARGE & SEQUENCE Function
27. (32:00) SEQUENCY Function for incrementing Numbers in cells
28. (32:10) SEQUENCY Function for incrementing Stepped Numbers in cells
29. (32:28) MID, LEN and SEQUENCE to extract characters from a cell in a Row
30. (33:26) MID, LEN, TRANSPOSE and SEQUENCE to extract characters from a cell in a Column
31. (33:40) Dynamic PivotTable using SUMIFS and a number of new Array Functions
32. (35:15) SINGLE Function and Implicit Intersection
33. (37:45) Some Functions still will NOT make Array Calculations, like SUMIFS, COUNTIFS and AVERGAEIFS
34. (38:14) RANDARRAY Function
35. (39:01)
36. Summary
Add 2nd Set of Points to Excel X – Y Scatter Chart (Excel Magic Trick 1517)
Download Excel File: https://ift.tt/2yOfeQ7
In this video learn how to add a second set of Data Points to an X – Y Scatter Plot Chart.
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs