Chapter 1 : Formula & Function Tips & Shortcuts
Display and highlight formulas05:00
Use the auditing tools06:18
Use entire row/column references03:01
Change formulas to values and update values without formulas05:55
Simplify debugging formulas with the F9 key04:07
Enhance readability with range names05:10
Create 3D formulas to tabulate data from multiple sheets05:11
Chapter 1 Quiz : Formula & Function Tips & Shortcuts
Chapter 2: If & Related Functions
Explore IF logical tests and use relational operators04:36
Create and expand the use of nested IF statements04:44
Create compound logical tests with AND, OR, NOT, and IF05:28
Use IFS for multiple conditions02:50
Chapter 2 Quiz: If & Related Functions
Chapter 3: Lookup & Reference Functions
Explore the VLOOKUP and HLOOKUP functions03:41
Find approximate matches with VLOOKUP and HLOOKUP05:02
Use VLOOKUP to find exact matches and search large tables05:05
Find table-like data within a function using CHOOSE04:03
Use the SWITCH function for formula-embedded selection04:45
Locate data with the MATCH function03:28
Retrieve information by location with the INDEX function02:47
Use the MATCH and INDEX functions together04:03
Document formulas with the FORMULATEXT function02:37
Extract and count unique entries from a list with UNIQUE04:28
Use the XLOOKUP function05:07
Chapter 3 Quiz: Lookup & Reference Functions
Chapter 4: Power Functions
Tabulate data using a single criterion with COUNTIF, SUMIF, and AVERAGEIF04:10
Tabulate data using multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIF04:05
Use MAXIFS and MINIFS02:34
Use the SUBTOTAL function to prevent double counting04:58
Chapter 4 Quiz: Power Functions
Chapter 5: Statistical Functions
Find middle and most common values with MEDIAN and MODE02:55
Rank data without sorting using RANK and RANK.EQ04:07
Find the largest and smallest values with LARGE and SMALL02:05
Tabulate blank cells with the COUNTBLANK function04:57
Use COUNT, COUNTA, and the status bar03:41
Chapter 5 Quiz: Statistical Functions
Chapter 6: Math Functions
Work with the ROUND, ROUNDUP, and ROUNDDOWN functions05:43
Use MROUND, CEILING, and FLOOR for specialized rounding03:10
Use INT, TRUNC, ODD, and EVEN for specialized rounding03:19
Use MOD to find remainders and apply conditional formatting05:08
Explore practical uses for RAND, RANDARRAY, and RANDBETWEEN05:36
Convert a value between measurement systems with CONVERT03:14
Use the AGGREGATE function to bypass errors and hidden data04:53
Use ROMAN and ARABIC to display different number systems03:17
Chapter 6 Quiz: Math Functions
Chapter 7: Date & Time Functions
Understand Excel date and time capabilities in formulas04:49
Use various date and time functions04:43
Use the TODAY and NOW functions for date and time entry05:35
Identify weekdays with the WEEKDAY function03:57
Count working days and completion dates (NETWORKDAYS and WORKDAY)04:06
Tabulate date differences with the DATEDIF function05:53
Calculate dates with EDATE and EOMONTH05:17
Chapter 7 Quiz: Date & Time Functions
Chapter 8: Reference Functions
Get data from remote cells with the OFFSET function04:08
Return references with the INDIRECT function03:53
Use INDIRECT with Data Validation for multitiered pick lists05:23
Chapter 8 Quiz: Reference Functions
Chapter 9: Text Functions
Locate and extract data with FIND, SEARCH, and MID05:11
Extract data with the LEFT and RIGHT functions03:42
Use the TRIM function to remove unwanted spaces in a cell04:48
Combine data with symbols (&) and CONCATENATE03:28
Use CONCAT and TEXTJOIN to combine data from different cells05:04
Adjust alphabetic case with UPPER, LOWER, and PROPER04:26
Adjust character content with REPLACE and SUBSTITUTE05:17
Use utility text functions: TEXT, REPT, VALUE, and LEN05:26
Use the new LET function05:23
Create custom functions with LAMBDA05:50
New functions: TEXTBEFORE, TEXTAFTER, and TEXTSPLIT06:18
Chapter 9 Quiz: Text Functions
Chapter 10: Information Functions
Extract information with the CELL and INFO functions04:52
Explore various information functions04:36
Use several error-checking functions05:18
Track and highlight formula cells with ISFORMULA03:32
Next steps
Next steps00:34
Use the most powerful formulas and functions in Excel
