Formula Library

Browse 50+ common spreadsheet formulas with examples and explanations.

SUM

Add up a range of numbers

=SUM(A1:A100)

AVERAGE

Calculate the average of a range

=AVERAGE(B1:B50)

IF

Return different values based on a condition

=IF(A1>100,"Yes","No")

VLOOKUP

Look up a value in a table by matching the first column

=VLOOKUP(A1,Sheet2!A:D,3,FALSE)

SUMIF

Sum values that meet a single condition

=SUMIF(A1:A100,"Completed",B1:B100)

COUNTIF

Count cells that meet a condition

=COUNTIF(A1:A100,"Yes")

INDEX/MATCH

Flexible lookup - more powerful than VLOOKUP

=INDEX(C1:C100,MATCH(E1,A1:A100,0))

CONCATENATE / TEXTJOIN

Join text from multiple cells

=TEXTJOIN(" ",TRUE,A1,B1,C1)

SUMIFS

Sum values meeting multiple conditions

=SUMIFS(C1:C100,A1:A100,"East",B1:B100,">1000")

COUNTIFS

Count cells meeting multiple conditions

=COUNTIFS(A1:A100,"Active",B1:B100,">30")

LEFT / RIGHT / MID

Extract part of a text string

IFS

Check multiple conditions (cleaner than nested IF)

=IFS(A1>=90,"A",A1>=80,"B",A1>=70,"C",TRUE,"F")

XLOOKUP

Modern replacement for VLOOKUP (Excel 365)

=XLOOKUP(E1,A1:A100,C1:C100,"Not Found")

TODAY / NOW

Get current date or date/time

DATEDIF

Calculate difference between two dates

=DATEDIF(A1,B1,"D")

ROUND

Round a number to specified decimal places

MAX / MIN

Find the largest or smallest value in a range

=MAX(A1:A100)

LEN

Count the number of characters in text

TRIM

Remove extra spaces from text

IFERROR

Show a custom value instead of an error

=IFERROR(A1/B1,"N/A")

AVERAGEIF

Average values meeting a condition

=AVERAGEIF(A1:A100,"East",B1:B100)

UNIQUE

Return unique values from a range

=UNIQUE(A1:A100)

FILTER

Return rows matching criteria (dynamic filter)

=FILTER(A1:D100,B1:B100>50)

SORT

Sort a range dynamically

=SORT(A1:D100,2,-1)

TEXT

Format a number as text with a specific format

=TEXT(A1,"$#,##0.00")

UPPER / LOWER / PROPER

Change text case

SUBSTITUTE

Replace specific text within a string

=SUBSTITUTE(A1,"old","new")

HLOOKUP

Horizontal lookup - search across a row

=HLOOKUP("Q1",A1:D2,2,FALSE)

AND / OR

Check if multiple conditions are true

=IF(AND(A1>50,B1="Active"),"Yes","No")

RANK

Rank a value within a range

=RANK(A1,$A$1:$A$100,0)

MEDIAN

Find the middle value of a dataset

=MEDIAN(A1:A100)

STDEV

Calculate standard deviation

=STDEV(A1:A100)

NETWORKDAYS

Count business days between two dates

=NETWORKDAYS(A1,B1)

EDATE

Add or subtract months from a date

YEAR / MONTH / DAY

Extract year, month, or day from a date

PMT

Calculate loan payment amount

=PMT(0.05/12,360,250000)

FV

Calculate future value of an investment

=FV(0.07/12,120,-500)

PERCENTILE

Find the value at a given percentile

=PERCENTILE(A1:A100,0.9)

INDIRECT

Reference a cell using a text string

=INDIRECT("A"&B1)

SEARCH / FIND

Find the position of text within a string

=SEARCH("word",A1)

COUNTA

Count non-empty cells in a range

=COUNTA(A1:A100)

ARRAYFORMULA

Apply a formula to an entire column (Google Sheets)

=ARRAYFORMULA(A1:A100*B1:B100)Sheets

QUERY

SQL-like queries on spreadsheet data (Google Sheets)

=QUERY(A1:D100,"SELECT A,C WHERE B>50 ORDER BY C DESC")Sheets

IMPORTRANGE

Import data from another Google Sheet

=IMPORTRANGE("spreadsheet_url","Sheet1!A1:D100")Sheets

CHOOSE

Return value based on index number

=CHOOSE(A1,"Jan","Feb","Mar","Apr")

SWITCH

Match value against list of cases

=SWITCH(A1,"A","Excellent","B","Good","C","Fair","Unknown")

OFFSET

Reference a cell offset from a starting point

=OFFSET(A1,5,2)

PRODUCT

Multiply all numbers in a range

=PRODUCT(A1:A10)

ABS

Return absolute (positive) value of a number

SPARKLINE

Create inline mini chart (Google Sheets)

=SPARKLINE(A1:L1,{"charttype","line"})Sheets