Excel: Advanced Formulas and Functions

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
• Create custom functions with LAMBDA
• New functions: TEXTBEFORE, TEXTAFTER, and TEXTSPLIT

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