Excel: Advanced Formulas and Functions (2023)

Course details

Follow along with Excel MVP Oz du Soleil as he demystifies the many kinds of formulas and functions available in Excel. Oz starts with a few critical keyboard shortcuts that will speed up your work, and offers grounding in how to develop your own style working with formulas and functions. Next, Oz covers a variety of formulas such as the XLOOKUP/VLOOKUP and INDEX functions, counting and statistical functions, text functions, and date/time, array, math, and information functions. Oz provides practical examples to help viewers easily transition to using Excel's most powerful formulas and functions in real-world scenarios. The course concludes with a series of challenges so you can test your knowledge.

Concepts

Introduction

Learning advanced formulas and functions using Excel
Developing your own style with formulas and functions
Challenges

Using Tables and Dynamic Arrays for Data Integrity and Consistency

Tables
Tables and absolute cell references
Dynamic arrays introduction

The World of IF Statements and Conditions

IF function
SUMIFS and COUNTIFS
MAXIFS, MINIFS, and AVERAGEIFS

Looking Up, Down, and All Around - Comparing and Combining with Lookups

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

Formula Tips and Strategies

Use Alt+Enter to make formulas more readable
Formula vs. lookup table
Formula vs. helper columns
Build complex formulas in steps
Writing formulas for future you
Compatibility functions
Writing 3D formulas
Volatile functions
LET function overview
Error handling - IFNA and IFERROR

Mid-Term Challenges

Challenge 1 - Course completions
Challenge 2 - Two-way lookup
Challenge 3 - Guitars

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
RANDARRY

Statistical Functions

LARGE and SMALL
MEDIAN and MODE
FACT
COMBIN COMBINA

Math Functions

Rounding
MROUND, CEILING, and FLOOR
MOD

Wild Cards

Wildcards
XLOOKUP with wildcards

New, Handy, and Fun Functions

ROMAN and ARABIC
IMAGE
CHAR and CODE

Final Challenges

Challenge 1 - Towers
Challenge 2 - Donations
Challenge 3 - Assignment
Challenge 4 - Course order

Conclusion

Take your Excel skills to the next level