📚 Table of Contents
1-10: Basic Calculations
11-20: Logical Functions
21-30: Lookup & Reference
31-40: Text Functions
41-50: Date & Advanced
Basic Calculations (1-10)
1
SUM
Beginner
=SUM(A1:A10)
Adds up all numbers in a range. The most fundamental Excel function.
Example:
=SUM(B2:B50) adds all sales from row 2 to 50
2
AVERAGE
Beginner
=AVERAGE(A1:A10)
Calculates the arithmetic mean of numbers. Ignores text and blank cells.
Example:
=AVERAGE(C2:C100) finds the average test score
3
COUNT
Beginner
=COUNT(A1:A10)
Counts how many cells contain numbers. Useful for finding data points.
Example:
=COUNT(D2:D1000) counts how many sales entries exist
4
MAX
Beginner
=MAX(A1:A10)
Returns the largest number in a range.
Example:
=MAX(E2:E500) finds the highest sales amount
5
MIN
Beginner
=MIN(A1:A10)
Returns the smallest number in a range.
Example:
=MIN(F2:F500) finds the lowest price
6
COUNTA
Beginner
=COUNTA(A1:A10)
Counts non-empty cells. Works with text and numbers.
Example:
=COUNTA(A2:A1000) counts how many names are in the list
7
ROUND
Beginner
=ROUND(A1, 2)
Rounds a number to specified decimal places. Second argument is number of decimals.
Example:
=ROUND(G2, 2) rounds 15.6789 to 15.68
8
SUMIF
Intermediate
=SUMIF(range, criteria, sum_range)
Adds numbers that meet a specific condition.
Example:
=SUMIF(A2:A100, "Apple", B2:B100) sums sales only for Apple products
9
AVERAGEIF
Intermediate
=AVERAGEIF(range, criteria, average_range)
Calculates average of cells that meet a condition.
Example:
=AVERAGEIF(C2:C100, ">50", D2:D100) averages scores above 50
10
COUNTIF
Intermediate
=COUNTIF(range, criteria)
Counts cells that meet a specific condition.
Example:
=COUNTIF(E2:E500, "Complete") counts completed tasks
Logical Functions (11-20)
11
IF
Beginner
=IF(condition, value_if_true, value_if_false)
The most powerful decision-making function. Returns different values based on whether a condition is true or false.
Example:
=IF(A2>100, "High", "Low") labels values above 100 as "High"
12
AND
Intermediate
=AND(condition1, condition2, ...)
Returns TRUE only if ALL conditions are true.
Example:
=IF(AND(B2>50, C2="Yes"), "Qualified", "Not Qualified")
13
OR
Intermediate
=OR(condition1, condition2, ...)
Returns TRUE if ANY condition is true.
Example:
=IF(OR(D2="Urgent", E2>1000), "Priority", "Normal")
14
NOT
Intermediate
=NOT(condition)
Reverses the logic. Turns TRUE to FALSE and vice versa.
Example:
=IF(NOT(F2=""), "Has Value", "Empty")
15
IFERROR
Intermediate
=IFERROR(formula, value_if_error)
Catches errors and displays a custom message instead.
Example:
=IFERROR(A2/B2, "Cannot divide") prevents #DIV/0! errors
16
ISBLANK
Beginner
=ISBLANK(cell)
Checks if a cell is empty. Returns TRUE if blank.
Example:
=IF(ISBLANK(G2), "Missing", "Present")
17
IFS
Advanced
=IFS(condition1, value1, condition2, value2, ...)
Tests multiple conditions without nesting IFs. Excel 2016+.
Example:
=IFS(A2<60, "F", A2<70, "D", A2<80, "C", A2<90, "B", A2<=100, "A")
18
SWITCH
Advanced
=SWITCH(value, match1, result1, match2, result2, ...)
Matches a value against multiple options. Cleaner than nested IFs for exact matches.
Example:
=SWITCH(B2, 1, "Jan", 2, "Feb", 3, "Mar", "Unknown")
19
SUMIFS
Advanced
=SUMIFS(sum_range, criteria_range1, criteria1, ...)
Sums based on multiple conditions. More powerful than SUMIF.
Example:
=SUMIFS(D2:D100, A2:A100, "Apple", B2:B100, ">50") sums Apple sales over 50
20
COUNTIFS
Advanced
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)
Counts cells that meet multiple conditions.
Example:
=COUNTIFS(C2:C100, ">=18", D2:D100, "Female") counts adult females
Lookup & Reference (21-30)
21
VLOOKUP
Intermediate
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Looks up a value in the first column and returns a value from a specified column. The most popular lookup function.
Example:
=VLOOKUP(A2, PriceTable, 3, FALSE) finds product price from a table
Tip: Always use FALSE for exact matches to avoid unexpected results.
22
HLOOKUP
Intermediate
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Horizontal lookup. Searches in the first row and returns from a specified row below.
Example:
=HLOOKUP("March", A1:M5, 3, FALSE) finds March data from row 3
23
XLOOKUP
Advanced
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Modern replacement for VLOOKUP. More flexible and powerful. Excel 365 only.
Example:
=XLOOKUP(A2, Names, Prices, "Not Found") can search any direction
24
INDEX
Intermediate
=INDEX(array, row_num, [col_num])
Returns a value from a specific position in a range.
Example:
=INDEX(A1:C10, 5, 2) returns value from row 5, column 2 of the range
25
MATCH
Intermediate
=MATCH(lookup_value, lookup_array, [match_type])
Finds the position of a value in a range. Often combined with INDEX.
Example:
=MATCH("Apple", A1:A100, 0) returns the row number where "Apple" appears
26
INDEX-MATCH
Advanced
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
More powerful than VLOOKUP. Can look left and is more flexible.
Example:
=INDEX(C2:C100, MATCH(E2, A2:A100, 0)) looks up any column direction
27
OFFSET
Advanced
=OFFSET(reference, rows, cols, [height], [width])
Returns a reference to a range offset from a starting cell.
Example:
=SUM(OFFSET(A1, 0, 0, 5, 1)) sums 5 cells starting from A1
28
INDIRECT
Advanced
=INDIRECT(text_reference)
Converts text into a cell reference. Useful for dynamic references.
Example:
=INDIRECT("A"&ROW()) references cell A with current row number
29
CHOOSE
Intermediate
=CHOOSE(index_num, value1, value2, ...)
Returns a value from a list based on position number.
Example:
=CHOOSE(2, "Red", "Blue", "Green") returns "Blue"
30
ROW / COLUMN
Beginner
=ROW([reference]) or =COLUMN([reference])
Returns the row or column number of a reference.
Example:
=ROW() returns the current row number. =COLUMN(C1) returns 3
Text Functions (31-40)
31
CONCATENATE / CONCAT
Beginner
=CONCAT(text1, text2, ...) or =A1&" "&B1
Joins multiple text strings together. & operator is simpler.
Example:
=A2&" "&B2 combines first and last name with space
32
LEFT
Beginner
=LEFT(text, num_chars)
Extracts specified number of characters from the start of text.
Example:
=LEFT(A2, 5) extracts first 5 characters from cell A2
33
RIGHT
Beginner
=RIGHT(text, num_chars)
Extracts specified number of characters from the end of text.
Example:
=RIGHT(A2, 4) extracts last 4 characters (like year from date string)
34
MID
Intermediate
=MID(text, start_num, num_chars)
Extracts characters from the middle of text starting at a position.
Example:
=MID(A2, 6, 3) extracts 3 characters starting from position 6
35
LEN
Beginner
=LEN(text)
Returns the number of characters in text.
Example:
=LEN(A2) counts characters in cell A2
36
UPPER / LOWER / PROPER
Beginner
=UPPER(text) / =LOWER(text) / =PROPER(text)
Converts text to uppercase, lowercase, or proper case (title case).
Example:
=PROPER("john smith") returns "John Smith"
37
TRIM
Beginner
=TRIM(text)
Removes extra spaces from text, leaving only single spaces between words.
Example:
=TRIM(A2) cleans up messy imported data with extra spaces
38
SUBSTITUTE
Intermediate
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Replaces specific text with new text. Case-sensitive.
Example:
=SUBSTITUTE(A2, "Street", "St") replaces "Street" with "St"
39
FIND / SEARCH
Intermediate
=FIND(find_text, within_text, [start_num])
Finds position of text within another text. FIND is case-sensitive, SEARCH is not.
Example:
=FIND("@", A2) finds position of @ symbol in email
40
TEXTJOIN
Advanced
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Joins multiple text strings with a delimiter. Excel 2016+.
Example:
=TEXTJOIN(", ", TRUE, A2:A10) joins all names with commas
Date & Advanced Functions (41-50)
41
TODAY / NOW
Beginner
=TODAY() / =NOW()
Returns current date (TODAY) or current date and time (NOW). Updates automatically.
Example:
=TODAY() returns today's date. =NOW() includes time.
42
DATE
Beginner
=DATE(year, month, day)
Creates a date from separate year, month, and day values.
Example:
=DATE(2025, 10, 1) creates the date October 1, 2025
43
YEAR / MONTH / DAY
Beginner
=YEAR(date) / =MONTH(date) / =DAY(date)
Extracts the year, month, or day from a date.
Example:
=YEAR(A2) extracts year from date in A2
44
DATEDIF
Intermediate
=DATEDIF(start_date, end_date, unit)
Calculates difference between two dates. Units: "Y" (years), "M" (months), "D" (days).
Example:
=DATEDIF(A2, TODAY(), "Y") calculates age in years
45
WEEKDAY
Beginner
=WEEKDAY(date, [return_type])
Returns day of week as a number (1-7). Default: Sunday=1.
Example:
=WEEKDAY(A2) returns 1 for Sunday, 2 for Monday, etc.
46
EOMONTH
Intermediate
=EOMONTH(start_date, months)
Returns the last day of a month, a specified number of months before or after the start date.
Example:
=EOMONTH(A2, 0) returns last day of the month for date in A2
47
UNIQUE
Advanced
=UNIQUE(array, [by_col], [exactly_once])
Returns unique values from a range. Excel 365 only. Dynamic array formula.
Example:
=UNIQUE(A2:A100) returns list of unique names
48
FILTER
Advanced
=FILTER(array, include, [if_empty])
Filters a range based on criteria. Excel 365 only. Powerful dynamic array formula.
Example:
=FILTER(A2:C100, B2:B100>50, "No results") filters rows where column B > 50
49
SORT
Advanced
=SORT(array, [sort_index], [sort_order], [by_col])
Sorts data automatically. Excel 365 only. Dynamic array formula.
Example:
=SORT(A2:C100, 2, -1) sorts by column 2 in descending order
50
SEQUENCE
Advanced
=SEQUENCE(rows, [columns], [start], [step])
Generates sequence of numbers. Excel 365 only. Great for creating numbered lists automatically.
Example:
=SEQUENCE(10) creates numbers 1 through 10. =SEQUENCE(5, 1, 100, 10) creates 100, 110, 120, 130, 140
Pro Tips for Excel Mastery
🎯 Use F4 for Absolute References
Press F4 to toggle between $A$1, A$1, $A1, and A1 when editing formulas.
⚡ Master Ctrl+Enter
Select multiple cells, type formula, press Ctrl+Enter to apply to all at once.
🔍 Use Name Manager
Name important ranges (Formulas > Name Manager) to make formulas readable.
📊 Array Formulas
In Excel 365, dynamic arrays spill automatically. In older versions, use Ctrl+Shift+Enter.
💡 Debug with F9
Select part of formula, press F9 to evaluate just that portion.
🎨 Use Tables
Convert ranges to tables (Ctrl+T) for structured references and auto-expanding formulas.
50 Must-Know Excel Formulas
Master these formulas and transform your Excel skills
Created for data professionals, analysts, and Excel enthusiasts
Version 1.0 | 2025