Excel Cheat Sheet

1 The basics

• Every formula starts with =.
• Relative A1 shifts when copied; absolute $A$1 stays fixed. F4 toggles the $.
• Ranges: A1:A20. Whole column: A:A.
• Turn data into a Table (Ctrl+T) — formulas fill down automatically and ranges grow with your data.

=B2*C2            # qty × price
=B2*$F$1          # × a fixed tax cell

2 Everyday maths

3 Lookups — the big one

# modern, best: XLOOKUP
=XLOOKUP(A2, Products[Name],
              Products[Price])

# classic: VLOOKUP (exact match)
=VLOOKUP(A2, D:F, 3, FALSE)

# flexible: INDEX + MATCH
=INDEX(F:F, MATCH(A2, D:D, 0))

Use XLOOKUP if you have it — it looks left or right and takes an "if not found" argument: XLOOKUP(A2, names, prices, "not found").

4 Logic — IF & friends

=IF(B2>100, "premium", "standard")

=IFS(B2>100,"A", B2>50,"B", TRUE,"C")

=IF(AND(B2>50, C2="paid"), "ok", "hold")
=IF(OR(B2>100, C2="vip"), "yes", "no")

=IFERROR(A2/B2, 0)   # catch #DIV/0!

Wrap risky formulas (lookups, division) in IFERROR so a blank cell doesn't spray #N/A across your report.

5 Conditional sums & counts

=SUMIF(D:D, "paid", B:B)
=COUNTIF(D:D, "paid")
=AVERAGEIF(D:D, "paid", B:B)

# multiple conditions
=SUMIFS(B:B, D:D,"paid", E:E,">=2026-01-01")
=COUNTIFS(D:D,"paid", F:F,"KL")

These answer most business questions — "how much did we bill, paid, in KL, this year?" — without a pivot table.

6 Text

=TRIM(PROPER(A2)) & " — " & B2

7 Dates

=TODAY()            # today's date
=NOW()              # date + time
=YEAR(A2)  =MONTH(A2)  =DAY(A2)
=A2 - B2            # days between
=EDATE(A2, 3)       # +3 months
=EOMONTH(A2, 0)     # month-end
=TEXT(A2, "yyyy-mm-dd")

Dates are just numbers underneath (day 1 = 1900-01-01). That's why you can subtract them.

8 Pivot tables

• Select your data → Insert ▸ PivotTable.
• Drag fields into Rows, Columns, Values, Filters.
• Values default to SUM/COUNT — click to change to Average, Max, % of total.
• Right-click ▸ Refresh after the data changes.
• Group dates by month/quarter; add a Slicer for click-to-filter.

The fastest way to summarise thousands of rows without a single formula.

9 Clean messy data

• TRIM + CLEAN strip spaces and junk characters.
• Data ▸ Text to Columns splits "Name, City" into two.
• Remove Duplicates (Data tab) in one click.
• =VALUE(A2) turns "123" text into a real number.
• Flash Fill (Ctrl+E) — type one example, Excel infers the rest.

10 Shortcuts worth memorising