Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Advanced Excel Formulas & Functions
Getting Started
Course Structure & Outline (1:28)
DOWNLOAD: Course Resources
Setting Expectations (3:36)
Excel Formulas 101
Introduction (1:48)
Formula Syntax (11:03)
Fixed & Relative References (8:42)
Common Error Types (8:47)
Formula Auditing: Trace Precedents & Dependents (10:14)
Formula Auditing: Evaluate Formula & Error Checking (7:48)
Ctrl Shortcuts (7:30)
Function Shortcuts (9:44)
Alt Key Tips (4:16)
PRO TIP: Data Validation (6:12)
Congrats, You're a Developer! (5:07)
QUIZ: Formulas 101
HOMEWORK: Formulas 101
Conditional Statements & Logical Operators
Introduction (1:11)
Anatomy of the IF Statement (4:01)
Nested IF Statements (4:53)
AND/OR Operators (8:47)
NOT (<>) Operators (3:17)
Addressing Errors with IFERROR (4:03)
Common IS Statements (4:00)
QUIZ: Conditional Statements & Logical Operators
HOMEWORK: Logical Operators
Statistical Functions
Introduction (1:09)
Basic Stats Functions (5:17)
SMALL/LARGE & RANK/PERCENTRANK (6:15)
RAND() & RANDBETWEEN (2:12)
The SUMPRODUCT Function (3:55)
SUMPRODUCT Demo (4:57)
COUNTIFS/SUMIFS/AVERAGEIFS (4:49)
DEMO: Basic Dashboards with Stats Functions (8:09)
Combining COUNTIF & SUMPRODUCT (7:57)
PRO TIP: Data Profiling with COUNT & SUMPRODUCT (16:06)
QUIZ: Statistical Functions
HOMEWORK: Statistical Functions
Lookup & Reference Functions
Introduction (1:18)
Named Arrays (3:30)
ROW/ROWS & COLUMN/COLUMNS (3:18)
VLOOKUP & HLOOKUP (5:53)
DEMO: Joining Data with VLOOKUP (6:23)
Combining IFERROR & VLOOKUP (4:30)
VLOOKUP Range Options (6:18)
Approximate Match Lookups (5:05)
The INDEX Function (1:59)
The MATCH Function (2:32)
Combining INDEX & MATCH (6:08)
Combining MATCH & VLOOKUP (4:47)
UPDATE: VLOOKUP Correction (5:05)
SNEAK PEEK: XLOOKUP (Office 365 Only) (4:29)
DEMO: Building Dashboards with XLOOKUP (18:16)
Selecting List Items with CHOOSE (13:15)
The OFFSET Function (2:05)
Combining OFFSET & COUNTA (2:58)
DEMO: Building a Scrolling Chart with OFFSET (9:51)
QUIZ: Lookup & Reference Functions
HOMEWORK: Lookup & Reference Functions
Text Functions
Introduction (0:50)
UPPER, LOWER, PROPER & TRIM (3:32)
CONCATENATE (&) (3:26)
LEFT, MID, RIGHT & LEN (3:31)
TEXT & VALUE (4:20)
SEARCH & FIND (5:04)
Categorizing Data with IF(ISNUMBER(SEARCH)) (5:39)
Combining RIGHT, LEN & SEARCH (5:43)
The SUBSTITUTE Function (2:21)
QUIZ: Text Functions
HOMEWORK: Text Functions
Date & Time Functions
Introduction (2:08)
Understanding DATEVALUE (5:05)
Date Formatting & Fill Series (3:39)
TODAY() & NOW() (2:16)
YEAR/MONTH/DAY & HOUR/MINUTE/SECOND (3:04)
The EOMONTH Function (6:17)
The YEARFRAC Function (2:27)
WEEKDAY, WORKDAY & NETWORKDAYS (7:22)
The DATEDIF Function (4:23)
DEMO: Budget Pacing Tool (11:07)
QUIZ: Date & Time Functions
HOMEWORK: Date & Time Functions
Formula-Based Formatting
Introduction (1:26)
Creating and Managing Formula-Based Rules (4:39)
DEMO: Highlighting Rows with MOD (3:50)
DEMO: Formatting Cells Based on Values (3:29)
DEMO: Formatting Cells with Stats Functions (5:28)
DEMO: Formatting Cells with Text & Conditional Functions (5:08)
QUIZ: Formula-Based Formatting
HOMEWORK: Formula-Based Formatting
Dynamic Array Formulas
Intro & Compatibility (3:50)
Project Intro: Maven Recruiters (2:12)
Legacy vs. "Dynamic" Excel (7:46)
Spill Range Properties (6:58)
PRO TIP: Growing Source Data (11:08)
Common Dynamic Array Functions (1:31)
The SORT Function (7:44)
The SORTBY Function (5:03)
The FILTER Function (14:54)
The UNIQUE Function (4:51)
PRO TIP: Drop-Down Lists with SORT & UNIQUE (4:13)
Combining SORT, FILTER & UNIQUE (3:51)
Referencing Spill Ranges in Calculations (5:18)
Conditional Formatting with Dynamic Arrays (4:31)
PRO TIP: Creating Charts from Dynamic Arrays (5:55)
The SEQUENCE Function (1:47)
PRO TIP: Top N Calculations with SEQUENCE & LARGE (8:55)
The RANDARRAY Function (2:35)
PRO TIP: Creating Random Top N Lists (9:55)
The FREQUENCY Function (9:22)
The TRANSPOSE Function (8:43)
PRO TIP: Joining Dynamic Arrays with CHOOSE (8:11)
PRO TIP: Declaring Variables with LET (11:14)
QUIZ: Dynamic Array Formulas
HOMEWORK: Dynamic Array Formulas
Bonus Functions
Introduction (1:38)
The INDIRECT Function (10:55)
The HYPERLINK Function (6:19)
Real-Time Data with WEBSERVICE & FILTERXML (7:16)
NEW: Open Weather Map WEBSERVICE demo (9:14)
QUIZ: Bonus Functions
Wrapping Up
More from Maven Analytics
Combining COUNTIF & SUMPRODUCT
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock