YoVDO

Advanced Excel 365 Training: Dynamic Arrays, Complex Lookups, and Power Tools

Offered By: Simon Sez IT via YouTube

Tags

Microsoft Excel Courses Power Query Courses Power Pivot Courses Lambda Functions Courses XLOOKUP Courses

Course Description

Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!
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.

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

Related Courses

Практики работы с данными средствами Power Query и Power Pivot
Saint Petersburg State University via Coursera
Практики анализа экономических данных от простого к сложному
Saint Petersburg State University via Coursera
Das ultimative Excel Kurs Komplettpaket: Jetzt durchstarten!
Udemy
Excel Business Intelligence: Power Pivot and DAX
LinkedIn Learning
Excel: Creating a Dashboard with Power Query (Office 365/Microsoft 365)
LinkedIn Learning