20 Excel Formulas That Feel Like Cheat Codes

By the XLsheetAI Team · Updated August 9, 2026 · 10 min read

// tl;dr

Tiers 1–2 work in any Excel from 2016 onward. Tiers 3–4 need Microsoft 365 or Excel 2021+ — check before you build something a colleague can't open.
The biggest jump in "feels like magic" isn't a single function, it's LET and LAMBDA: they let you name and reuse logic instead of repeating it three times in one cell.
OFFSET and INDIRECT are powerful and volatile. Use them on purpose, not by default.
Most of these exist in Google Sheets too. The version table at the end tells you exactly which ones don't.

Most "Excel tips" lists are the same five functions everyone already knows, reordered. This one is organised by how much they change what you can do in one cell, from formulas that quietly replace three helper columns to ones that let you write your own custom function without a line of VBA. Every example below is tested syntax, not a paraphrase, and every version claim is checked, not guessed.

TIER 1 Everyday cheat codes

These five work in Excel 2016 and every version since, plus Google Sheets, so there's no compatibility excuse for not using them. Each one replaces a clumsier multi-formula habit with a single readable line, and none of them require dynamic arrays or a 365 subscription.

TEXTJOIN

Unlocks: combining a range of cells into one string with a delimiter, skipping blanks automatically.

=TEXTJOIN(", ", TRUE, A2:A6)

A2:A6 = Amy, Ben, "", Cara, Dan → "Amy, Ben, Cara, Dan"

IFS

Unlocks: replacing a nested IF pyramid with one flat list of condition/result pairs.

=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"D")

B2 = 84 → "B". Needs Excel 2019 or 365.

SWITCH

Unlocks: mapping one value to many exact outcomes without repeating the same comparison.

=SWITCH(B2,1,"Mon",2,"Tue",3,"Wed","?")

B2 = 2 → "Tue". Needs Excel 2019 or 365.

SUMPRODUCT

Unlocks: a multi-condition sum across arrays without an array-entered formula or SUMIFS' AND-only limits.

=SUMPRODUCT((A2:A9="North")*(B2:B9>100)*C2:C9)

Sums C where region is North AND value >100.

AGGREGATE

Unlocks: SUM, AVERAGE or MAX that silently ignores errors or hidden rows, in one function.

=AGGREGATE(9, 6, A2:A20)

9 = SUM, 6 = ignore errors. Excel-only, no Sheets equivalent.

"The gap between a beginner and a power user usually isn't knowledge of more functions — it's knowing which ONE function replaces the five they're already chaining together."

TIER 2 Power combos

Tier 2 is technique rather than novelty: still Excel 2016-compatible, but each one solves a problem that normally takes a workaround. Two of them, OFFSET and INDIRECT, are genuinely powerful and genuinely easy to misuse, so read the caution note before you build a model around them.

INDEX + MATCH×2

Unlocks: a true two-way lookup — find a value by matching both its row AND its column.

=INDEX(B2:F10, MATCH(H1,A2:A10,0), MATCH(H2,B1:F1,0))

H1 = row label, H2 = column label → the cell where they intersect.

OFFSET

Unlocks: a range that moves or resizes based on other cells, e.g. a rolling 12-month window.

=SUM(OFFSET(A1, 0, 0, COUNTA(A:A), 1))

⚠ Volatile: recalculates on almost every edit. Use sparingly on large files.

INDIRECT

Unlocks: building a cell or sheet reference from text, so a formula can point at a different tab by name.

=SUM(INDIRECT(A1 & "!B:B"))

A1 = "January" → sums column B on the "January" sheet. Also volatile.

CHOOSE

Unlocks: switching between several hardcoded scenarios by index, without an IF chain.

=CHOOSE(B1, "Optimistic", "Base case", "Downside")

B1 = 2 → "Base case". Great for scenario toggles.

RANDBETWEEN

Unlocks: instant realistic test data for a mock dataset, no plugin needed.

=RANDBETWEEN(1000, 5000)

A fresh whole number 1000–5000 every recalculation.

TIER 3 Dynamic array wizardry

Tier 3 needs Microsoft 365 or Excel 2021 and later — these will not calculate correctly, or will error out, in Excel 2019 or older. What makes them feel like cheat codes is that they spill: one formula in one cell produces a whole block of results that updates automatically as the source data changes.

UNIQUE

Unlocks: a live, deduplicated list that updates itself — no Remove Duplicates button, no stale copy.

=UNIQUE(A2:A100)

Spills every distinct value in A2:A100 downward automatically.

FILTER

Unlocks: pulling only the rows that match a condition into a new location, live.

=FILTER(A2:C100, B2:B100="North")

Every row where column B is "North", spilled in place.

SORT

Unlocks: a sorted copy of your data without touching the source, and it re-sorts on its own.

=SORT(A2:B20, 2, -1)

Sorts by column 2, descending. Pair with SORTBY to sort by a column not in the output.

SEQUENCE

Unlocks: generating a numbered list, calendar grid, or row of dates from one formula.

=SEQUENCE(12, 1, 1, 1)

