Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Microsoft Excel 2021 Intermediate Training Course
Section 1: Introduction
Course Introduction (6:16)
DOWNLOAD ME: Excel 2021 Intermediate Exercise Files
DOWNLOAD ME: Excel 2021 Intermediate Course Files
Section 2: Designing Better Spreadsheets
The Golden Rules of Spreadsheet Design (11:54)
Improving Readability with Cell Styles (4:58)
Controlling Data Input (8:19)
Adding Navigation Buttons (8:42)
Section 3: Making Decisions with Logical Functions
Logical Functions (AND, OR, IF) (13:18)
The IF Function (5:26)
Nested IFs (8:01)
The IFS Function (6:04)
Conditional IFs (SUMIF, COUNTIF, AVERAGEIF) (6:45)
Multiple Criteria (SUMIFS, COUNTIFS, AVERAGEIFS) (7:12)
Error Handling with IFERROR and IFNA (6:01)
Exercise 01 (7:29)
Section 4: Looking Up Information
Looking Up Information using VLOOKUP (Exact Match) (10:41)
Looking Up Information using VLOOKUP (Approx Match) (4:25)
Looking Up Information Horizontally using HLOOKUP (5:38)
Performing Flexible Lookups with INDEX and MATCH (10:28)
Using XLOOKUP and XMATCH (10:09)
The OFFSET Function (10:50)
The INDIRECT Function (9:07)
Exercise 02 (5:03)
Section 5: Advanced Sorting and Filtering
Performing Sorts on Multiple Columns (7:03)
Sorting Using a Custom List (3:35)
The SORT and SORTBY Functions (10:00)
Using the Advanced Filter (6:52)
Extracting Unique Values - The UNIQUE Function (5:16)
The FILTER Function (9:29)
Exercise 03 (5:41)
Section 6: Working with Date and Time
Understanding How Dates are Stored in Excel (4:11)
Applying Custom Date Formats (7:09)
Using Date and Time Functions (8:44)
Using the WORKDAY and WORKDAY.INT Functions (3:49)
Using the NETWORKDAYS and NETWORKDAYS.INT Function (2:56)
Tabulate Date Differences with the DATEDIF Function (6:14)
Calculate Dates with EDATE and EOMONTH (7:22)
Exercise 04 (4:51)
Section 7: Preparing Data for Analysis
Importing Data into Excel (9:56)
Removing Blank Rows, Cells and Duplicates (5:20)
Changing Case and Removing Spaces (9:03)
Splitting Data using Text to Columns (7:03)
Splitting Data using Text Functions (8:05)
Splitting or Combining Cell Data Using Flashfill (5:00)
Joining Data using CONCAT (7:30)
Formatting Data as a Table (8:33)
Exercise 05 (5:41)
Section 8: PivotTables
PivotTables Explained (2:12)
Creating a PivotTable from Scratch (4:49)
Pivoting the PivotTable Fields (5:31)
Applying Subtotals and Grand Totals (3:14)
Applying Number Formatting to PivotTable Data (3:00)
Show Values As and Summarize Values By (5:50)
Grouping PivotTable Data (5:39)
Formatting Error Values and Empty Cells (5:02)
Choosing a Report Layout (4:35)
Applying PivotTable Styles (4:49)
Exercise 06 (4:05)
Section 9: Pivot Charts
Creating a Pivot Chart (5:04)
Formatting a Pivot Chart - Part 1 (8:36)
Formatting a Pivot Chart - Part 2 (9:14)
Using Map Charts (5:30)
Exercise 07 (4:18)
Section 10: Adding Interaction to PivotTables and Charts
Inserting and Formatting Slicers (7:30)
Inserting Timeline Slicers (5:07)
Connecting Slicers to Pivot Charts (5:01)
Updating PivotTable Data (4:25)
Exercise 08 (6:48)
Section 11: Interactive Dashboards
What is a Dashboard? (5:27)
Assembling a Dashboard - Part 1 (11:25)
Assembling a Dashboard - Part 2 (10:47)
Assembling a Dashboard - Part 3 (8:49)
Exercise 09 (2:27)
Section 12: Formula Auditing
Troubleshooting Common Errors (8:11)
Tracing Precedents and Formula Auditing (7:44)
Exercise 10 (7:30)
Section 13: Data Validation
Creating Dynamic Drop-down Lists (7:27)
Other Types of Data Validation (7:05)
Custom Data Validation (10:10)
Exercise 11 (6:53)
Section 14: WhatIf Analysis Tools
Goal Seek and the PMT Function (5:33)
Using Scenario Manager (7:27)
Data Tables: One Variable (4:29)
Data Tables: Two Variables (4:12)
Exercise 12 (8:32)
Section 15: Course Close
Course Close (1:34)
Course Quiz and Certificate of Completion
Tracing Precedents and Formula Auditing
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock