Excel cheat sheet for data analysts

Line total (type in K2, fill down)

=F2*G2*(1-H2/100)

Relative references move as you fill: row 3 reads F3, G3, H3.

Lock the input cell with $

=G2*(1+$R$1)

Without $, filling down turns R1 into R2, R3 …, so every row after the first reads the wrong cell. F4 cycles $R$1 → R$1 → $R1 → R1.

Lock only the column or only the row

=$G2*R$1

$G2 keeps column G when filled right; R$1 keeps row 1 when filled down.

Share of the column total

=G2/SUM($G$2:$G$41)

Total, average, middle value

=SUM(F2:F41)
=AVERAGE(G2:G41)
=MEDIAN(G2:G41)

Smallest, largest, 3rd largest

=MIN(G2:G41)
=MAX(G2:G41)
=LARGE(G2:G41,3)

Count numbers, filled cells, blanks

=COUNT(G2:G41)
=COUNTA(I2:I41)
=COUNTBLANK(I2:I41)

COUNT counts numbers only; COUNTA counts anything that is not empty.

Round the result

=ROUND(AVERAGE(G2:G41),2)
=ROUNDUP(F2/4,0)

ROUND changes the value; a number format only changes how it looks.

Multiply two columns and add up

=SUMPRODUCT(F2:F41,G2:G41)

Two outcomes

=IF(G2>=$R$2,"high","low")

Several bands, first match wins

=IFS(G2<50,"under 50",G2<200,"50-199",TRUE,"200+")

The final TRUE is the catch-all. Without it, a value no test matches gives #N/A.

All conditions true

=IF(AND(C2="web",G2>200),"review","")

Any condition true

=IF(OR(D2="cancelled",D2="refunded"),"lost","kept")

Count rows that match

=COUNTIF(C2:C41,"web")
=COUNTIFS(C2:C41,"web",D2:D41,"completed")

Sum or average where rows match

=SUMIFS(F2:F41,C2:C41,"app",D2:D41,"completed")
=AVERAGEIFS(G2:G41,C2:C41,"web")

SUMIFS takes the column to add first, then range/criterion pairs.

Comparisons and "not equal" in criteria

=COUNTIFS(G2:G41,">=75",D2:D41,"<>cancelled")

Criterion from a cell

=COUNTIFS(G2:G41,">="&R2)

Join the operator and the cell with &. ">=R2" would look for the text R2.

Largest value where rows match

=MAXIFS(G2:G41,C2:C41,"marketplace")

A channel × status table from one formula (in U5)

=COUNTIFS($C$2:$C$41,$T5,$D$2:$D$41,U$4)

Channels down T5:T7, statuses across U4:V4. Type in U5 and fill right and down: $C/$D stay put, $T5 keeps the column, U$4 keeps the row.

XLOOKUP (Excel 365, Google Sheets)

=XLOOKUP(E2,$L$2:$L$121,$M$2:$M$121,"not found")

Exact match by default; the 4th argument replaces #N/A.

INDEX / MATCH (every version)

=INDEX($N$2:$N$121,MATCH(E2,$L$2:$L$121,0))

The 0 in MATCH means exact match. Leave it out and you get the nearest smaller key.

VLOOKUP, exact match

=VLOOKUP(E2,$L$2:$O$121,3,FALSE)

Without FALSE, VLOOKUP does an approximate match and returns wrong rows on unsorted data. It also breaks when a column is inserted.

Catch a missing key

=IFNA(INDEX($M$2:$M$121,MATCH(E2,$L$2:$L$121,0)),"missing")

Remove stray spaces, fix case

=TRIM(I2)
=PROPER(TRIM(I2))
=UPPER(C2)

Text before and after a character

=LEFT(J2,FIND("@",J2)-1)
=MID(J2,FIND("@",J2)+1,100)

Join text

=C2&" / "&D2
=TEXTJOIN(", ",TRUE,C2,D2,I2)

TEXTJOIN with TRUE skips empty cells, so a missing city leaves no ", ," gap.

Replace part of a text, length

=SUBSTITUTE(J2,"@"," at ")
=LEN(TRIM(I2))

Text that looks like a number

=VALUE("42.50")
=ISNUMBER(G2)

Numbers stored as text sit left-aligned and SUM ignores them. VALUE converts.

Parts of a date

=YEAR(B2)
=MONTH(B2)
=WEEKDAY(B2,2)

WEEKDAY with 2 numbers Monday as 1 and Sunday as 7.

A month key for grouping

=TEXT(B2,"yyyy-mm")

TEXT returns text, which groups and sorts well but no longer does date maths.

End of month, next month

=EOMONTH(B2,0)
=EDATE(B2,1)

Days between two dates

=DATE(2025,12,31)-B2
=DATEDIF(B2,DATE(2025,12,31),"m")

Dates are numbers (days), so subtraction gives days. DATEDIF gives whole months or years.

Count rows in a date range

=COUNTIFS(B2:B41,">="&DATE(2025,1,1),B2:B41,"<"&DATE(2025,2,1))

Fill a blank with a label

=IF(ISBLANK(I2),"unknown",TRIM(I2))

Replace an error with a value

=IFERROR(G2/F2,0)

IFERROR hides every error, typos included. Use IFNA when only "not found" is expected.

Flag duplicates

=COUNTIF($J$2:$J$41,J2)>1

TRUE on every copy, the first one included.

Keep the first copy only

=COUNTIF($J$2:J2,J2)=1

The range grows as you fill down, so only the first time a value appears is TRUE.

Flag impossible values

=IF(F2<=0,"check quantity","")