Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Microsoft Excel 2021 Advanced Training
Section 1: Introduction
Course Introduction (2:46)
Exercise 01 (1:47)
DOWNLOAD ME: Exercise Files
DOWNLOAD ME: Course Files
Section 2: Dynamic Arrays
What are Dynamic Arrays? (2:14)
Introduction to Spills and Arrays (10:49)
Extract and Count Unique Entries (6:14)
Unique vs Distinct (3:23)
Extract Unique Values with Multiple Criteria (3:11)
Extract Unique Values by Column (4:23)
The SORT Function (8:53)
The SORT Function - Horizontal SORT (6:06)
The SORTBY Function (6:10)
The SORTBY Function - Horizontal SORT (3:05)
Simple SEQUENCE and Unstacking Records (10:10)
The FILTER Function (6:51)
FILTER with Logic: + Operator (OR) (4:48)
FILTER with Logic: * Operator (AND) (5:39)
FILTER with Logic: = Operator (Both or Neither) (4:11)
FILTER with Logic: - Operator (One or the Other) (4:49)
Using RANDARRAY and RANDBETWEEN to Randomize Data (9:44)
Using XLOOKUP to Perform Complex Lookups (12:22)
Using XMATCH (6:20)
Exercise 02 (13:09)
Section 3: Advanced Lookup and Reference Functions
Performing 2-Way Lookups (8:25)
Using the CHOOSE Function (10:02)
Using the SWITCH Function (4:31)
Exercise 03 (7:05)
Section 4: Statistical Functions
Using MEDIAN, MODE.SNGL and MODE.MULT (6:16)
Using LARGE and SMALL (7:27)
Ranking Data with Sorting using RANK.EQ and RANK.AVG (7:41)
The COUNTBLANK Function (4:45)
Exercise 04 (7:05)
Section 5: Math Functions
Rounding Values using ROUND, ROUNDUP and ROUNDDOWN (6:59)
Specialized Rounding (MROUND, CEILING.MATH and FLOOR.MATH) (4:30)
Using the AGGREGATE Function (4:32)
Exercise 05 (5:52)
Section 6: New Functions - LET and LAMBDA
The LET Function (15:07)
The LAMBDA Function (14:13)
Exercise 06 (11:49)
Section 7: Advanced PivotTables and Pivot Charts
Creating a Custom PivotTable Style (9:47)
Applying Custom Number Formatting (10:01)
Sorting Using Custom Lists (4:38)
Applying Multiple Filters to Data (5:06)
Adjusting Slicer Settings (12:02)
Locking Workbooks but not Slicers (3:18)
Creating a Calculated Field (8:01)
Creating a Calculated Item (6:30)
Solve Order (8:11)
GETPIVOTDATA (11:57)
Using GETPIVOTDATA - Dependent Data Validation Lists (8:37)
Creating a Dynamic Pivot Chart Title (10:41)
Adding a Dynamic Series to a Pivot Chart (12:47)
Adding Totals to a Stacked Column Chart (5:52)
Exercise 07 (10:10)
Section 8: Form Controls
Introduction to Form Controls (4:14)
Combo Box - Select from a List (7:21)
Check Box - Check/Uncheck Option (6:48)
Option Button - Choose an Option (6:30)
Spin Button - Move Up and Down a List (5:54)
List Box - Select from a List of Items (4:59)
Scroll Bar - Scroll Through a List (5:01)
Exercise 08 (5:57)
Section 9: Working with Power Query
Power Query Overview (1:35)
Importing Data Using Get and Transform (7:09)
The Power Query Editor (4:45)
Combining Files (4:52)
Transforming Files (9:30)
Loading Files into Excel (4:02)
Exercise 09 (5:54)
Section 10: Forecasting
Getting Forecast Data with FRED (5:34)
Creating a Linear Sales Forecast (8:21)
Creating a Sales Forecast with Seasonality (5:02)
Adding Confidence Levels (6:58)
Using Forecast Sheets (7:16)
Exercise 10 (2:21)
Section 11: Macros and VBA
Using Macros to Automate Tasks (6:40)
Recording, Saving and Running Macros (10:00)
Recording a Macro with Relative Referencing (6:58)
Multi-step Macros (7:09)
The VBA Editor (7:55)
Assigning Macros to Buttons (6:43)
Creating a Custom Macro Ribbon (6:34)
Exercise 11 (5:29)
Section 12: Practical Excel - Advanced Formulas
Finding the Last Occurrence of a Value (7:48)
Finding the Closest Value (6:15)
Extract Data from the Middle of a String (8:53)
Exercise 12 (5:44)
Section 13: Course Close
Course Close (3:23)
Course Quiz and Certificate of Completion
What are Dynamic Arrays?
Complete and Continue