The curriculum is empty
- Home
- All Courses
- Advanced Excel Formulas Functions
Advanced Excel Formulas Functions
Course Curriculum
Excel Formulas Basics
- Introduction
- Formula Syntax
- Fixed & Relative References
- Common Error Types
- Formula Auditing: Trace Precedents & Dependents
- Formula Auditing: Evaluate Formula & Error Checking
- Ctrl Shortcuts
- Function Shortcuts
- Alt Key Tips
- PRO TIP: Data Validation
- HOMEWORK
Conditional Statements & Logical Operators
- Introduction
- Anatomy of the IF Statement
- Nested IF Statements
- AND/OR Operators
- NOT
- Addressing Errors with IFERROR
- Common IS Statements
- HOMEWORK
Statistical Functions
- Introduction
- Basic Stats Functions
- SMALL/LARGE & RANK/PERCENTRANK
- RAND() & RANDBETWEEN
- The SUMPRODUCT Function
- SUMPRODUCT Demo
- COUNTIFS/SUMIFS/AVERAGEIFS
- DEMO: Basic Dashboards with Stats Functions
- Combining COUNTIF & SUMPRODUCT
- QUIZ: Statistical Functions
- HOMEWORK
Lookup & Reference Functions
- Introduction
- Named Arrays
- ROW/ROWS & COLUMN/COLUMNS
- VLOOKUP & HLOOKUP
- DEMO: Joining Data with VLOOKUP
- Combining IFERROR & VLOOKUP
- VLOOKUP Range Options
- Fuzzy Match Lookups
- The INDEX Function
- The MATCH Function
- Combining INDEX & MATCH
- Combining MATCH & VLOOKUP
- UPDATE: VLOOKUP Correction
- The OFFSET Function
- Combining OFFSET & COUNTA
- DEMO: Building a Scrolling Chart with OFFSET
- QUIZ: Lookup & Reference Functions
- HOMEWORK
Text Functions
- Introduction
- UPPER, LOWER, PROPER & TRIM
- CONCATENATE
- LEFT, MID, RIGHT & LEN
- TEXT & VALUE
- SEARCH & FIND
- Categorizing Data with IF(ISNUMBER(SEARCH))
- Combining RIGHT, LEN & SEARCH
- The SUBSTITUTE Function
- QUIZ: Text Functions
- HOMEWORK
Date & Time Functions
- Introduction
- Understanding DATEVALUE
- Date Formatting & Fill Series
- TODAY() & NOW()
- YEAR/MONTH/DAY & HOUR/MINUTE/SECOND
- The EOMONTH Function
- The YEARFRAC Function
- WEEKDAY, WORKDAY & NETWORKDAYS
- The DATEDIF Function
- DEMO: Budget Pacing Tool
- QUIZ: Date & Time Functions
- HOMEWORK
Formula-Based Formatting
- Introduction
- Creating and Managing Formula-Based Rules
- DEMO: Highlighting Rows with MOD
- DEMO: Formatting Cells Based on Values
- DEMO: Formatting Cells with Stats Functions
- DEMO: Formatting Cells with Text & Conditional Functions
- QUIZ: Formula-Based Formatting
- HOMEWORK
Basic Array Formulas
- Introduction
- Rules of Array Formulas
- Pros & Cons of Array Formulas
- Vertical, Horizontal & 2-D Array Constants
- Using Array Constants in Formulas
- Named Array Constants
- The TRANSPOSE Function
- Linking Data: Array vs. Non-Array Comparison
- DEMO: Returning the X Largest Values
- DEMO: Counting Characters Across Cells
- DEMO: Creating a MAX IF Function
- DEMO: Creating a MAX IF Function
- The Double Unary Operator
- QUIZ: Array Formulas
- HOMEWORK
Bonus Functions
- Introduction
- The INDIRECT Function
- The HYPERLINK Function
- Real-Time Data with WEBSERVICE & FILTERXML
- Open Weather Map WEBSERVICE demo
- Wrapping Up
- Resources & Next Steps