Module 1 Before We Dive In
Unit 1 Course Structure & Outline
Unit 2 Resource Files and Datasets
Unit 3 BONUS: Extra Homework Exercises
Module 2 Excel Formulas 101
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
Module 3 Conditional Statements & Logical Operators
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
Module 4 Statistical Functions
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
Module 5 Lookup/Reference 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
Module 6 Text 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
Module 7 Date & Time 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
Module 8 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 The Transpose Function
Unit 7 Linking Data Between Sheets: Array vs. Non-Array Comparison
Unit 8 Returning the "X" Largest Values in a Range
Unit 9 Counting Characters Across Cells
Unit 10 Creating a "MAX IF" Array Formula
Unit 11 BONUS: "MAX IF" with Multiple Criteria
Unit 12 BONUS: Using the Double Unary Operator ("--")
Unit 13 HOMEWORK: Array Functions
Module 9 Array Formulas
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
Module 10 Badass Bonus Functions
Unit 1 Conclusion & Next Steps
Powered By WP Courseware

Microsoft Excel – Advanced Excel Formulas & Functions

take-this-course

Course Description

It's time to show Excel who's boss. Whether you're starting from square one or aspiring to become an absolute Excel badass, you've come to the right place.

This course will give you a deep understanding of the advanced formulas and functions that transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. While most Excel courses focus on simply what each formula does, I teach through hands-on, contextual examples designed to showcase why these formulas are awesome and how they can be applied in a number of ways. I will not train you to regurgitate functions and formula syntax; I will teach you how to THINK like Excel.

By the end of the course you'll be writing robust, elegant formulas and functions from scratch, allowing you to:

  • Easily build dynamic tools & Excel dashboards to filter, display and analyze your data
  • Go rogue and design your own formula-based formatting rules
  • Join datasets from multiple sources in seconds with lookup, index & match functions
  • Pull real-time data from APIs directly into Excel (weather, stock quotes, directions, etc.)
  • Manipulate dates, times, text, and arrays
  • Automate tedious and time-consuming analytics tasks using cell formulas and functions in Excel (no VBA required!)

We'll dive into a broad range of Excel formulas & functions, including:

  • Lookup/Reference functions
  • Statistical functions
  • Formula based formatting
  • Date & Time functions
  • Logical operators
  • Array formulas
  • Text functions
  • Powerful analytics tools and formulas like INDIRECT, HYPERLINK, WEBSERVICE & FILTERXML