Spills 1 through 12 down a column. Also in Google Sheets.

TEXTSPLIT

Unlocks: splitting one text string into multiple cells with a formula, live-updating unlike Text to Columns.

=TEXTSPLIT(A2, "-")

"XL-2041-IN" → three cells: XL, 2041, IN. Excel-only; Sheets uses SPLIT() instead.

TIER 4 365-exclusive sorcery

Tier 4 is Microsoft 365 only, full stop — these functions won't open in a perpetual-licence copy of Excel at all. This is also where the "feels illegal" reputation comes from: LET and LAMBDA let you write logic once and reuse it, which is closer to programming than spreadsheeting.

LET

Unlocks: naming a value or sub-calculation inside a formula so you compute it once and reuse it.

=LET(price, B2, qty, C2, price*qty*1.18)

Names price and qty, then uses both — no repeated B2*C2.

LAMBDA

Unlocks: writing your own reusable function with a name, no VBA and no add-in.

TaxedPrice = LAMBDA(price, price*1.18)

Define once in Name Manager, then call =TaxedPrice(B2) anywhere.

Spill operator (#)

Unlocks: referencing an entire dynamic array result without knowing how big it is.

=SUM(D2#)

Sums the whole spilled block starting at D2, whatever size it currently is.

GROUPBY

Unlocks: a pivot-table-style summary built with one formula instead of Insert > PivotTable.

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

Groups by column A, sums column C. Microsoft 365 only, no Sheets equivalent.

BYROW / MAP

Unlocks: applying your own LAMBDA to every row or every cell in a range, one formula, no fill-down.

=BYROW(A2:C10, LAMBDA(r, SUM(r)))

A row total for every row, spilled as a column — no dragging a formula down.

Bonus round: the conditional formatting trick nobody shows you

Conditional formatting normally colours one cell. The trick is a formula rule with a partly-locked reference, which highlights an entire row based on a single column's value — the kind of thing that looks like a hidden feature the first time you see it.

Select the whole data range, then Home → Conditional Formatting → New Rule → Use a formula, and enter a rule like =$D2="Overdue". The dollar sign locks column D while the row number stays relative, so every row checks its own D cell and the whole row lights up together. Apply the formatting, and you have a self-maintaining highlight rule instead of manually colouring rows by hand.

Version cheat sheet

Function	Minimum Excel version	Google Sheets
`TEXTJOIN`	2016 (365 first)	Yes
`IFS` / `SWITCH`	2019	Yes
`SUMPRODUCT`	All versions	Yes
`AGGREGATE`	2010+	No equivalent
`INDEX` / `MATCH`	All versions	Yes
`OFFSET` / `INDIRECT`	All versions	Yes
`CHOOSE` / `RANDBETWEEN`	All versions	Yes
`UNIQUE` / `FILTER` / `SORT`	365 / 2021	Yes
`SEQUENCE`	365 / 2021	Yes
`TEXTSPLIT`	365 only	Different: SPLIT()
`LET` / `LAMBDA`	365 only	Yes (added 2022)
Spill operator `#`	365 only	No equivalent
`GROUPBY`	365 only, newer builds	No equivalent

Don't want to memorise all 20?

Describe what you're trying to do in plain English and XLsheetAI writes the exact formula for you, explains it, and lets you practise it hands-on.

Want the fundamentals first? Start with the Excel formulas cheat sheet, or see how XLOOKUP compares to VLOOKUP for the lookup side of things.

FAQ

Which Excel version do I need for these formulas?

Tiers 1 and 2 work in Excel 2016 and later, including old perpetual licences. Tier 3 (UNIQUE, FILTER, SORT, SEQUENCE, TEXTSPLIT) needs Microsoft 365 or Excel 2021. Tier 4 (LET, LAMBDA, the spill operator, GROUPBY) is Microsoft 365 only and will not open correctly in older files.

Do these formulas work in Google Sheets?

Most do. TEXTJOIN, IFS, SWITCH, SUMPRODUCT, INDEX/MATCH, OFFSET, INDIRECT, CHOOSE, RANDBETWEEN, UNIQUE, FILTER, SORT, SEQUENCE, LET and LAMBDA all exist in Sheets, sometimes with small syntax differences. AGGREGATE, TEXTSPLIT, the # spill operator and GROUPBY are Excel-only; Sheets has different tools for the same jobs.

What is the single most useful formula on this list?

For most people, LET. It lets you name a calculation once inside a formula and reuse it, which cuts down repeated sub-formulas, shortens long expressions, and makes a formula bar readable enough that you can actually debug it six months later.

Is it risky to use OFFSET and INDIRECT in a real workbook?

Both are volatile, meaning Excel recalculates them on almost every change, which slows down large files. They are also harder to audit than a plain reference because the range isn't visible until you evaluate the formula. Use them when you genuinely need a moving range or dynamic sheet name, not as a default habit.

What does the # spill range operator actually do?

When a dynamic array formula in Excel 365 spills results into multiple cells starting at, say, D2, typing D2# in another formula refers to the entire spilled block, however large it currently is. It removes the need to guess or hardcode how many rows the result will occupy.