Free

Excel & Sheets Formula Cheat Sheet

The formulas analysts reach for — syntax, a worked example, and what each is actually for.

Lookup & reference

XLOOKUP

=XLOOKUP(lookup, lookup_range, return_range, [if_not_found])

The modern replacement for VLOOKUP — looks left or right, no column counting, built-in not-found handling.

=XLOOKUP("Ava", A:A, C:C, "Not found")

VLOOKUP

=VLOOKUP(lookup, table, col_index, FALSE)

Finds a value in the first column and returns a value from another column. Always pass FALSE for an exact match.

=VLOOKUP(A2, Sheet2!A:D, 4, FALSE)

INDEX + MATCH

=INDEX(return_range, MATCH(lookup, lookup_range, 0))

The flexible lookup: works in any direction and doesn't break when columns are inserted.

=INDEX(C:C, MATCH(A2, B:B, 0))

IFERROR

=IFERROR(value, value_if_error)

Catches #N/A and other errors and shows something readable instead.

=IFERROR(VLOOKUP(A2, D:E, 2, FALSE), "—")

Aggregation & counting

SUMIFS

=SUMIFS(sum_range, crit_range1, crit1, ...)

Sums rows that meet multiple conditions — the workhorse of spreadsheet analysis.

=SUMIFS(C:C, A:A, "UK", B:B, ">100")

COUNTIFS

=COUNTIFS(crit_range1, crit1, ...)

Counts rows meeting several conditions at once.

=COUNTIFS(A:A, "Active", B:B, ">=2026-01-01")

AVERAGEIFS

=AVERAGEIFS(avg_range, crit_range1, crit1, ...)

Averages only the rows that match your conditions.

=AVERAGEIFS(C:C, A:A, "Product")

SUMPRODUCT

=SUMPRODUCT(range1, range2)

Multiplies ranges element-wise and sums the result — great for weighted totals.

=SUMPRODUCT(B2:B10, C2:C10)

Text & cleaning

TEXTSPLIT

=TEXTSPLIT(text, delimiter)

Splits one cell into several by a delimiter — the fastest way to break up a messy column.

=TEXTSPLIT(A2, ",")

TRIM

=TRIM(text)

Removes stray leading, trailing and repeated spaces — the number-one cause of failed lookups.

=TRIM(A2)

CONCAT / TEXTJOIN

=TEXTJOIN(delimiter, ignore_empty, range)

Joins several cells into one, skipping blanks if you ask it to.

=TEXTJOIN(", ", TRUE, A2:C2)

SUBSTITUTE

=SUBSTITUTE(text, old, new)

Replaces text — handy for stripping symbols before converting to a number.

=SUBSTITUTE(A2, "$", "")

Logic & dates

IF

=IF(condition, value_if_true, value_if_false)

The basic branch. Nest sparingly — reach for IFS when you have many conditions.

=IF(C2>100, "High", "Low")

IFS

=IFS(cond1, val1, cond2, val2, ...)

Cleaner than nested IFs. End with TRUE for the catch-all case.

=IFS(C2>=90,"A", C2>=80,"B", TRUE,"C")

DATEDIF

=DATEDIF(start, end, unit)

Difference between two dates in days, months or years.

=DATEDIF(A2, B2, "M")

EOMONTH

=EOMONTH(date, months)

Returns the last day of a month — the basis for monthly bucketing.

=EOMONTH(A2, 0)

Works the same in Excel and Google Sheets unless noted. Newer functions (XLOOKUP, TEXTSPLIT) need a recent version.

The handful that do most of the work

If you learn five things well, make them XLOOKUP (or INDEX/MATCH), SUMIFS, COUNTIFS, TRIM and IF/IFS. Between them they cover joining data, conditional aggregation, cleaning and branching — which is 80% of real spreadsheet analysis.

When to stop using spreadsheets

When the same clean-and-aggregate runs every week, or the file gets slow, it is time to move that logic into SQL. The formulas here map neatly onto SQL: SUMIFS is a filtered SUM with GROUP BY, XLOOKUP is a JOIN. Learning both makes you far more employable than mastering either alone.

Other free tools

Spreadsheets to SQL to a new job

Real analyst roles scraped daily from company career pages.

Browse analyst jobs