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)
Month: October 2018
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
Power Pivot Intro #2: Amazing Columnar Database Importing Millions of Rows Data into Excel MSPTDA 14
Download Excel START File: https://ift.tt/2OjjG41
Download Zipped Folder with Text Files: https://ift.tt/2yAuGiN
Download Excel FINISHED File: https://ift.tt/2Omigpl and https://ift.tt/2yDQGsS
Download pdf Notes about Power Query: https://ift.tt/2Okxdsa
Assigned Homework:
Download Excel file and complete the homework:
https://ift.tt/2yIVa1J
Examples of Finished Homework:
https://ift.tt/2OoIUxZ
This video teaches about the Columnar Database in Excel Power Pivot Data Model, which allows us to import and hold millions of rows of data in an Excel Workbook and have a small file size. We can also make Data Model PivotTable Reports from this imported “Big Data”.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:15) Introduction: Discussion of how to import “Big Data” Into Excel. Discussion about how Excel, Power Query, Power Pivot and the Data Model Columnar Database work together to make importing big data possible in Excel (and in Power BI Desktop).
2. (03:25) Power Query to Import Text Files (Big Data) From Folder and Append them into a single table.
3. (07:50 Columnar Database Explained. How does Columnar Database work? Where is Columnar Database in Excel?
4. (12:55) Make Data Model PivotTable with Implicit Measures.
5. (14:23) Example of dramatic file size reduction when we use the Columnar Database for a single Text File.
6. (16:21) Define Columnar Database. Lookup at pdf notes.
7. (17:37) Why Microsoft names the Big Data tool in Excel “Power Pivot”.
8. (18:21) Summary
MSPTDA 14: Power Pivot Into #2: Amazing Columnar Database Importing Millions of Rows Data into Excel
Download Excel START File: https://ift.tt/2OjjG41
Download Zipped Folder with Text Files: https://ift.tt/2yAuGiN
Download Excel FINISHED File: https://ift.tt/2Omigpl and https://ift.tt/2yDQGsS
Download pdf Notes about Power Query: https://ift.tt/2Okxdsa
Assigned Homework:
Download Excel file and complete the homework:
https://ift.tt/2yIVa1J
Examples of Finished Homework:
https://ift.tt/2OoIUxZ
This video teaches about the Columnar Database in Excel Power Pivot Data Model, which allows us to import and hold millions of rows of data in an Excel Workbook and have a small file size. We can also make Data Model PivotTable Reports from this imported “Big Data”.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:15) Introduction: Discussion of how to import “Big Data” Into Excel. Discussion about how Excel, Power Query, Power Pivot and the Data Model Columnar Database work together to make importing big data possible in Excel (and in Power BI Desktop).
2. (03:25) Power Query to Import Text Files (Big Data) From Folder and Append them into a single table.
3. (07:50 Columnar Database Explained. How does Columnar Database work? Where is Columnar Database in Excel?
4. (12:55) Make Data Model PivotTable with Implicit Measures.
5. (14:23) Example of dramatic file size reduction when we use the Columnar Database for a single Text File.
6. (16:21) Define Columnar Database. Lookup at pdf notes.
7. (17:37) Why Microsoft names the Big Data tool in Excel “Power Pivot”.
8. (18:21) Summary
MSPTDA 14: Power Pivot Into #2: Amazing Columnar Database Importing Millions of Rows Data into Excel
Download Excel START File: https://ift.tt/2OjjG41
Download Zipped Folder with Text Files: https://ift.tt/2yAuGiN
Download Excel FINISHED File: https://ift.tt/2Omigpl and https://ift.tt/2yDQGsS
Download pdf Notes about Power Query: https://ift.tt/2Okxdsa
Assigned Homework:
Download Excel file and complete the homework:
https://ift.tt/2yIVa1J
Examples of Finished Homework:
https://ift.tt/2OoIUxZ
This video teaches about the Columnar Database in Excel Power Pivot Data Model, which allows us to import and hold millions of rows of data in an Excel Workbook and have a small file size. We can also make Data Model PivotTable Reports from this imported “Big Data”.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:15) Introduction: Discussion of how to import “Big Data” Into Excel. Discussion about how Excel, Power Query, Power Pivot and the Data Model Columnar Database work together to make importing big data possible in Excel (and in Power BI Desktop).
2. (03:25) Power Query to Import Text Files (Big Data) From Folder and Append them into a single table.
3. (07:50 Columnar Database Explained. How does Columnar Database work? Where is Columnar Database in Excel?
4. (12:55) Make Data Model PivotTable with Implicit Measures.
5. (14:23) Example of dramatic file size reduction when we use the Columnar Database for a single Text File.
6. (16:21) Define Columnar Database. Lookup at pdf notes.
7. (17:37) Why Microsoft names the Big Data tool in Excel “Power Pivot”.
8. (18:21) Summary