Unit 1 |
Course Structure & Outline |

Unit 2 |
Resource Files and Datasets |

Unit 3 |
BONUS: Extra Homework Exercises |

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 |

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 |

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 |

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 |

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 |

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 |

Unit 1 |
Introduction |

Unit 2 |
Creating, Editing & Managing Formula-Based 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: Formula-Based Formatting |

Unit 1 |
Introduction |

Unit 2 |
Rules of Array Functions |

Unit 3 |
Pros & Cons of Array Functions |

Unit 4 |
Vertical, Horizontal, and 2-Dimensional 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. Non-Array 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 |

Unit 1 |
Introduction |

Unit 2 |
The INDIRECT Function |

Unit 3 |
HYPERLINK: as Awesome as it Sounds |

Unit 4 |
Tapping into Real-Time Data with WEBSERVICE & FILTERXML |

Unit 1 |
Conclusion & Next Steps |