Elevate your Microsoft Excel 2021/365 skills to an advanced level with this comprehensive 3.5-hour tutorial. Master dynamic array functions, spill handling, and advanced formulas like XLOOKUP and XMATCH. Learn to differentiate between Unique and Distinct, utilize various sort functions, and implement advanced filtering techniques. Explore creating flexible lookup formulas, employing statistical functions for data ranking, and calculating MEDIAN and MODE. Practice with downloadable exercise files to reinforce your learning. Navigate through topics such as array manipulation, advanced sorting and filtering, lookup functions, data analysis, and specialized rounding techniques. Perfect for those seeking to enhance their Excel proficiency and streamline complex data management tasks.
Overview
Syllabus
- Introduction
- Exercise 01
- Dynamic Arrays
- Spill and Arrays
- Extract and Count Unique Entries
- Unique vs. Distinct
- Unique with Multiple Criteria
- Unique Values by Column
- SORT Function
- Horizontal Sort
- SORTBY Function
- Horizontal SORTBY
- Sequence and Unstacking Records
- Filter Function
- Using Filter with Logic Plus Operator
- Using Filter with Logic Asterisk Operator
- Using Filter with Logic Equals Operator
- Using Filter with Logic Minus Operator
- RANDARRAY and RANDBETWEEN
- XLookUp
- XMatch
- Exercise 02
- Two-way Lookups
- CHOOSE Function
- SWITCH Function
- Exercise 03
- Median and Mode
- Using LARGE and SMALL
- Ranking Data
- COUNTBLANK Function
- Exercise 04
- Rounding Values
- Specialized Rounding
- Aggregate Function
- Exercise 05
Taught by
Simon Sez IT
Related Courses
-
Advanced Excel 365 Training: Dynamic Arrays, Complex Lookups, and Power Tools
-
Excel - Formulas & Functions Beginner to Expert Course
-
Excel Formulas & Functions Part 4: Statistical Functions
-
Microsoft Excel Masterclass (2016/2019) - Basic to Advanced
-
Microsoft Excel - Excel Course For Beginners
-
Microsoft Excel: Excel - Beginner to Advanced (Full Excel)
