Google Sheets Formulas Cheat Sheet

Sum all values in range A1 to A10

Sum specific cells

Sum multiple ranges

Average of range A1 to A10

Average of specific cells

Average of entire column A

Count numeric cells in range

Count numeric cells in entire column

Count specific numeric cells

Count cells greater than 100

Count cells equal to 'Apple'

Count non-empty cells

Sum values greater than 100

Sum B values where A contains 'Apple'

Sum B where A equals A12 value

Sum C where A='Apple' AND B>100

Sum D for North region in Q1

Sum C where A contains 'Pro' and B>0

Average C where A='A' and B>50

Average D for North region in 2024

Largest value in range

Largest value across multiple ranges

Return A1 if positive, otherwise 0

Smallest value in range

Smallest value across multiple ranges

Middle value in a sorted dataset

Returns 3

Returns 3.14

Round to nearest integer

Round to nearest 100 → 1200

Returns 3

Returns 7.5

Returns 20

Returns 4

Returns 6.5

Returns 25

Returns 1 (remainder of 10÷3)

Check if A1 is odd (returns 1) or even (0)

Alternate 1 and 0 for striped row logic

Largest value (same as MAX)

Second largest value

Third largest value

Smallest value (same as MIN)

Second smallest value

Rank of A1 in descending order

Rank of A1 in ascending order

Rank with absolute reference

Join first and last name with space

Add greeting around a name

Alternative using & operator

Join A1:A5 with commas, skip blanks

Join with dash, include blanks

Concatenate all non-empty values

Split A1 by comma into multiple columns

Split by space → Hello | World

Split by the entire delimiter string

First 3 characters of A1

Extract first word

Returns 'Hell'

Last 4 characters of A1

Extract last word

Returns '2024'

5 characters starting from position 3

Returns 'World'

Extract text after a dash

Character count of A1

Length after trimming whitespace

Count commas in a string

Clean up spaces in A1

Returns 'Hello World'

Clean and lowercase text

Replace all 'Old' with 'New'

Remove all spaces

Replace only the first 'a'

Find position of @ in email

Find position of first space

Find second period

Extract first number from text

Extract uppercase letters

Extract 4-digit year

Check if A1 is all digits

Validate basic email pattern

Case-insensitive match for 'yes'

Replace numbers with #

Collapse multiple spaces

Wrap each word in brackets

Label values above 100 as High

Show 'Empty' for blank cells

Nested IF for grades

Grade A/B/C/F from score

Map AM/PM to labels

Check if A1 is between 0 and 100

Multi-condition IF

Check positive number

Check for multiple valid inputs

Handle blank or zero

Either A1 or B1 exceeds 100

Check if A1 is not blank

Equivalent to A1<>B1

Invert condition in IF

Return 0 instead of #DIV/0!

Handle missing VLOOKUP result

Default to 0 if A1 isn't a number

Handle missing lookup

Handle failed MATCH

Map month number to name

Map code to color

TRUE if exactly one of A1,B1 is positive

Returns TRUE

Returns FALSE (both true = even count)

Current date

Days since date in A1

Date 30 days from now

Current date and time

Time elapsed since A1 timestamp

Display only current time

Create Dec 31, 2024

First day of next month

Build date from separate columns

Age in complete years

Months between two dates

Days between two dates

3 months after date in A1

Same day last month

1 year after A1

Last day of current month

Last day of next month from A1

First day of current month

Business days between A1 and B1

Working days to deadline, excluding holidays

5 business days from today

3 business days before A1

10 business days, excluding holidays

Day of week (1=Sun to 7=Sat by default)

Type 2: 1=Mon to 7=Sun

Classify as weekday or weekend

Current ISO week number

Week number of Jan 1, 2024

Find A1 in column B, return column C

Look up 'Smith' in A, return column C

Safe VLOOKUP with fallback

Find 'Q1' in row 1, return row 3

Lookup year in header row

Value at row 3, column 2 of A1:C10

Paired with MATCH for flexible lookup

Random value from range

Find position of 'Apple' in A1:A10

INDEX-MATCH combo for lookup

Row of maximum value

Look up A1 in B, return from C

Custom text if not found

Exact match, search from last

Position of 'Apple' (exact match)

Next larger value position

Wildcard match

Cell 2 rows down, 1 column right from A1

Sum 5 rows starting at A1

Last non-empty cell in column A

Reference cell A[value in B1]

Sum dynamic range from text

Reference another sheet dynamically

Import A1:D10 from another sheet

URL in A1, import column B

Return day name from number

Today's day name

Multiply columns element-wise

Apply formula to entire column

Conditional sum with array logic

Select columns where C>100

Sum by group, sorted

Dynamic filter using cell value

Rows where column B > 50

Multiple conditions (AND)

OR condition using addition

Unique values from column A

Unique rows across three columns

Sorted unique values

Sort by first column ascending

Sort by second column descending

Sort by column 1 then 2

Current Google stock price

Apple trading volume

GBP to USD exchange rate

Translate A1 to French

Auto-detect source, translate to Spanish

Translate 'Hello' to Japanese

Detect language of A1 (returns 'en', 'fr', etc.)

Auto-translate to English

Import CSV from web URL

Import CSV from URL stored in A1

Import first table from Wikipedia

Import second list from URL in A1

Import BBC news headlines

Extract all H1 headings from a page

Insert image, fit to cell

Insert image, stretch to cell size

Custom height 50, width 80 pixels

Line sparkline for the range

Bar chart sparkline

Blue column sparkline

Monthly payment on £10k loan, 5% annual, 5 years

Dynamic loan calculator

PV of 120 monthly payments of £1500 at 8%

PV of £10k received in 10 years at 5%

FV of £200/month for 10 years at 6%

FV of £1000 in 5 years at 5% annual

NPV of future cash flows minus initial investment

NPV of three yearly cash flows at 8%

IRR where A1 is initial investment (negative)

IRR for given cash flow series

Annual rate on £10k loan, £200/month, 5 years

Dynamic annual rate calculation

Months to repay £10k at 5% annual with £200/month

Years to reach £1m saving £2k/year at 7%

TRUE if A1 is empty

Show 'No data' for empty cells

Count blanks (alternative approach)

TRUE if division causes an error

Replace errors with 0

Check if lookup fails

TRUE if A1 contains a number

Conditional calculation

Check if 'error' appears in A1

TRUE if A1 is text

Uppercase only text cells

Returns TRUE

Returns TRUE (expression is logical)

Return type code for A1

Returns 2 (text)

Returns 1 (number)

Returns 1

Returns 0

Returns 0 (text cannot convert)

Returns 10

Returns 255

Returns '1010'

Returns '11111111' (8 digits)

Returns 255

Returns 26

Miles to kilometres

Celsius to Fahrenheit

Pounds to kilograms

Returns 8 (1100 AND 1010 = 1000)

Mask to extract lower 4 bits

Returns 14 (1100 OR 1010 = 1110)

Returns 12

Returns 6 (1100 XOR 1010 = 0110)

Returns 240

Sum Sales column where F criteria match

Sum 3rd column matching criteria

Average Score where criteria match

Count numeric Amount values matching criteria

Max Revenue for matching rows

Minimum Cost for matching rows

Get Email for the single matching row