Course Highlights
  • Learn how to THINK like Excel, and write powerful and dynamic Excel formulas from scratch
  • Automate, streamline, and completely revolutionize your workflow with Excel
  • Master unique tips & techniques that you won't find in ANY other course, guaranteed
  • Explore fun, interactive, and highly effective demos from a best-selling Excel instructor
  • Get LIFETIME access to project files, quizzes, homework exercises, and 1-on-1 expert support
  • Write advanced conditional, text, date and lookup functions, including XLOOKUP & Dynamic Arrays
Curriculum

4 Topics
Course Structure & Outline
READ ME: Important Notes for New Students
DOWNLOAD: Course Resources
Setting Expectations

14 Topics
Introduction: Excel Formulas 101
Excel Formula Syntax
Writing Efficient Formulas with Fixed & Relative References
Common Excel Error Types
Formula Auditing: Trace Precedents & Dependents
Formula Auditing: Evaluate Formula & Error Checking (PC Only)
Navigating Excel Worksheets with Ctrl Shortcuts
Saving Time with Function Shortcuts
Accessing Tools with Alt Key Tips
BONUS: Mac Shortcuts
PRO TIP: Creating Drop-Down Menus with Data Validation
Congrats You're a Developer!
Excel Formulas 101
HOMEWORK: Excel Formulas 101

9 Topics
Introduction
Anatomy of the IF Statement
Nesting Multiple IF Statements
Adding Conditional AND/OR Operators
Using NOT & "<>" Conditionals
Fixing Errors with IFERROR
Common IS Statements
Excel Logical Operators
HOMEWORK: Logical Operators

12 Topics
Introduction
Basic Excel Statistical Functions
Extracting Values with SMALL/LARGE & RANK/PERCENTRANK
Randomization with RAND() & RANDBETWEEN
Row-Level Calculation with SUMPRODUCT
DEMO: Calculating Shipping Costs with SUMPRODUCT
Conditional Aggregation with COUNTIFS SUMIFS & AVERAGEIFS
DEMO: Building a Basic Dashboard with COUNTIFS & SUMIFS
DEMO: Counting Duplicates with COUNTIF & SUMPRODUCT
PRO TIP: Data Profiling with COUNT & SUMPRODUCT
Excel Stats Functions
HOMEWORK: Common Stats Functions

21 Topics
Introduction
Working with Named Ranges
Counting Rows & Columns with ROW/ROWS & COLUMN/COLUMNS
Introduction to VLOOKUP/HLOOKUP
Joining Data with VLOOKUP
Fixing Errors with IFERROR & VLOOKUP
VLOOKUP Reference Array Options
Approximate Match Lookups
Navigating Cell Ranges with INDEX
Matching Text & Values with MATCH
Combining INDEX & MATCH to Dynamically Search Ranges
Combining MATCH & VLOOKUP for More Flexible Lookups
TROUBLESHOOTING: VLOOKUP with Duplicate Keys
SNEAK PEEK: XLOOKUP (Office 365 Only)
DEMO: Building Dashboards with XLOOKUP (Office 365 Only)
Selecting List Items with CHOOSE
Defining Ranges with OFFSET
Combining OFFSET with COUNTA to Create a Flexible Range
PRO TIP: Using OFFSET to Create Interactive Charts
Excel Lookup & Reference Functions
HOMEWORK: Lookup & Reference Functions

11 Topics
Introduction
Capitalization with UPPER LOWER PROPER & TRIM
Combining Text with CONCATENATE (&)
Extracting Strings with LEFT MID RIGHT & LEN
Converting Text to Values with TEXT & VALUE
Searching Text String with SEARCH & FIND
Categorizing Data with IF(ISNUMBER(SEARCH))
Combining RIGHT LEN and SEARCH
Replacing Text with SUBSTITUTE
Excel Text Functions
HOMEWORK: Text Functions

12 Topics
Introduction
Understanding Excel Date Syntax with DATEVALUE
Formatting Dates & Filling Date Series
Creating Real-Time Functions with TODAY & NOW
Extracting Time Periods with YEAR MONTH DAY HOUR MINUTE & SECOND
Calculating the Month Start or End with EOMONTH
Calculating % of Year with YEARFRAC
Defining Time Periods with WEEKDAY WORKDAY & NETWORKDAYS
Calculating Differences Between Dates with DATEDIF (Legacy Function)
DEMO: Building a Simple Budget Pacing Tool
Excel Date & Time Functions
HOMEWORK: Date & Time Functions

8 Topics
Introduction
Creating Editing & Managing Formula-Based Rules
Highlighting Rows Using the MOD Function
Formatting Based on the Value of Another Cell
Formatting Cells Using Stats Functions
Formatting Cells Using Text Functions & Logical Operators
Excel Formula-Based Formatting
HOMEWORK: Formula-Based Formatting

25 Topics
Intro & Compatibility
Project Intro: Maven Recruiters
Legacy vs. "Dynamic" Excel
Spill Range Properties
PRO TIP: Growing Source Data
Common Dynamic Array Functions
The SORT Function
The SORTBY Function
The FILTER Function
The UNIQUE Function
PRO TIP: Drop-Down Lists with SORT & UNIQUE
Combining SORT FILTER & UNIQUE
Referencing Spill Ranges in Calculations
Conditional Formatting with Dynamic Arrays
PRO TIP: Creating Charts from Dynamic Arrays
The SEQUENCE Function
PRO TIP: Top N Calculations with SEQUENCE & LARGE
The RANDARRAY Function
PRO TIP: Creating Random Top N Lists
The FREQUENCY Function
The TRANSPOSE Function
PRO TIP: Joining Dynamic Arrays with CHOOSE
PRO TIP: Declaring Variables with LET
QUIZ: Dynamic Array Formulas
HOMEWORK: Dynamic Array Formulas

6 Topics
Introduction
Creating Custom References with INDIRECT
Linking to Worksheet Locations with HYPERLINK
Real-Time Weather Conditions with WEBSERVICE & FILTERXML (Part 1)
Real-Time Weather Conditions with WEBSERVICE & FILTERXML (Part 2)
Bonus Excel Functions

1 Topic
BONUS LESSON

  Write a Review

Microsoft Excel: Advanced Excel Formulas & Functions

Go to Paid Course