ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
 EXCEL FORMULA MASTERY
3
The Data Analyst's Complete Reference & Training Workbook
4
5
6
 HOW TO USE THIS WORKBOOK
7
1. Click any formula name below to jump to its dedicated training sheet.
8
2. Each sheet: Syntax > Argument breakdown > Common mistakes > Live examples with dummy data.
9
3. Modify the dummy data and watch formulas update in real time.
10
4. Use the Back to Homepage link on each sheet to return here.
11
12
CATEGORY LEGEND
13
Lookup & Reference
 Logical Math & Aggregation Text Manipulation Date & Time
14
Statistical
 Dynamic Arrays
15
16
 FORMULA INDEX — Click any formula to start learning
17
#FormulaCategoryDifficultyDescription
18
1VLOOKUPLookup & Reference⭐⭐Search for a value in the first column and return from another column
19
2INDEX-MATCHLookup & Reference⭐⭐⭐Flexible lookup combo — search any direction, any column
20
3XLOOKUPLookup & Reference⭐⭐Modern VLOOKUP replacement with built-in error handling (365+)
21
4IF-IFSLogical⭐⭐Return different values based on one or more conditions
22
5SUMIFSMath & Aggregation⭐⭐Sum values meeting multiple criteria
23
6COUNTIFSMath & Aggregation⭐⭐Count cells meeting multiple criteria
24
7AVERAGEIFSMath & Aggregation⭐⭐Average values meeting multiple criteria
25
8IFERRORLogical⭐Trap and handle formula errors gracefully
26
9TEXT FunctionsText Manipulation⭐⭐LEFT, RIGHT, MID, LEN, TRIM, FIND, UPPER, LOWER, PROPER
27
10DATE FunctionsDate & Time⭐⭐YEAR, MONTH, EOMONTH, EDATE, NETWORKDAYS, TODAY
28
11SUMPRODUCTMath & Aggregation⭐⭐⭐Multiply arrays and sum — for weighted calcs & conditional logic
29
12TEXTJOINText Manipulation⭐⭐Join text with delimiter, skip blanks (365+)
30
13ROUND FunctionsMath & Aggregation⭐ROUND, ROUNDUP, ROUNDDOWN, MROUND for precision control
31
14RANK-LARGEStatistical⭐⭐RANK, LARGE, SMALL, PERCENTILE for ranking analysis
32
15AND-OR-NOTLogical⭐Combine multiple logical tests for complex conditions
33
16UNIQUE-SORTDynamic Arrays⭐⭐⭐UNIQUE, SORT, FILTER, SEQUENCE — dynamic spill functions (365+)
34
17IFSLogical⭐⭐Evaluate multiple conditions sequentially — cleaner than nested IFs (365+)
35
18TEXTBEFOREText Manipulation⭐⭐Extract text before a specified delimiter (365+)
36
19TEXTAFTERText Manipulation⭐⭐Extract text after a specified delimiter (365+)
37
20ISNUMBER-SEARCHLogical⭐⭐Check if a cell contains specific text — powerful pattern for partial matching
38
21FILTERDynamic Arrays⭐⭐⭐Extract rows matching conditions — dynamic array that replaces manual filtering (365+)
39
22Compare ListsLogical⭐⭐IF + COUNTIF combo to find matches, mismatches, and duplicates between two lists
40
41
 Each sheet includes live formulas — edit the dummy data and watch results update automatically!
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100