Excel cheat sheet: formulas, operators and shortcuts

Math and totals

SUM(range)

Add up numbers

AVERAGE(range)

Mean

MIN(range) / MAX(range)

Smallest / largest

ROUND(n, digits)

Round the stored value

ROUNDUP / ROUNDDOWN(n, digits)

Always up / always down

MOD(n, divisor)

Remainder

SUMPRODUCT(a, b)

Multiply pairs, then add

SUBTOTAL(9, range)

Sum of visible rows only

Count and conditional

COUNT(range)

Cells with numbers

COUNTA(range)

Cells that are not empty

COUNTBLANK(range)

Empty cells

COUNTIF(range, criteria)

Count matches

COUNTIFS(r1, c1, r2, c2)

Count, several conditions

SUMIF(range, criteria, sum_range)

Add matching rows

SUMIFS(sum_range, r1, c1, ...)

Add, several conditions

AVERAGEIFS / MAXIFS / MINIFS

Same pattern as SUMIFS (MAXIFS: 2019+)

Logic

IF(test, if_true, if_false)

Pick one of two results

IFS(t1, v1, t2, v2, ...)

First true test wins (2019+)

AND(t1, t2) / OR(t1, t2)

All true / any true

NOT(test)

Flip TRUE and FALSE

IFERROR(value, fallback)

Replace any error

IFNA(value, fallback)

Replace #N/A only

SWITCH(x, v1, r1, ..., default)

Match a value to a result (2019+)

Lookup

XLOOKUP(value, look_in, return, [not_found])

Any direction, exact by default (2021+)

VLOOKUP(value, table, col, FALSE)

Classic; FALSE = exact match

INDEX(range, MATCH(value, look_in, 0))

Works in every version

MATCH(value, range, 0)

Position of a value

XMATCH(value, range)

Modern MATCH (2021+)

Text

LEFT / RIGHT(text, n)

First / last n characters

MID(text, start, n)

Characters from the middle

LEN(text)

Number of characters

FIND / SEARCH(find, text)

Position; SEARCH ignores case

TRIM(text)

Remove extra spaces

UPPER / LOWER / PROPER(text)

Change case

SUBSTITUTE(text, old, new)

Replace text

TEXT(value, "format")

Number or date as formatted text

TEXTJOIN(", ", TRUE, range)

Join with a separator (2019+)

TEXTBEFORE / TEXTAFTER(text, "-")

Split at a delimiter (365, 2024)

VALUE(text)

Text to number

Dates

TODAY()

Today’s date (updates daily)

DATE(y, m, d)

Build a date

YEAR / MONTH / DAY(date)

Parts of a date

EDATE(date, months)

Same day, n months later

EOMONTH(date, 0)

Last day of the month

DATEDIF(start, end, "y")

Whole years ("m" months, "d" days)

NETWORKDAYS(start, end)

Working days between

WORKDAY(start, days)

Date after n working days

WEEKDAY(date, 2)

Day number, Monday = 1

Dynamic arrays (365, 2021+)

FILTER(range, condition, "None")

Matching rows

SORT(range, col, -1)

Sorted copy; -1 = descending

SORTBY(range, by_range)

Sort by another column

UNIQUE(range)

Each value once

SEQUENCE(rows)

1, 2, 3...

LET(name, value, calc)

Name a part of a formula

Operators

Order of operations

1. Brackets, innermost first
2. Negation (-2)
3. %
4. ^
5. * and /
6. + and -
7. &
8. = < > <= >= <>

So =-2^2 is 4 and =2^3^2 is 64.

Criteria and wildcards

References