Module 1 
Before We Dive In 

Unit 1 
Course Structure & Outline 
Unit 2 
Resource Files and Datasets 
Unit 3 
BONUS: Extra Homework Exercises 
Module 2 
Excel Formulas 101 

Unit 1 
Introduction 
Unit 2 
The Formula Library & Auditing Tools 
Unit 3 
DEMO: Using the Evaluate Formula Tool 
Unit 4 
Basic Formula Syntax 
Unit 5 
Fixed, Relative, & Mixed References 
Unit 6 
Common Errors & the IFERROR statement 
Unit 7 
Function, CTRL & ALT Shortcuts 
Unit 8 
BONUS: Shortcuts for Mac Users 
Unit 9 
Creating Custom Data Validation Rules 
Unit 10 
Fixed vs. Volatile Functions 
Unit 11 
HOMEWORK: Formulas 101 
Module 3 
Conditional Statements & Logical Operators 

Unit 1 
Introduction 
Unit 2 
Anatomy of the IF Statement 
Unit 3 
Nested IF Statements 
Unit 4 
AND/OR Operators 
Unit 5 
NOT vs. "<>" 
Unit 6 
Fixing Errors with IFERROR 
Unit 7 
Common IS Statements 
Unit 8 
HOMEWORK: Logical Operators 
Module 4 
Statistical Functions 

Unit 1 
Introduction 
Unit 2 
Basic Statistical Functions 
Unit 3 
SMALL/LARGE & RANK/PERCENTRANK 
Unit 4 
RAND() & RANDBETWEEN 
Unit 5 
The SUMPRODUCT Function 
Unit 6 
COUNTIFS/SUMIFS/AVERAGEIF 
Unit 7 
PROJECT SHOWCASE: Building a Basic Dashboard with COUNTIFS & SUMIFS 
Unit 8 
HOMEWORK: Statistical Functions 
Module 5 
Lookup/Reference Functions 

Unit 1 
Introduction 
Unit 2 
Working with Named Arrays 
Unit 3 
ROW/ROWS & COLUMN/COLUMNS 
Unit 4 
VLOOKUP/HLOOKUP 
Unit 5 
Joining Data with VLOOKUP 
Unit 6 
Fixing Errors with IFERROR & VLOOKUP 
Unit 7 
VLOOKUP Reference Array Options 
Unit 8 
The INDEX Function 
Unit 9 
The MATCH Function 
Unit 10 
Using INDEX & MATCH together 
Unit 11 
Combining MATCH with VLOOKUP 
Unit 12 
UPDATE: VLOOKUP Correction 
Unit 13 
The OFFSET Function 
Unit 14 
Combining OFFSET with COUNTA to return a variable list 
Unit 15 
PROJECT SHOWCASE: Using OFFSET to create a dynamic scrolling chart 
Unit 16 
HOMEWORK: Lookup/Reference Functions 
Module 6 
Text Functions 

Unit 1 
Introduction 
Unit 2 
UPPER/LOWER/PROPER & TRIM 
Unit 3 
The CONCATENATE Function (&) 
Unit 4 
LEFT/MID/RIGHT & LEN 
Unit 5 
TEXT/VALUE 
Unit 6 
SEARCH/FIND 
Unit 7 
Categorizing Data with IF(ISNUMBER(SEARCH)) 
Unit 8 
Combining RIGHT, LEN, and SEARCH 
Unit 9 
BONUS: The SUBSTITUTE Function 
Unit 10 
HOMEWORK: Text Functions 
Module 7 
Date & Time Functions 

Unit 1 
Introduction 
Unit 2 
DATEVALUE: Your New BFF 
Unit 3 
Date Formatting & Fill Series 
Unit 4 
TODAY()/NOW() 
Unit 5 
YEAR/MONTH/DAY & HOUR/MINUTE/SECOND 
Unit 6 
The EOMONTH Function 
Unit 7 
The YEARFRAC Function 
Unit 8 
WEEKDAY, WORKDAY & NETWORKDAYS 
Unit 9 
The DATEDIF Function 
Unit 10 
PROJECT SHOWCASE: Designing a Sample Budget Pacing Tool 
Unit 11 
HOMEWORK: Date & Time Functions 
Module 8 
FormulaBased Formatting 

Unit 1 
Introduction 
Unit 2 
Creating, Editing & Managing FormulaBased Rules 
Unit 3 
Highlighting Every Other Row Using the MOD function 
Unit 4 
Formatting Cells Based on the Value of Another Cell 
Unit 5 
Formatting Cells Using Statistical Functions 
Unit 6 
Formatting Cells Using Text Functions & Logical Operators 
Unit 7 
HOMEWORK: FormulaBased Formatting 
Module 9 
Array Formulas 

Unit 1 
Introduction 
Unit 2 
Rules of Array Functions 
Unit 3 
Pros & Cons of Array Functions 
Unit 4 
Vertical, Horizontal, and 2Dimensional Array Constants 
Unit 5 
Using Array Constants in Formulas 
Unit 6 
Named Array Constants 
Unit 7 
The Transpose Function 
Unit 8 
Linking Data Between Sheets: Array vs. NonArray Comparison 
Unit 9 
Returning the "X" Largest Values in a Range 
Unit 10 
Counting Characters Across Cells 
Unit 11 
Creating a "MAX IF" Array Formula 
Unit 12 
BONUS: "MAX IF" with Multiple Criteria 
Unit 13 
BONUS: Using the Double Unary Operator ("") 
Unit 14 
HOMEWORK: Array Functions 
Module 10 
Badass Bonus Functions 

Unit 1 
Introduction 
Unit 2 
The INDIRECT Function 
Unit 3 
HYPERLINK: as Awesome as it Sounds 
Unit 4 
Tapping into RealTime Data with WEBSERVICE & FILTERXML 
Module 11 
Wrapping Up 

Unit 1 
Conclusion & Next Steps 