Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Microsoft Excel: Advanced Formulas & Functions
Getting Started
Course Structure & Outline (4:12)
Downloading the Homework Exercises (1:42)
Excel Formulas 101
Introduction (1:06)
The Formula Library & Auditing Tools (4:37)
DEMO: Evaluate Formula (1:25)
Basic Formula Syntax (3:31)
Fixed & Relative Cell References (4:57)
Common Errors & IFERROR (7:25)
Function, CTRL & ALT Shortcuts (6:36)
BONUS: Mac Shortcuts (2:08)
Data Validation Rules (3:28)
Fixed vs. Volatile Functions (3:21)
QUIZ: Formulas 101
HOMEWORK: Formulas 101 (0:46)
New Lecture
Conditional Statements & Logical Operators
Introduction (1:23)
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: Conditional Statements & Logical Operators (0:44)
Statistical Functions
Introduction (1:12)
Basic Stats Functions (5:17)
SMALL/LARGE & RANK/PERCENTRANK (6:19)
RAND() & RANDBETWEEN (2:12)
The SUMPRODUCT Function (3:55)
COUNTIFS/SUMIFS/AVERAGEIFS (4:49)
DEMO: Basic Dashboards with Stats Functions (8:09)
QUIZ: Statistical Functions
HOMEWORK: Statistical Functions (0:35)
Lookup & Reference Functions
Introduction (1:23)
Named Arrays (3:30)
ROW/ROWS & COLUMN/COLUMNS (3:18)
VLOOKUP & HLOOKUP (5:54)
DEMO: Joining Data with VLOOKUP (6:23)
Combining IFERROR & VLOOKUP (4:30)
VLOOKUP Range Options (6:18)
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)
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 (0:54)
Text Functions
Introduction (0:57)
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 (0:59)
Date & Time Functions
Introduction (2:13)
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:24)
The DATEDIF Function (4:23)
DEMO: Budget Pacing Tool (11:07)
QUIZ: Date & Time Functions
HOMEWORK: Date & Time Functions (0:35)
Formula-Based Formatting
Introduction (1:30)
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 (0:34)
Basic Array Formulas
Introduction (1:26)
Rules of Array Formulas (3:24)
Pros & Cons of Array Formulas (2:15)
Vertical, Horizontal & 2-D Array Constants (6:26)
Using Array Constants in Formulas (3:38)
Named Array Constants (4:31)
The TRANSPOSE Function (4:21)
Linking Data: Array vs. Non-Array Comparison (2:36)
DEMO: Returning the "X" Largest Values (3:09)
DEMO: Counting Characters Across Cells (1:52)
DEMO: Creating a "MAX IF" Function (Part 1) (3:09)
DEMO: Creating a "MAX IF" Function (Part 2) (6:54)
The Double Unary Operator ("--") (3:49)
QUIZ: Array Formulas
HOMEWORK: Array Formulas (2:09)
Badass Bonus Functions
Introduction (1:41)
The INDIRECT Function (10:55)
The HYPERLINK Function (6:19)
Real-Time Data with WEBSERVICE & FILTERXML (7:16)
QUIZ: Badass Bonus Functions
Wrapping Up
Resources & Next Steps (0:41)
Data Validation Rules
Complete and Continue
Discussion
0
comments
Load more
0 comments