Self-Paced Course
Excel Formulas & Functions
Master 75+ formulas & functions, and learn how to transform basic Excel worksheets into dynamic and powerful analytics tools.
Course Description
This course is all about using formulas & functions to transform basic Excel worksheets into dynamic and powerful analytics tools.
We'll cover 75+ core functions, using hands-on demos designed to showcase why they are important and how they can be applied in a variety of ways. This isn't about memorizing syntax; it's about learning how to THINK like Excel.
By the end of the course, you'll have the skills to build interactive models and dashboards, automate tedious and time-consuming tasks, and explore your data using functions like VLOOKUP, INDEX/MATCH, OFFSET, SUMPRODUCT, and more. Whether you're looking to brush up on the basics or develop expert-level formula skills, you've come to the right place.
Course Outline
- Welcome to the Course!
- Benchmark Assessment
- Course Structure & Outline
- DOWNLOAD: Course Resources
- Setting Expectations
- 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
- Congrats, You're a Developer!
- QUIZ: Formulas 101
- HOMEWORK: Formulas 101
- Introduction
- Anatomy of the IF Statement
- Nested IF Statements
- AND/OR Operators
- NOT vs. <>
- Fixing Errors with IFERROR
- Common IS Statements
- QUIZ: Logical Operators
- HOMEWORK: Logical Operators
- Introduction
- Basic Statistical Functions
- SMALL/LARGE & RANK/PERCENTRANK
- RAND() & RANDBETWEEN
- The SUMPRODUCT Function
- SUMPRODUCT Demo
- COUNTIFS/SUMIFS/AVERAGEIFS
- DEMO: Building a Basic Dashboard
- Combining COUNTIF & SUMPRODUCT
- PRO TIP: Data Profiling with COUNT & SUMPRODUCT
- QUIZ: Statistical Functions
- HOMEWORK: Statistical Functions
- Introduction
- Working with Named Arrays
- ROW/ROWS & COLUMN/COLUMNS
- VLOOKUP/HLOOKUP
- Joining Data with VLOOKUP
- Fixing Errors with IFERROR & VLOOKUP
- VLOOKUP Reference Array Options
- Approximate Match Lookups
- The INDEX Function
- The MATCH Function
- Using INDEX & MATCH Together
- Combining MATCH with VLOOKUP
- UPDATE: VLOOKUP Correction
- SNEAK PEEK: XLOOKUP (Excel 365 Only)
- DEMO: Building Dashboards with XLOOKUP
- Selecting List Items With CHOOSE
- The OFFSET Function
- Combining OFFSET with COUNTA
- DEMO: Using OFFSET to Create a Scrolling Chart
- QUIZ: Lookup/Reference Functions
- HOMEWORK: Lookup/Reference Functions
- Introduction
- UPPER/LOWER/PROPER & TRIM
- The CONCATENATE Function (&)
- LEFT/MID/RIGHT & LEN
- TEXT/VALUE
- SEARCH/FIND
- Categorizing Data with IF(ISNUMBER(SEARCH))
- Combining RIGHT, LEN, and SEARCH
- SUBSTITUTE
- QUIZ: Text Functions
- HOMEWORK: Text Functions
- Introduction
- 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: Designing a Budget Pacing Tool
- QUIZ: Date & Time Functions
- HOMEWORK: Date & Time Functions
- Introduction
- Creating, Editing & Managing Rules
- Highlighting Every Other Row Using MOD
- Formatting Cells Based on the Value of Another Cell
- Formatting Cells Using Statistical Functions
- Formatting Cells Using Text & Logical Operators
- QUIZ: Formula-Based Formatting
- HOMEWORK: Formula-Based Formatting
- Introduction
- READ ME: Array Formulas are Changing!
- Rules of Array Functions
- Pros & Cons of Array Functions
- Vertical, Horizontal & 2-D Array Constants
- Using Array Constants in Formulas
- Named Array Constants
- The Transpose Function
- Linking Data Between Sheets: Array vs. Non-Array
- Returning the X Largest Values in a Range
- Counting Characters Across Cells
- Creating a MAX IF Array Formula
- MAX IF with Multiple Criteria
- Using the Double Unary Operator
- QUIZ: Array Formulas
- HOMEOWORK: Array Formulas
- Introduction
- The INDIRECT Function
- The HYPERLINK Function
- WEBSERVICE & FILTERXML: Weather Underground
- WEBSERVICE & FILTERXML: Open Weather Map
- QUIZ: Bonus Functions
- Final Assessment
- Course Feedback Survey
- Share the love!
- Next Steps
WHO SHOULD TAKE THIS COURSE?
Analysts looking to extract insight from raw data, and develop expert-level formula skills
Excel users who want to build interactive models or dashboards (forecasts, financial models, scenario tests, etc)
Anyone seeking to improve productivity and work more efficiently with data
WHAT ARE THE COURSE REQUIREMENTS?
Microsoft Excel 2007+ (some features may not be available in all versions)
Basic familiarity with Excel formulas recommended, but not required
Start learning for FREE, no credit card required!
Every subscription includes access to the following course materials
- Interactive Project files
- Downloadable e-books
- Graded quizzes and assessments
- 1-on-1 Expert support
- 100% satisfaction guarantee
- Verified credentials & accredited badges
Ready to become a
data rockstar?
Start learning for free, no credit card required!