Excel: Advanced Formulas and Functions

Course details

This course with Excel MVP Oz du Soleil takes you beyond basic spreadsheet skills to harness the full analytical power of Microsoft Excel. Oz begins with essential keyboard shortcuts and formula development strategies that will dramatically increase your efficiency. You'll explore a comprehensive range of advanced functions including XLOOKUP/VLOOKUP, INDEX, and sophisticated counting, statistical, text, date/time, array, mathematical, and information functions. Each concept is explained through practical, real-world examples that demonstrate how these powerful tools solve complex data challenges. Learn how to develop a personal style for approaching formula creation as well as how to troubleshoot when formulas don't behave as expected. The course concludes with challenging exercises designed to test and reinforce your new skills, ensuring you can confidently apply advanced Excel functions in your professional work.

Learning objectives
Utilize keyboard shortcuts to increase Excel productivity.
Master lookup functions including XLOOKUP/VLOOKUP and INDEX/MATCH.
Apply advanced statistical and counting functions for data analysis.
Manipulate text data with specialized text functions.
Work with complex date and time calculations.
Create dynamic solutions using array formulas and functions.
Develop a systematic approach to formula creation and troubleshooting.

Concepts

Introduction

Learning advanced formulas and functions using Excel

Using Tables and Dynamic Arrays for Data Integrity and Consistency

Tables
Tables and absolute cell references
Introducing dynamic arrays

The World of IF Statements and Conditions

IF function
SUMIFS and COUNTIFS
MAXIFS, MINIFS, and AVERAGEIFS

Look Up, Down, and All Around - Compare and Combine with Lookups

VLOOKUP
XLOOKUP
Comparing VLOOKUP and XLOOKUP
INDEX MATCH
The INDEX MATCH vs. VLOOKUP controversy
Two-way lookups
Approximate or tiered matches
INDIRECT

Formula Tips and Strategies

Use ALT+ENTER to make formulas more readable
Formula vs. lookup table
Formula vs. helper columns
Developing your own style with formulas and functions
Build complex formulas in steps
Writing formulas for your future self
Compatibility functions
Writing 3D formulas
Volatile functions
LET function overview
Error handling - IFNA and IFERROR

Midterm Challenges

Introduction to the midterm challenges
Challenge 1 - Assignments
Challenge 2 - Guitars
Challenge 3 - Course completions

Date and Time Functions

Time, rounding, and converting to decimals
EOMONTH
YEARFRAC

Working with Text and Arrays

LEFT, RIGHT, and MID
UPPER, LOWER, and PROPER
TEXTJOIN
FILTER
UNIQUE
TOCOL
TEXTBEFORE and TEXTAFTER
RANDARRAY

Statistical Functions

LARGE and SMALL
MEDIAN and MODE
FACT
COMBIN and COMBINA

Math Functions

Rounding
MROUND, CEILING, and FLOOR
MOD

Wildcards

Wildcards
XLOOKUP with wildcards

New, Handy, and Fun Functions

ROMAN and ARABIC
FORMULATEXT
Images in functions
CHAR and CODE
ISEVEN

Final Challenges

Introduce the finals
Challenge 1 - Towers
Challenge 2 - Donations
Challenge 3 - Assignment
Challenge 4 - Course order
Challenge 5 - Home selection

Conclusion

A word about AI tools
Take your Excel skills to the next level