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)
Multi-Cell Formulas: Returning the Nth Largest Items from a List
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock
course builder. Create your online course today.
Start now
course builder. Create your online course today.
Start now
course builder. Create your online course today.
Start now
