← All cheat sheets

Excel & Google Sheets formulas

Formulas that work the same in Excel and Google Sheets. Click one to copy it, then change the cell ranges to match your sheet.

Math

: Add up a range
: Average of a range
: Smallest value (MAX for the largest)
: Round to 2 decimals (ROUNDUP, ROUNDDOWN too)
: Multiply two cells (+ − / ^ work too)
: Divide; format the cell as % for a percentage
: Multiply pairs and add them up, e.g. quantity × price
: Middle value

Counting

: Count cells with numbers
: Count cells that aren’t empty
: Count empty cells
: Count cells equal to a value
: Count cells over 100
: Count rows matching several conditions

Conditions

: One result if true, another if false
: All conditions must be true (OR: any of them)
: Several conditions in order
: Add up the values where another column matches
: Add up with several conditions
: Average where another column matches
: Show 0 (or anything) instead of an error

Looking up values

: Find E2 in column A and return the value from column C
: Same, with a message when nothing matches
: Older way: return the 3rd column of the first exact match
: Lookup that works in every version
: Position of a value in a range
: All rows that match a condition
: List each value once
: Sort by the 3rd column, largest first

Text

: Join text (first and last name)
: Join a range with a separator, skipping blanks
: First 3 characters (RIGHT for the last ones)
: 2 characters starting at the 4th
: Number of characters
: Remove extra spaces
: CAPITALS (LOWER, PROPER for Title Case)
: Replace text, e.g. remove dashes
: Show a number in a fixed format
: Split text into columns (Google Sheets; Excel: TEXTSPLIT)

Dates

: Today’s date (NOW() includes the time)
: Make a date from year, month and day
: Year of a date (MONTH, DAY too)
: Days between two dates
: Whole years between dates, e.g. age
: Working days between two dates
: Same day 3 months later
: Last day of the month
: Day of the week, Monday = 1

Cell references

: Changes when the formula is copied
: Stays fixed when copied (press F4 to switch)
: Column fixed, row changes
: Row fixed, column changes
: A cell on another sheet
: A sheet whose name has spaces
: A whole column

Some regions use ; instead of , between arguments, e.g. =IF(A2>0; "Yes"; "No").

Something not working?