Excel guide · 3 min read

Excel formulas cheat sheet: the 25 everyone should know

These 25 formulas cover most everyday Excel work: SUM, AVERAGE, MIN/MAX, COUNT, COUNTA and ROUND for math; IF, AND, OR, IFS and IFERROR for logic; COUNTIF, COUNTIFS, SUMIF and SUMIFS for conditional totals; XLOOKUP, VLOOKUP and INDEX/MATCH for lookups; TRIM, LEFT/RIGHT/MID and TEXTJOIN for text; and TODAY, DATEDIF, UNIQUE and FILTER for dates and lists.

Sample data

Every formula on this page was calculated live against this sheet.

	A	B	C	D
1	Employee	Dept	Salary	Rating
2	Priya Shah	Sales	62000	4
3	Marcus Bell	IT	78000	3
4	Elena Ruiz	Sales	55000	5
5	Tom Fischer	Finance	69000	4
6	Aisha Khan	IT	83500	5
7	Jonah Price	Sales	48000	2

Math and counting (1-6)

1. SUM(C2:C7) adds a range. 2. AVERAGE(C2:C7) gives the mean. 3. MIN and MAX return the smallest and largest value. 4. COUNT counts cells with numbers. 5. COUNTA counts cells that are not empty, including text. 6. ROUND(number, digits) rounds properly; formatting only changes what you see, ROUND changes the value.

Formulas nest, so you can round an average in one step.

fx=ROUND(AVERAGE(C2:C7),0)

Result: 65917

The average salary is 65,916.67, rounded to 65,917.

Logic (7-11)

7. IF(test, if_true, if_false) picks between two results. 8. AND(...) is TRUE when every test is true. 9. OR(...) is TRUE when any test is true. 10. IFS(test1, value1, test2, value2, ...) handles several bands without nesting (Excel 2019 and later). 11. IFERROR(formula, fallback) shows a fallback instead of an error.

Here IF and AND flag strong performers who are paid below 70,000.

fx=IF(AND(D2>=4,C2<70000),"Raise review","-")

Result: Raise review

Priya has a rating of 4 and a salary of 62,000, so she is flagged for a raise review.

Conditional counts and sums (12-15)

12. COUNTIF(range, criteria) counts matching cells, such as COUNTIF(B2:B7,"Sales"). 13. COUNTIFS counts rows matching several conditions. 14. SUMIF(range, criteria, sum_range) adds matching values. 15. SUMIFS(sum_range, range1, criteria1, ...) adds with several conditions; note the sum range comes first.

fx=SUMIFS(C2:C7,B2:B7,"Sales",D2:D7,">=4")

Result: 117000

Salaries of Sales staff rated 4 or above: Priya and Elena, 117,000 in total.

Lookups (16-18)

16. XLOOKUP(value, lookup_range, return_range, if_not_found) finds a value and returns a match from any column (Excel 365 and 2021). 17. VLOOKUP(value, table, col_num, FALSE) is the classic version; it only looks right and needs FALSE for an exact match. 18. INDEX(return_range, MATCH(value, lookup_range, 0)) works in every version and in any direction.

fx=XLOOKUP("Aisha Khan",A2:A7,C2:C7,"Not found")

Result: 83500

XLOOKUP finds Aisha Khan and returns her salary: 83,500.

Text (19-21)

19. TRIM(text) removes extra spaces, the top cause of lookups failing. 20. LEFT, RIGHT and MID pull characters from the start, end or middle; pair them with FIND to split at a space or dash. 21. TEXTJOIN(", ", TRUE, range) joins cells with a separator and can skip blanks. (CONCAT and the & operator join without a separator.)

fx=LEFT(A2,FIND(" ",A2)-1)

Result: Priya

FIND locates the space in "Priya Shah", and LEFT returns everything before it: Priya.

Dates and dynamic arrays (22-25)

22. TODAY() returns the current date and updates every day. 23. DATEDIF(start, end, "y") gives whole years between two dates, useful for age or tenure; use "m" for months or "d" for days. 24. UNIQUE(range) lists each value once. 25. FILTER(range, condition) returns every row that matches. UNIQUE and FILTER spill into the cells below and need Excel 365 or 2021.

fx=FILTER(A2:A7,B2:B7="Sales")

Result: Priya Shah spills 3×1

Priya Shah

Elena Ruiz

Jonah Price

FILTER spills the three Sales employees: Priya Shah, Elena Ruiz and Jonah Price.

Common mistakes

Forgetting to lock ranges before copying

Use $ (press F4) on lookup tables and fixed cells, such as $A$2:$C$7, so they do not shift when you fill down.

Leaving out FALSE in VLOOKUP

Without it VLOOKUP does an approximate match and can quietly return the wrong row.

Typing numbers as text

Numbers with units or leading spaces are ignored by SUM and break lookups. Keep number cells pure and use number formats for units.

Using new functions in shared files

XLOOKUP, IFS, UNIQUE and FILTER show #NAME? in older Excel. Check what version your colleagues use.

FAQ

What are the most used Excel formulas?

SUM, AVERAGE, IF, COUNTIF, SUMIF and a lookup (XLOOKUP or VLOOKUP) cover most daily work. Learn those six first, then IFERROR and SUMIFS.

What is the difference between a formula and a function?

A formula is anything you type that starts with =. A function is a named, built-in calculation such as SUM or IF that you use inside a formula.

How do I see all formulas on a sheet?

Press Ctrl+` (the grave accent key, next to 1) or go to Formulas > Show Formulas. Press it again to go back to results.

Functions used

SUM Adds numbers
AVERAGE Mean of numbers
MIN Smallest number
MAX Largest number
COUNT Counts numbers
COUNTA Counts non-empty cells
ROUND Rounds to digits
IF Picks a result from a test
AND True if all tests are true
OR True if any test is true
IFS First true test wins
IFERROR Fallback when a formula errors
COUNTIF Counts cells that match
COUNTIFS Counts with several conditions
SUMIF Adds cells that match
SUMIFS Adds with several conditions
XLOOKUP Modern lookup in any direction
VLOOKUP Vertical lookup
INDEX Value at a position
MATCH Position of a value
TRIM Removes extra spaces
LEFT Characters from the start
RIGHT Characters from the end
MID Characters from the middle
FIND Position of text (case-sensitive)
TEXTJOIN Joins with a separator
CONCAT Joins text
TODAY Today’s date
DATEDIF Whole years, months or days between
UNIQUE Removes duplicates, spills
FILTER Keeps matching rows, spills

Keep learning

Updated 2026-10-31