0. Introduction
01 - Use the most powerful formulas and functions in Excel
1. Formula and Function Tips and Shortcuts
02 - Display and highlight formulas
03 - Use the auditing tools
04 - Use entire row column references
05 - Change formulas to values and update values without formulas
06 - Simplify debugging formulas with the F9 key
07 - Enhance readability with range names
08 - Create 3D formulas to tabulate data from multiple sheets
2. IF and Related Functions
09 - Explore IF logical tests and use relational operators
10 - Create and expand the use of nested IF statements
11 - Create compound logical tests with AND, OR, NOT, and IF
12 - Use IFS for multiple conditions
3. Lookup and Reference Functions
13 - Explore the VLOOKUP and HLOOKUP functions
14 - Find approximate matches with VLOOKUP and HLOOKUP
15 - Use VLOOKUP to find exact matches and search large tables
16 - Find table-like data within a function using CHOOSE
17 - Use the SWITCH function for formula-embedded selection
18 - Locate data with the MATCH function
19 - Retrieve information by location with the INDEX function
20 - Use the MATCH and INDEX functions together
21 - Document formulas with the FORMULATEXT function
22 - Extract and count unique entries from a list with UNIQUE
23 - Use the XLOOKUP function
4. Power Functions
24 - Tabulate data using a single criterion with COUNTIF, SUMIF, and AVERAGEIF
25 - Tabulate data using multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS
26 - Use MAXIFS and MINIFS
27 - Use the SUBTOTAL function to prevent double counting
5. Statistical Functions
28 - Find middle and most common values with MEDIAN and MODE
29 - Rank data without sorting using RANK and RANK.EQ
30 - Find the largest and smallest values with LARGE and SMALL
31 - Tabulate blank cells with the COUNTBLANK function
32 - Use COUNT, COUNTA, and the status bar
6. Math Functions
33 - Work with the ROUND, ROUNDUP, and ROUNDDOWN functions
34 - Use MROUND, CEILING, and FLOOR for specialized rounding
35 - Use INT, TRUNC, ODD, and EVEN for specialized rounding
36 - Use MOD to find remainders and apply conditional formatting
37 - Explore practical uses for RAND, RANDARRAY, and RANDBETWEEN
38 - Convert a value between measurement systems with CONVERT
39 - Use the AGGREGATE function to bypass errors and hidden data
40 - Use ROMAN and ARABIC to display different number systems
7. Date and Time Functions
41 - Understand Excel date and time capabilities in formulas
42 - Use various date and time functions
43 - Use the TODAY and NOW functions for date and time entry
44 - Identify weekdays with the WEEKDAY function
45 - Count working days and completion dates (NETWORKDAYS and WORKDAY)
46 - Tabulate date differences with the DATEDIF function
47 - Calculate dates with EDATE and EOMONTH
8. Reference Functions
48 - Get data from remote cells with the OFFSET function
49 - Return references with the INDIRECT function
50 - Use INDIRECT with Data Validation for multitiered pick lists
9. Text Functions
51 - Locate and extract data with FIND, SEARCH, and MID
52 - Extract data with the LEFT and RIGHT functions
53 - Use the TRIM function to remove unwanted spaces in a cell
54 - Combine data with symbols (&) and CONCATENATE
55 - Use CONCAT and TEXTJOIN to combine data from different cells
56 - Adjust alphabetic case with UPPER, LOWER, and PROPER
57 - Adjust character content with REPLACE and SUBSTITUTE
58 - Use utility text functions - TEXT, REPT, VALUE, and LEN
59 - Use the new LET function
60 - Create custom functions with LAMBDA
61 - New functions - TEXTBEFORE, TEXTAFTER, and TEXTSPLIT
10. Information Functions
62 - Extract information with the CELL and INFO functions
63 - Explore various information functions
64 - Use several error-checking functions
65 - Track and highlight formula cells with ISFORMULA
Conclusion
66 - Next steps