Excel: Advanced Formulas and Functions

Introduction

• Use the most powerful formulas and functions in Excel

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• Get data from remote cells with the OFFSET function
• Return references with the INDIRECT function
• Use INDIRECT with Data Validation for multitiered pick lists

• 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

• Extract information with the CELL and INFO functions
• Explore various information functions
• Use several error-checking functions
• Track and highlight formula cells with ISFORMULA

• Next steps