## SIngle Course

### 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
• 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 