Spreadsheet formulas, explained

Clear guides with syntax, a worked example and formulas you can copy. Works in Excel and Google Sheets. Free, no sign-up.

Not sure which one you need? Use the free formula finder

Lookup

XLOOKUP

Find a value in one column and return the matching value from another column.

VLOOKUP

Look up a value in the first column of a table and return a value from a column to the right.

INDEX MATCH

A flexible lookup that works in every Excel version and can look left.

UNIQUE / FILTER

List unique values, or return every row that meets a condition.

Math

SUMIFS

Add up numbers that meet one or more conditions.

SUMIF

Add up numbers that meet a single condition.

ROUND

Round a number to a set number of decimals.

Percentage change

Calculate growth or decline between two numbers as a percentage.

Logic

Return one value if a condition is true and another if it is false.

IFS

Test several conditions in order and return the first one that is true.

AND / OR

Combine conditions: AND needs all to be true, OR needs at least one.

IFERROR

Show a clean value instead of an error like #N/A or #DIV/0!.

Statistics

COUNTIFS

Count rows that meet one or more conditions.

COUNTIF

Count cells that meet a single condition.

AVERAGEIFS

Average numbers that meet one or more conditions.

Text

TEXTJOIN

Join text from several cells with a separator.

LEFT / RIGHT / MID

Extract characters from the start, end or middle of text.

Date

DATEDIF

Calculate the difference between two dates in days, months or years.

EOMONTH

Get the last day of a month, a number of months before or after a date.

NETWORKDAYS

Count working days between two dates, skipping weekends and holidays.

Finance

PMT

Calculate the monthly payment for a loan with a fixed rate.

NPV / IRR

Value future cash flows today (NPV) and find their rate of return (IRR).