Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

# Excel: Advanced Formulas and Functions

## Overview

Take advantage of the most powerful features in Microsoft Excel. Learn how to use advanced formulas and functions, including lookup, statistical, text, and math functions.

Follow along with Excel expert Dennis Taylor as he demystifies the hundreds of formulas and functions available in Excel. Dennis starts with a few critical formula shortcuts that will speed up your work, then covers a variety of functions, such as VLOOKUP, MATCH, and INDEX, statistical functions, text functions, and date and time, math, text, and information functions. Dennis provides practical examples to help viewers easily transition to using Excel's most powerful formulas and functions in real-world scenarios. Note that this course is recorded in Excel for Office 365 but anyone using a recent versionâincluding 2019, 2016, and 2013âwill be able to follow along.

## Syllabus

Introduction
• Use the most powerful formulas and functions in Excel
1. Formula and Function Tips and Shortcuts
• Display and highlight formulas
• Use the auditing tools
• Use entire row/column references
• Change formulas to values and update values without formulas
• Simplify debugging formulas with the F9 key
• Enhance readability with range names
• Create 3D formulas to tabulate data from multiple sheets
2. IF and Related Functions
• Explore IF logical tests and use relational operators
• Create and expand the use of nested IF statements
• Create compound logical tests with AND, OR, NOT, and IF
• Use IFS for multiple conditions
3. Lookup and Reference Functions
• Explore the VLOOKUP and HLOOKUP functions
• Find approximate matches with VLOOKUP and HLOOKUP
• Use VLOOKUP to find exact matches and search large tables
• Find table-like data within a function using CHOOSE
• Use the SWITCH function for formula-embedded selection
• Locate data with the MATCH function
• Retrieve information by location with the INDEX function
• Use the MATCH and INDEX functions together
• Document formulas with the FORMULATEXT function
• Extract and count unique entries from a list with UNIQUE
• Use the XLOOKUP function
4. Power Functions
• Tabulate data using a single criterion with COUNTIF, SUMIF, and AVERAGEIF
• Tabulate data using multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS
• Use MAXIFS and MINIFS
• Use the SUBTOTAL function to prevent double counting
5. Statistical Functions
• Find middle and most common values with MEDIAN and MODE
• Rank data without sorting using RANK and RANK.EQ
• Find the largest and smallest values with LARGE and SMALL
• Tabulate blank cells with the COUNTBLANK function
• Use COUNT, COUNTA, and the status bar
6. Math Functions
• Work with the ROUND, ROUNDUP, and ROUNDDOWN functions
• Use MROUND, CEILING, and FLOOR for specialized rounding
• Use INT, TRUNC, ODD, and EVEN for specialized rounding
• Use MOD to find remainders and apply conditional formatting
• Explore practical uses for RAND, RANDARRAY, and RANDBETWEEN
• Convert a value between measurement systems with CONVERT
• Use the AGGREGATE function to bypass errors and hidden data
• Use ROMAN and ARABIC to display different number systems
7. Date and Time Functions
• Understand Excel date and time capabilities in formulas
• Use various date and time functions
• Use the TODAY and NOW functions for date and time entry
• Identify weekdays with the WEEKDAY function
• Count working days and completion dates (NETWORKDAYS and WORKDAY)
• Tabulate date differences with the DATEDIF function
• Calculate dates with EDATE and EOMONTH
8. Reference Functions
• Get data from remote cells with the OFFSET function
• Return references with the INDIRECT function
• Use INDIRECT with Data Validation for multitiered pick lists
9. Text Functions
• Locate and extract data with FIND, SEARCH, and MID
• Extract data with the LEFT and RIGHT functions
• Use the TRIM function to remove unwanted spaces in a cell
• Combine data with symbols (&) and CONCATENATE
• Use CONCAT and TEXTJOIN to combine data from different cells
• Adjust alphabetic case with UPPER, LOWER, and PROPER
• Adjust character content with REPLACE and SUBSTITUTE
• Use utility text functions: TEXT, REPT, VALUE, and LEN
• Use the new LET function
10. Information Functions
• Extract information with the CELL and INFO functions
• Explore various information functions
• Use several error-checking functions
• Track and highlight formula cells with ISFORMULA
Conclusion
• Next steps

Dennis Taylor