This course was created with the
course builder. Create your online course today.
Start now
Create your course
with
Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Microsoft Excel: From Beginner to Formula Master
Module 1: Introduction to Excel
Module 1 Overview (1:37)
1.1 The What, Why, and How of Excel
The What, Why, and How of Excel (4:25)
1.2 Excel Components and User Interface
Excel Components and File Extensions (2:36)
Excel's User Interface: The Ribbon (4:55)
Excel's User Interface: Quick Access and Formula Toolbars (4:07)
Excel's User Interface: The Worksheet (3:58)
1.3 Introduction to Formulas and Functions
Introduction to Formulas and Functions (2:57)
Quiz 1: Formulas and Functions
Order of Operations and Formulas (6:48)
Using Excel's Built in Functions (13:18)
1.4 Overview of Cell References
Understanding Cell References (12:02)
Quiz 2: Cell References
Module 2: Process and Transform Data
Module 2 Overview (2:44)
2.1 Performing Data Aggregation
Module 2.1 Overview (1:34)
Simple Aggregation Functions (12:00)
Quiz 3: Simple Aggregation Functions
Single Criteria Conditional Aggregation: Static Criteria (8:13)
Single Criteria Conditional Aggregation: Dynamic Criteria (8:59)
Single Criteria Conditional Aggregation: >, <, and <> Conditions (10:18)
Single Criteria Conditional Aggregation: Combining Aggregations (3:39)
Quiz 4: Single Criteria Conditional Aggregation Functions
Multiple Criteria Conditional Aggregation (10:11)
Multiple Criteria Conditional Aggregation: AND Testing (4:40)
Multiple Criteria Conditional Aggregation: OR Testing (4:13)
Multiple Criteria Conditional Aggregation: Using Arrays for OR Testing (6:42)
Multiple Criteria Conditional Aggregation: More on OR Testing (8:17)
Quiz 5: Multiple Criteria Conditional Aggregation Functions
Bonus: Using Array Multiplication to Aggregate Data from Many Columns (5:03)
Database Aggregation Functions: Overview (3:39)
Database Aggregation Functions: Building a Reporting Tool (10:03)
Database Aggregations: Advanced Searches (5:30)
Quiz 6: Database Aggregation Functions
Performing Data Aggregation: Wrap Up (3:05)
2.2 Performing Logical Operations
Module 2.2 Overview (1:17)
Logical Operators (8:10)
Conditional Functions Overview (3:41)
More on Conditional Functions: The If Function (7:44)
Handling more than two Outcomes: Nesting If Functions (6:29)
Simple Error Handling with the Iferror Function (2:52)
Logical Value Functions: True, False, and Not (3:02)
Quiz 7: Logical Operations
Performing Logical Operations: Wrap Up (1:42)
2.3 Mastering Lookups and References
Module 2.3 Overview (2:41)
Choose Function: An Overview and Simple Example (3:35)
Choose Function: Making Other Functions Dynamic (8:54)
Choose Function: Transforming Quality Scores and Selecting a Forecast Model (7:00)
Choose Function: Nesting Functions Inside Choose (5:42)
Quiz 8: The Choose Function
Vlookup and Hlookup: An Overview (3:41)
Vlookup: Key Concepts (10:09)
Vlookup: Adding Meaning to Classifications (6:30)
Vlookup: Making it Unique (6:50)
Vlookup: Understanding Approximate Match (5:26)
Vlookup: A Federal Income Tax Calculator (8:07)
Vlookup: Searching Multiple Tables (2:53)
Hlookup: More of the Same, Kind of (6:39)
Quiz 9: The Vlookup and Hlookup Functions
Index and Match: An Overview (3:31)
Match: Finding the Position (4:16)
Match: Understanding the Approximate Match (5:56)
Index: A Special Case with 1D Arrays (6:39)
Index: Getting Values from a Table (5:20)
Index: Getting Values from Multiple Tables (3:01)
Index: Returning a Reference Instead of a Value (6:29)
Quiz 10: The Index and Match Functions
Index and Match: Benefits of Index / Match Lookup (4:44)
Index and Match: Left lookups and Reference Stability (6:41)
Index and Match: Building a Dynamic Order Report (8:46)
Index and Match: Mastering the Approximate Lookup (4:06)
Index and Match: Putting the Approximate Match to Use (6:59)
Index and Match: The Power of Offsetting Lookups (4:08)
Index and Match: Searching Multiple Tables - Revisited (7:50)
Index and Match: Finding the First Occurrence (5:22)
Index and Match: Returning the Entire Row or Column (6:21)
Quiz 11: Combining the Index and Match Functions
Defined Names: Overview and Simple Example (7:30)
Defined Names: More than Constants - Storing Ranges and Calculations (5:50)
Quiz 12: Defined Names
Offset: Understanding the Offset Function (6:19)
Offset: Creating a Dynamic Dropdown List (8:23)
Offset: Creating a Dynamic Calculation (4:19)
Offset: Another Dynamic Calculation (5:34)
Offset: Dynamic Charting (5:13)
Quiz 13: The Offset Function
Row and Column: An Overview (5:27)
Row and Column: Finding the Last Row and Automatic Numbering (5:45)
Rows and Columns: An Overview (3:47)
Rows and Columns: Cleaning Up Bad Data (3:52)
Quiz 14: Row, Column, Rows, and Columns
Lookups, References, and Arrays: Returning More than the First Occurrence (13:09)
Lookups, References, and Arrays: Managing Schedules - Part 1 (7:35)
Lookups, References, and Arrays: Managing Schedules - Part 2 (10:52)
Lookups, References, and Arrays: Managing Schedules - Part 3 (6:14)
Lookups, References, and Arrays: Searching Data to Return Records (14:24)
Address: An Overview (4:26)
Address: Finding the Last Cell of A Dynamic List (2:54)
Address: Getting Data From Templated Worksheets Quickly (5:38)
Transpose: An Overview (4:09)
Transpose: Transposing Data Inside Array Formulas (4:17)
Hyperlink: An Overview (2:42)
Hyperlink: Workbook and File Navigation (7:13)
Quiz 15: Address, Transpose, and Hyperlink Functions
Mastering Lookups and References: Wrap up (1:27)
2.4 Working with Informationals
Module 2.4 Overview (1:31)
Value Testers: An Overview (8:28)
Value Testers: Special Treatment for Blanks (6:00)
Value Testers: Conditionally Controlling Action Based on Input Data Type (4:48)
Error Testers: An Overview (3:19)
Error Testers: Enhancing Lookup Functions (7:38)
Error Testers: Preventing Errors from Bubbling Up (4:44)
Core Informationals: An Overview (6:07)
Core Informationals: Formatting Details and File Names (4:20)
Core Informationals: Last Active Cell and Environment Information (3:26)
Core Informationals: The Type Function (2:55)
Quiz 16: Informationals
Working with Informationals: Wrap Up (1:17)
2.5 Working with Dates and Times
Module 2.5 Overview (1:57)
Date and Time Foundations: Serial Numbers (5:13)
Date and Time Foundations: Format vs. Value (4:55)
Date and Time Foundations: Matching Units (7:58)
Date Information Functions: An Overview (3:25)
Quiz 17: Date and Time Foundations
Date Information Functions: Breaking Date-Time Values Down (2:31)
Date Information Functions: Aggregating Data on Different Time Periods - Part 1 (9:31)
Date Information Functions: Aggregating Data on Different Time Periods - Part 2 (5:41)
Date Information Functions: Analyzing Inbound Work for Staffing - Part 1 (7:40)
Date Information Functions: Analyzing Inbound Work for Staffing - Part 2 (8:06)
Quiz 18: Date Information Functions
Date Math: An Overview - Part 1 (2:51)
Date Math: Forecasting Revenues Based on Sales Pipeline (7:38)
Date Math: Working with Strange Payment Terms (2:21)
Date Math: Understanding the Datedif Function (4:48)
Date Math: A Shortcut for Calculating Age (3:31)
Date Math: An Overview - Part 2 (3:35)
Date Math: Calculating Project Completion Dates - Consecutive Tasks (2:55)
Date Math: Calculating Project Completion Dates - Dependent Tasks (10:10)
Date Math: Analyzing Historical Duration Data (4:44)
Date Math: An Overview - Part 3 (4:08)
Date Math: Calculating the Accrued Interest on a Bond (4:46)
Quiz 19: Date Math Functions
Date Creation: An Overview (2:57)
Date Creation: Dynamically Calculating Age (4:22)
Date Creation: Dynamic Reports (4:29)
Date Creation: Building Dates, Converting Text into Serial #s, & Handling Units (8:08)
Quiz 20: Date Creation Functions
Working with Dates and Times: Wrap Up (1:35)
2.6 Working with Strings
Module 2.6 Overview (1:50)
String Basics: Strings and Data Entry (6:56)
String Information: An Overview - Part 1 (1:25)
String Information: Cleaning up Mixed Data (4:09)
String Information: Testing String Equality when Case Matters (3:30)
String Information: An Overview - Part 2 (1:42)
String Information: Finding Information in a String (4:51)
String Information: Case Sensitive Data Aggregation (4:58)
Quiz 21: String Information Functions
String Manipulation: An Overview - Part 1 (4:18)
String Manipulation: String Concatenation of Function Outputs (3:12)
String Manipulation: String Concatenation of Function Inputs (2:16)
String Manipulation: Enhancing an Earlier Example (6:02)
String Manipulation: An Overview - Part 2 (2:45)
String Manipulation: Building Dates from Numbers (9:25)
String Manipulation: Extracting Data based on Character Position (5:04)
String Manipulation: Cleaning up Leading and Trailing Spaces for Lookups (4:03)
String Manipulation: An Overview - Part 3 (3:26)
String Manipulation: An Overview - Part 4 (2:42)
String Manipulation: Parse Strings and Remove Duplicates (7:57)
String Manipulation: Replacing Specific Instances of Text (5:44)
String Manipulation: Keeping Content and Creating CSV's (3:33)
String Manipulation: An Overview - Part 5 (3:17)
String Manipulation: Creating Static Formatted Text Strings (10:34)
String Manipulation: Creating Dynamic Formatted Text Strings (9:07)
String Manipulation: Text Function Shortcuts and Creating Numerical Values (4:04)
String Manipulation: Automating Text Summaries in Reports (9:12)
String Manipulation: Enhancing an Earlier Example - Take 2 (5:20)
Quiz 22: String Manipulation Functions
Working with Strings: Wrap Up (1:43)
2.7 Working with Array Formulas
Module 2.7 Overview (1:13)
Array Basics: Review the Array Cheat Sheet (3:47)
Array Basics: Single-Cell vs Multi-Cell Array Formulas (4:28)
Array Basics: Rules Review - Can't Change Part of an Array (3:48)
Array Basics: Array Math (3:28)
Array Basics: Array Math - A Special Case (2:07)
Quiz 23: Array Basics
Single-Cell Formulas: Expanding Functions by Passing them Arrays (2:20)
Single-Cell Formulas: Finding the Smallest Value (4:04)
Single-Cell Formulas: Finding the Largest Value (3:25)
Single-Cell Formulas: A General Solution to Aggregating Every nth Value (6:46)
Multi-Cell Formulas: Simple Calculations and Range Position (5:16)
Multi-Cell Formulas: Returning the Nth Largest Items from a List (6:32)
Multi-Cell Formulas: Returning the Nth Smallest Items from a List (3:53)
Multi-Cell Formulas: Returning the Nth Items from a List when N is Large (6:10)
Array Constants: Entering Array Constants (6:27)
Array Constants: Defined Name Array Constants (2:49)
Array Constants: Storing Static Values and Making Formulas Readable (6:12)
Array Data Aggregation: Understanding Logical Tests with Arrays (9:39)
Array Data Aggregation: A General Solution for Maxif and Minif (10:55)
Array Data Aggregation: Translating Counting Problems into Array Formulas (11:28)
Array Data Aggregation: Translating Aggregation Problems into Array Formulas (8:58)
Array Data Aggregation: Summing up Nth Largest or Smallest Values (3:54)
Array Data Aggregation: Counting when there's a Single Logical Test (2:38)
Array Data Aggregation: Building a Histogram - Part 1 (7:44)
Array Data Aggregation: Building a Histogram - Part 2 (10:44)
Array Data Aggregation: Aggregation Reference Sheet Overview (3:21)
Quiz 24: Arrays and Array Formulas
Working with Arrays: Wrap Up (3:33)
String Manipulation: Extracting Data based on Character Position
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock