Formula recipes

Formulas for real jobs

Start from the job you need done. Each recipe gives the Excel and Google Sheets versions, with a short explanation.

Excel functions (530)Sheets functions (515)Formula recipes (12)Compare

Want more? Get 100 formulas in one PDF.

Download 100 Excel Formulas

Two-way lookup by row and columnLookup

Excel=XLOOKUP(G2,A2:A10,XLOOKUP(G3,B1:E1,B2:E10))

Sheets=XLOOKUP(G2,A2:A10,XLOOKUP(G3,B1:E1,B2:E10))

The inner XLOOKUP picks the column for G3. The outer one picks the row for G2.

Sum values between two datesSum

Excel=SUMIFS(C:C,A:A,">="&F1,A:A,"<="&F2)

Sheets=SUMIFS(C:C,A:A,">="&F1,A:A,"<="&F2)

Two tests on the date column set the start and end. The & joins each operator with its date cell.

Count unique valuesCount

Excel=COUNTA(UNIQUE(A2:A100))

Sheets=COUNTUNIQUE(A2:A100)

UNIQUE removes repeats and COUNTA counts what is left. Keep the range free of blanks in Excel, or add FILTER first.

Return the last value in a columnLookup

Excel=XLOOKUP(TRUE,A2:A100<>"",A2:A100,,,-1)

Sheets=INDEX(FILTER(A2:A100,A2:A100<>""),COUNTA(A2:A100))

The Excel version searches upward for the first non-blank cell. The Sheets version keeps non-blank cells and picks the last one.

Split a full name into two cellsText

Excel=TEXTSPLIT(A2," ")

Sheets=SPLIT(A2," ")

The space marks each break. Results spill to the right, so keep those cells empty.

Create a sorted list with no duplicatesDynamic arrays

Excel=SORT(UNIQUE(A2:A100))

Sheets=SORT(UNIQUE(A2:A100))

UNIQUE removes repeats and SORT orders the result. The list updates with the source.

Count workdays with a Friday and Saturday weekendDates

Excel=NETWORKDAYS.INTL(A2,B2,7,Holidays)

Sheets=NETWORKDAYS.INTL(A2,B2,7,Holidays)

Weekend code 7 means Friday and Saturday. Code 1 means Saturday and Sunday. Holidays is a named range of dates.

Calculate age in yearsDates

Excel=DATEDIF(A2,TODAY(),"Y")

Sheets=DATEDIF(A2,TODAY(),"Y")

DATEDIF counts complete years from the birth date in A2 to today.

Build a running totalSum

Excel=SUM($B$2:B2)

Sheets=SUM($B$2:B2)

The first reference stays fixed and the second grows as you fill the formula down.

Extract the domain from an email addressText

Excel=TEXTAFTER(A2,"@")

Sheets=REGEXEXTRACT(A2,"@(.+)$")

Both return the text after the @ sign.

Total sales by category with one formulaSummaries

Excel=GROUPBY(A2:A100,C2:C100,SUM)

Sheets=QUERY(A1:C100,"select A, sum(C) group by A",1)

Both return a live summary with one row per category.

Show the top five rows by valueDynamic arrays

Excel=TAKE(SORT(A2:B100,2,-1),5)

Sheets=SORTN(A2:B100,5,0,2,FALSE)

Both sort by the second column from high to low and keep five rows.