Dive into an advanced Microsoft Excel 365 tutorial spanning over 3 hours to elevate your data analysis and productivity skills. Master dynamic array formulas like SORT, FILTER, and XLOOKUP for efficient data manipulation. Explore complex lookups, reference functions, and learn to create custom functions using LET and LAMBDA. Gain proficiency in Power Pivot and Power Query for seamless data management and analysis. Follow along with downloadable course files and navigate easily through various topics using provided timestamps. Enhance your Excel expertise with hands-on exercises and comprehensive coverage of advanced features, preparing you to tackle complex data challenges in your professional endeavors.
Advanced Excel 365 Training: Dynamic Arrays, Complex Lookups, and Power Tools
Simon Sez IT via YouTube
Overview
Syllabus
- Course Introduction
- Exercise 01
- What are Dynamic Array Functions?
- Introduction to Spills and Arrays
- Extract and Count Unique Entries
- Unique vs Distinct
- Extract Multiple Unique Columns
- Extract Unique Values by Column
- The SORT Function
- The SORT Function - Horizontal Sort
- The SORTBY Function
- The SORTBY Function - Horizontal SORTBY
- The SEQUENCE Function
- The FILTER Function
- FILTER with Logic: + Operator OR
- FILTER with Logic: * Operator AND
- FILTER with Logic: = Operator Both or Neither Criteria
- FILTER with Logic: - Operator One or the Other but Not Both
- RANDARRAY and RANDBETWEEN
- Use XLOOKUP to Perform Complex Lookups
- The XMATCH Function
- Exercise 02
- Lookups with Multiple Criteria
- Lookups with Duplicate Values
- Exercise 03
- The LET Function
- Create Your Own Formulas with LAMBDA
- Exercise 04
- Create a Data Model in Power Pivot
- Create Relationships Between Tables
- Add Calculated Columns
- Build a PivotTable from Multiple Sources
- Exercise 05
- Import Data Using Power Query
- Clean Data with Power Query
- Combine Files from a Folder
- Add More Files to the Data Model and Refresh
- Exercise 06
Taught by
Simon Sez IT
