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
Tag: Productivity
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
Excel Magic Trick 1515: Yearly Income Statement Using SUMIFS Function, and NO Merge & Center
Download Excel START File: https://ift.tt/2Pul1RO
Download Excel FINISHED File: https://ift.tt/2y8TA9D
Entire page with all Excel Files for All Videos: https://ift.tt/1kSFWvs
In this video see how summarize the monthly Net Income Statement numbers into a Yearly Net Income Statement using the SUMIFS function. In the process, we will have to fix the Net Income Template and remove vthe Merge and Center feature and replace it with Center Across Selection.
Power Pivot Introduction #1: Relationships rather than VLOOKUP for PivotTable Report MSPTDA 13
Download Excel START File: https://ift.tt/2pB6fxA
Download Excel FINISHED File: https://ift.tt/2ynEf4C
Download Example of Large File Size using VLOOKUP: https://ift.tt/2pFVFp8
Download pdf Notes about Power Query: https://ift.tt/2ykLQ3J
This video introduces Excel Power Pivot and shows how to use the Relationship feature rather than VLOOKUP Function to reduce file size and allow user to pull fields for a PivotTable Report from Multiple Tables.
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
2. (01:43) Look at VLOOKUP solution
3. (02:49) Introduction to What Is Power Pivot? Discuss what a Data Model is.
4. (05:46) Relationship feature
5. (09:16) Create PivotTable Report
6. (11:45) First Look at Implicit Measures
7. (13:02) Enable the Power Pivot Ribbon Tab
8. (13:37) First Look at Power Pivot Window and Data Model
9. (20:30) Summary