Self-Paced Course
Excel Formulas & Functions
Master 75+ formulas & functions, and learn how to transform basic Excel worksheets into dynamic and powerful analytics tools.
Not Sure Which COURSE or PATH is right for you?
Take our customized survey to find the perfect courses & paths for your skill level and goals!
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
- Introduction
- Anatomy of the IF Statement
- Nested IF Statements
- AND/OR Operators
- NOT vs. <>
- Fixing Errors with IFERROR
- Common IS Statements
- QUIZ: 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
- QUIZ: 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
- Fuzzy Match Lookups
- The INDEX Function
- The MATCH Function
- Using INDEX & MATCH Together
- Combining MATCH with VLOOKUP
- UPDATE: VLOOKUP Correction
- The OFFSET Function
- Combining OFFSET with COUNTA
- DEMO: Using OFFSET to Create a Scrolling Chart
- QUIZ: 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
- 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
- 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
- Introduction
- 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
- Introduction
- The INDIRECT Function
- The HYPERLINK Function
- WEBSERVICE & FILTERXML: Weather Underground
- WEBSERVICE & FILTERXML: Open Weather Map
- QUIZ: Bonus Functions
- FINAL ASSESSMENT
- 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
Are you ready to become a DATA ROCKSTAR? Start learning today with your FREE 7-Day trial!
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?
Sign up today for a 7-Day Free Trial and start learning today!