⚡ Excel Masterclass

Excel Formula Roadmap

The Complete Library: 100+ Formulas & Use Cases.

🔍

PART 1: The Formula Library

01. Lookup & Reference

VLOOKUP / HLOOKUP ○ XLOOKUP ○ INDEX / MATCH ○ FILTER ○ UNIQUE ○ SORT / SORTBY ○ OFFSET ○ INDIRECT ○ CHOOSE ○ LOOKUP (Legacy) ○

IF ○ IFS ○ AND / OR / NOT ○ XOR ○ SWITCH ○ IFERROR ○ IFNA ○ TRUE / FALSE ○

03. Math & Trig

SUM ○ SUMIF / SUMIFS ○ PRODUCT ○ QUOTIENT / MOD ○ POWER / SQRT ○ ROUND / UP / DOWN ○ CEILING / FLOOR ○ INT ○ ABS ○ RAND / BETWEEN ○

04. Statistical

AVERAGE / A ○ AVERAGEIF / S ○ COUNT / COUNTA ○ COUNTBLANK ○ COUNTIF / S ○ MIN / MAX ○ LARGE / SMALL ○ MEDIAN / MODE ○ STDEV.P / .S ○ RANK.EQ / .AVG ○

05. Text Functions

TRIM / CLEAN ○ LEFT / RIGHT ○ MID ○ LEN ○ FIND / SEARCH ○ CONCAT / TEXTJOIN ○ SUBSTITUTE ○ REPLACE ○ TEXT / VALUE ○ UPPER / LOWER ○

06. Date & Time

TODAY / NOW ○ DATE / TIME ○ YEAR / MONTH ○ HOUR / MIN / SEC ○ DATEDIF ○ EOMONTH / EDATE ○ WORKDAY / INTL ○ NETWORKDAYS ○ WEEKNUM / DAY ○ TEXT (Date) ○

PMT (Payments) ○ RATE ○ NPV (Net Present) ○ IRR ○ FV (Future Value) ○ PV (Present Value) ○ NPER ○

08. Information

ISNUMBER / TEXT ○ ISBLANK ○ ISERROR / ISNA ○ TYPE / CELL ○ ERROR.TYPE ○

09. Dynamic Arrays

SEQUENCE ○ RANDARRAY ○ TRANSPOSE ○ VSTACK / HSTACK ○ TOCOL / TOROW ○ TAKE / DROP ○ CHOOSECOLS ○

10. Engineering

BIN2DEC / DEC2BIN ○ HEX2DEC ○ FACT (Factorial) ○

PART 2: Syntax & Essentials

Operators, Errors & Shortcuts

Math (+ - * / ^) ○ Logical (= > < >=) ○ References ($A$1 vs A1) ○ Text Join (&) ○ Wildcards (* ?) ○ Errors (#N/A #REF!) ○ Ranges (: ,) ○ Structured Refs ([@Col]) ○

PART 3: Combined Formulas (Advanced)

Advanced Lookups

01. INDEX + MATCH ○ 02. INDEX + MATCH + MATCH ○ 03. VLOOKUP + CHOOSE ○ 04. XLOOKUP + XLOOKUP ○ 05. VLOOKUP + MATCH ○ 06. Multiple Criteria (Array) ○ 07. Lookup MAX Value ○ 08. Lookup Last Value ○ 09. Hyperlink Lookup ○ 10. Image Lookup ○ 11. Case Sensitive Lookup ○ 12. Closest Match (Fuzzy) ○ 13. Wildcard Search (*) ○ 14. OFFSET + MATCH ○ 15. INDIRECT + VLOOKUP ○

01. TRIM + CLEAN + PROPER ○ 02. Extract First Name ○ 03. Extract Last Name ○ 04. Extract Middle Text ○ 05. Count Words ○ 06. Remove Line Breaks ○ 07. Extract Numbers Only ○ 08. Split Cell to Rows ○ 09. KPI Sentence ○ 10. Get Username ○ 11. Mask Data (****) ○ 12. Generate Initials ○ 13. Reverse Text ○ 14. Remove Double Spaces ○ 15. Parse URL Domain ○

01. SUMPRODUCT Logic ○ 02. Nested IF Alternatives ○ 03. Weighted Averages ○ 04. Dynamic Sum (OFFSET) ○ 05. Tiered Commission ○ 06. IFS vs Nested IF ○ 07. SWITCH(TRUE) ○ 08. Count Between Dates ○ 09. Highlight Odd Rows ○ 10. 3D Sum (Multi-Sheet) ○ 11. Min Non-Zero ○ 12. Sum Visible Rows ○ 13. Rank within Group ○ 14. Count Unique (Cond) ○ 15. Average Top 5 ○

Date & Projects

01. Project Due Date ○ 02. Age Calculation ○ 03. Days Remaining ○ 04. Fiscal Year ○ 05. Fiscal Quarter ○ 06. First Day Next Month ○ 07. Last Day Month ○ 08. Get Day Name ○ 09. Week Start Date ○ 10. Overlapping Dates ○ 11. Time Difference ○ 12. Convert Text Date ○ 13. Next Anniversary ○ 14. Nearest Workday ○ 15. Gantt Chart Data ○

01. Search Bar ○ 02. Top 10 Dynamic ○ 03. Dependent Dropdown ○ 04. Extract Duplicates ○ 05. Compare Lists ○ 06. Transpose Stack ○ 07. Calendar Gen ○ 08. Top N Bottom N ○ 09. Random Sample ○ 10. Running Total Array ○ 11. Split Cols ○ 12. Unpivot Data ○ 13. Sheet Index ○ 14. Dynamic Image ○ 15. Custom LAMBDA ○

⬅ Back to Main Home