Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Free Preview: Excel Formulas & Functions
Getting Started
About this Preview Course
Full Course Structure & Outline (4:12)
Downloading the Homework Exercises
Excel Formulas 101
Introduction (1:06)
The Formula Library & Auditing Tools (4:37)
DEMO: Evaluate Formula (1:25)
Basic Formula Syntax
Fixed & Relative Cell References (4:57)
Common Errors & IFERROR
Function, CTRL & ALT Shortcuts (6:36)
BONUS: Mac Shortcuts
Data Validation Rules
Fixed vs. Volatile Functions
QUIZ: Formulas 101
HOMEWORK: Formulas 101
Conditional Statements & Logical Operators
Introduction (1:23)
Anatomy of the IF Statement (4:01)
Nested IF Statements
AND/OR Operators
NOT (<>) Operators
Addressing Errors with IFERROR (4:03)
Common IS Statements
QUIZ: Conditional Statements & Logical Operators
HOMEWORK: Conditional Statements & Logical Operators
Statistical Functions
Introduction (1:12)
Basic Stats Functions
SMALL/LARGE & RANK/PERCENTRANK (6:19)
RAND() & RANDBETWEEN
The SUMPRODUCT Function
COUNTIFS/SUMIFS/AVERAGEIFS
DEMO: Basic Dashboards with Stats Functions (8:09)
QUIZ: Statistical Functions
HOMEWORK: Statistical Functions
Lookup & Reference Functions
Introduction (1:23)
Named Arrays
ROW/ROWS & COLUMN/COLUMNS
VLOOKUP & HLOOKUP (5:54)
DEMO: Joining Data with VLOOKUP
Combining IFERROR & VLOOKUP
VLOOKUP Range Options
The INDEX Function
The MATCH Function
Combining INDEX & MATCH (6:08)
Combining MATCH & VLOOKUP
The OFFSET Function
Combining OFFSET & COUNTA
DEMO: Building a Scrolling Chart with OFFSET (9:51)
QUIZ: Lookup & Reference Functions
HOMEWORK: Lookup & Reference Functions
Text Functions
Introduction (0:57)
UPPER, LOWER, PROPER & TRIM
CONCATENATE (&)
LEFT, MID, RIGHT & LEN (3:31)
TEXT & VALUE
SEARCH & FIND
Categorizing Data with IF(ISNUMBER(SEARCH)) (5:39)
Combining RIGHT, LEN & SEARCH (5:43)
The SUBSTITUTE Function
QUIZ: Text Functions
HOMEWORK: Text Functions
Date & Time Functions
Introduction (2:13)
Understanding DATEVALUE (5:05)
Date Formatting & Fill Series
TODAY() & NOW() (2:16)
YEAR/MONTH/DAY & HOUR/MINUTE/SECOND
The EOMONTH Function (6:17)
The YEARFRAC Function
WEEKDAY, WORKDAY & NETWORKDAYS
The DATEDIF Function
DEMO: Budget Pacing Tool
QUIZ: Date & Time Functions
HOMEWORK: Date & Time Functions
Formula-Based Formatting
Introduction (1:30)
Creating and Managing Formula-Based Rules
DEMO: Highlighting Rows with MOD
DEMO: Formatting Cells Based on Values (3:29)
DEMO: Formatting Cells with Stats Functions
DEMO: Formatting Cells with Text & Conditional Functions (5:08)
QUIZ: Formula-Based Formatting
HOMEWORK: Formula-Based Formatting
Intro to Array Formulas
Introduction (1:26)
Rules of Array Formulas
Pros & Cons of Array Formulas (2:15)
Vertical, Horizontal & 2-D Array Constants
Using Array Constants in Formulas (3:38)
Named Array Constants
The TRANSPOSE Function
Linking Data: Array vs. Non-Array Comparison
DEMO: Returning the "X" Largest Values (3:09)
DEMO: Counting Characters Across Cells
DEMO: Creating a "MAX IF" Function (Part 1)
DEMO: Creating a "MAX IF" Function (Part 2)
The Double Unary Operator ("--") (3:49)
QUIZ: Array Formulas
HOMEWORK: Array Formulas
Badass Bonus Functions
Introduction (1:41)
The INDIRECT Function
The HYPERLINK Function
Real-Time Data with WEBSERVICE & FILTERXML (7:16)
QUIZ: Badass Bonus Functions
Wrapping Up
Special Offer
ROW/ROWS & COLUMN/COLUMNS
Lesson content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock