Excel formulas, explained properly

LET 365 Advanced

LET(name1, value1, [name2, value2, …], calculation)

Names come in pairs (name, value) and the last argument is the calculation that uses them. Names can refer to earlier names.

=IF(XLOOKUP(B2,Factors[Fuel],Factors[kgCO2e])=0,
    "check fuel",
    C2*XLOOKUP(B2,Factors[Fuel],Factors[kgCO2e])/1000)

=LET(
    factor, XLOOKUP(B2, Factors[Fuel], Factors[kgCO2e]),
    tonnes, C2 * factor / 1000,
    IF(factor = 0, "check fuel", tonnes)
)

Reading a LET: skim the names first (factor, tonnes) — they tell you the story. Then read the final line, which is usually short. Only dig into the value expressions if something looks wrong.

TEXT Basic

TEXT(value, format_code)

Turns a number into text using a format code — the same codes you can type into Format Cells (Ctrl+1) → Custom, such as #,##0.0 or d mmm yyyy. Essential when building labels with &, because & ignores cell formatting.

="Total: "&TEXT(E6,"#,##0.0")&" tCO2e as at "&TEXT(TODAY(),"d mmm yyyy")
→ Total: 72.6 tCO2e as at 18 Sep 2026

The result is text — you can't sum it. Keep the number in its own cell and only use TEXT for the label.

LEFT · RIGHT · MID Basic

LEFT(text, [num_chars]) · RIGHT(text, [num_chars]) · MID(text, start, num_chars)

Take characters from the start, end, or middle (starting at position start, counting from 1).

=LEFT("OX-01 Oxford Office", 5)     → OX-01
=RIGHT("2026-09", 2)               → 09
=MID("OX-01 Oxford Office", 4, 2)  → 01

LEN Basic

LEN(text)

Number of characters, including spaces. Handy for validation (=LEN(A2)=8) and for the classic "count how many times X appears" trick:

=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))   // number of commas in A2

FIND · SEARCH Basic

FIND(find_text, within_text, [start]) · SEARCH(same arguments)

Return the position of one string inside another. FIND is case-sensitive and literal; SEARCH ignores case and allows wildcards (* ?). Both return #VALUE! if not found — wrap with ISNUMBER to make a test:

=ISNUMBER(SEARCH("gas", B2))   → TRUE if B2 contains "gas" in any case
=MID(A2, FIND(" ", A2)+1, 99)  // everything after the first space (legacy way)

SUBSTITUTE · REPLACE

SUBSTITUTE(text, old_text, new_text, [instance]) · REPLACE(text, start, num_chars, new_text)

SUBSTITUTE swaps text you know (all instances, or just the n-th). REPLACE swaps by position. Nine times out of ten you want SUBSTITUTE.

=SUBSTITUTE(A2, "-", "")        → OX01 Oxford Office
=SUBSTITUTE(A2, " ", "_", 1)    → OX-01_Oxford Office   (only the first space)

TRIM · CLEAN Basic

TRIM(text) · CLEAN(text)

The first thing to reach for when a lookup "should" match but doesn't. TRIM removes leading/trailing spaces and collapses doubles; CLEAN strips non-printing characters from imports. They don't remove non-breaking spaces (CHAR(160)) from web copy-paste — add SUBSTITUTE(x, CHAR(160), " ") for those.

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))   // the full "sanitise an import" wrapper

UPPER · LOWER · PROPER Basic

UPPER(text) · LOWER(text) · PROPER(text)

Change case. PROPER capitalises every word (so "o'neil" becomes "O'Neil" and "LPG" becomes "Lpg" — check your acronyms). Excel comparisons with = are already case-insensitive, so you rarely need these for matching.

& · CONCAT · CONCATENATE Basic

text1 & text2 · CONCAT(text1, [text2, …])

Join pieces of text. The & operator is the clearest for a few pieces; CONCAT accepts whole ranges. CONCATENATE is the old name, kept for compatibility.

=A2&" – "&B2&" ("&TEXT(C2,"#,##0")&" kWh)"
→ Oxford office – Electricity (48,200 kWh)

TEXTJOIN 365

TEXTJOIN(delimiter, ignore_empty, text1, [text2, …])

Join a whole range with a separator between items — the function everyone wanted for 20 years. Pair it with FILTER or UNIQUE to build "list all the sites in this region" strings.

=TEXTJOIN(", ", TRUE, A2:A5)               → Oxford office, Oxford office, Madrid office, Warehouse
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A5))       → Oxford office, Madrid office, Warehouse

TEXTSPLIT 365

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

The formula version of Text-to-Columns. Splits into columns (and optionally rows) and spills the result.

=TEXTSPLIT("OX-01 Oxford Office", " ")   → OX-01 | Oxford | Office   (3 cells across)
=TEXTSPLIT("a,b;c,d", ",", ";")          → a b / c d   (2 × 2 block)

TEXTBEFORE · TEXTAFTER 365

TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) · TEXTAFTER(same)

Replace the old LEFT(A2,FIND(" ",A2)-1) gymnastics. A negative instance_num counts from the end.

=TEXTBEFORE(A2, " ")          → OX-01
=TEXTAFTER(A2, " ")           → Oxford Office
=TEXTAFTER(A2, " ", -1)       → Office        (after the LAST space)
=TEXTBEFORE(A2, "-", , , , "no dash")   // fallback instead of #N/A

VALUE · NUMBERVALUE

VALUE(text) · NUMBERVALUE(text, [decimal_sep], [group_sep])

Convert text that looks like a number into a real number (green-triangle "number stored as text" cells won't sum until you do). NUMBERVALUE handles European "1.234,5". Quick alternatives: --A2, A2*1, or Data → Text to Columns → Finish on the whole column.

REPT

REPT(text, number_times)

Repeat text. The classic use is an in-cell bar chart: =REPT("█", ROUND(E2/5,0)). Also for padding codes: =REPT("0", 6-LEN(A2))&A2 (or just use the 000000 number format).

DATE · TODAY · YEAR / MONTH / DAY Basic

DATE(year, month, day) · TODAY() · YEAR(date)

Build a date from parts, or pull parts out. DATE is forgiving: DATE(2026,13,1) is 1 Jan 2027 and DATE(2026,3,0) is the last day of February — a neat trick for "last day of previous month".

=DATE(YEAR(A2), 1, 1)     // first day of A2's year
=DATE(YEAR(A2), MONTH(A2)+1, 0)   // last day of A2's month (or use EOMONTH)

EOMONTH · EDATE Basic

EOMONTH(start_date, months) · EDATE(start_date, months)

Move whole months forward or back. EOMONTH lands on the month end; EDATE keeps the same day. Negative months go backwards; 0 means "this month".

=EOMONTH(A2, 0)      // end of A2's month       → 30/09/2026
=EOMONTH(A2, -1)+1   // first day of A2's month → 01/09/2026
=EDATE(A2, 12)       // one year on, same day   → 18/09/2027

NETWORKDAYS · WORKDAY

NETWORKDAYS(start, end, [holidays]) · WORKDAY(start, days, [holidays])

Count working days between two dates, or find the date n working days away. Point holidays at a list of bank-holiday dates. The .INTL versions let you choose which days count as the weekend.

DATEDIF Hidden

DATEDIF(start, end, "Y" | "M" | "D" | "YM" | "MD" | "YD")

Whole years/months/days between two dates. It's not in Excel's autocomplete (a compatibility leftover) but it works and it's the cleanest way to get "age in years" or "months elapsed".

=DATEDIF(A2, TODAY(), "M")   // whole months since A2

IF Basic

IF(logical_test, value_if_true, [value_if_false])

Test something; return one value if it's TRUE, another if FALSE. Leaving out the third argument returns FALSE (usually not what you want — use "" for blank).

=IF(C2>50000, "Large", "Small")
=IF(C2="", "", C2*D2/1000)          // stay blank until there's an input
=IF(AND(B2="Natural gas", C2>50000), C2*0.18293/1000, 0)

IFS 2019+

IFS(test1, value1, [test2, value2, …])

Several tests in order; returns the value for the first test that's TRUE. Replaces nested IFs. There's no built-in "else" — add a final TRUE, "otherwise" pair, or you'll get #N/A when nothing matches.

=IF(C2>=100000,"A",IF(C2>=50000,"B",IF(C2>=10000,"C","D")))

=IFS(
    C2 >= 100000, "A",
    C2 >= 50000,  "B",
    C2 >= 10000,  "C",
    TRUE,         "D"
)

Order matters. Tests are checked top to bottom, so put the strictest first (≥100000 before ≥50000). For banding with many tiers, a lookup table with XLOOKUP(…, -1) is cleaner still — see the XLOOKUP explorer.

SWITCH 2019+

SWITCH(expression, value1, result1, [value2, result2, …], [default])

When you're comparing one thing against a list of exact values, SWITCH is tidier than IFS because you write the expression once.

=SWITCH(B2, "Natural gas", "kWh", "Diesel", "litre", "Electricity", "kWh", "check unit")

AND · OR · NOT Basic

AND(test1, [test2, …]) · OR(test1, [test2, …]) · NOT(test)

AND is TRUE only if every test is TRUE; OR if any is; NOT flips. They return a single TRUE/FALSE, which is why they don't work inside array formulas like SUMPRODUCT or FILTER — there you multiply instead (below).

=IF(AND(C2>0, D2>0), C2*D2, "missing input")
=IF(OR(B2="Diesel", B2="Petrol"), "liquid fuel", "other")
=NOT(ISBLANK(A2))

Boolean maths: * for AND, + for OR, -- to convert Advanced

Excel treats TRUE as 1 and FALSE as 0 the moment you do arithmetic on it. So:

This is the engine under SUMPRODUCT and the include argument of FILTER. Once you can read (cond1)*(cond2) as "cond1 AND cond2", most scary array formulas become plain English.

ISBLANK · ISNUMBER · ISTEXT · ISERROR · ISNA

ISNUMBER(value) etc.

Tests that return TRUE/FALSE about what a cell contains — useful as the test inside IF, or to build a "contains" check: =ISNUMBER(SEARCH("gas",B2)). Note ISBLANK is FALSE for a cell containing "" from a formula; use A2="" to catch both.

IFERROR · IFNA Basic

IFERROR(value, value_if_error) · IFNA(value, value_if_na)

Both return value normally and a fallback if it errors. The difference is what they catch:

=IFNA(XLOOKUP(A2, Factors[Fuel], Factors[Factor]), "check fuel name")
=IFERROR(C2/D2, 0)          // OK: the only realistic error is divide-by-zero

VLOOKUP Basic Legacy

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Looks for lookup_value down the first column of table_array, then returns the value from column number col_index_num of that table (counting the first column as 1). The fourth argument must be FALSE (or 0) for an exact match — always set it; the default is approximate matching, which returns wrong answers silently on unsorted data.

=VLOOKUP(A2, $F$2:$H$6, 3, FALSE)
// A2            what to find ("Diesel")
// $F$2:$H$6     the table, locked so it doesn't shift when filled down
// 3             return the 3rd column of that table (Factor)
// FALSE         exact match — never leave this out

HLOOKUP Legacy

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

VLOOKUP rotated 90°: searches the first row and returns from a row number below. Same limitations. XLOOKUP handles both directions.

MATCH Basic

MATCH(lookup_value, lookup_array, [match_type])

Returns a position (1, 2, 3…), not a value. match_type 0 = exact (use this), 1 = largest value ≤ lookup (list sorted ascending), -1 = smallest value ≥ lookup (sorted descending).

=MATCH("Diesel", F2:F6, 0)   → 2       // Diesel is the 2nd item

INDEX Basic

INDEX(array, row_num, [column_num])

Returns the cell at that row (and column) of the range. Row 0 or column 0 means "the whole row/column", which is how INDEX can return a range for other functions.

=INDEX(H2:H6, 2)             → 2.51233   // 2nd cell of the Factor column
=INDEX(G2:I4, 2, 3)          → value at row 2, column 3 of the block

INDEX / MATCH together, and the two-way lookup Advanced

=INDEX($H$2:$H$6, MATCH(A2, $F$2:$F$6, 0))
// read it inside-out: MATCH finds the row → INDEX returns that row of column H
// key column and return column are separate, so the key can be ANYWHERE — left, right, another sheet

Use two MATCHes for a grid where you need a row and a column — here, emission factors by fuel (rows) and year (columns):

=INDEX($G$2:$I$4, MATCH(A2, $F$2:$F$4, 0), MATCH(B2, $G$1:$I$1, 0))
// INDEX(block, which row, which column)

Inserting columns in the source table no longer breaks anything, because the return column is a reference, not a number. This pattern is still worth knowing: it's everywhere in existing workbooks, and it works in every Excel version.

XLOOKUP 365 Basic

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

=XLOOKUP(A2, $F$2:$F$6, $H$2:$H$6, "not found")
=XLOOKUP(A2, Factors[Fuel], Factors[Factor], "not found")          // same, using a Table
=XLOOKUP(A2, Factors[Fuel], Factors[[Unit]:[Factor]], "")          // returns Unit AND Factor, spilling across
=XLOOKUP(A2, Factors[Fuel], Factors[Factor], , , -1)               // last match (e.g. latest entry in a log)

XMATCH 365

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

MATCH with XLOOKUP's mode arguments and exact-match default. Use with INDEX when you need a position (e.g. to feed into OFFSET or to find the last non-blank row: =XMATCH(TRUE, A:A<>"", , -1)).

Approximate matching for bands and tiers

When you have thresholds (0, 50, 100, 250, 500 → Band A–E), don't write nested IFs. Put the lower bounds in a column and use "exact or next smaller":

=XLOOKUP(C2, Bands[Threshold], Bands[Band], "n/a", -1)   // 365: works in any order
=VLOOKUP(C2, $F$2:$G$6, 2, TRUE)                        // legacy: thresholds must be sorted ascending

INDIRECT Advanced

INDIRECT(ref_text, [a1])

Turns text into a reference: =INDIRECT("'"&A2&"'!B5") reads B5 from whichever sheet is named in A2. Powerful for "one summary row per sheet" layouts. Costs: it's volatile (recalculates constantly), it breaks silently if a sheet is renamed, and Excel can't trace it. Use sparingly; consider a Table plus XLOOKUP, or Power Query, instead.

OFFSET Advanced

OFFSET(reference, rows, cols, [height], [width])

Returns a range that's shifted from a starting cell and optionally resized. Classic for "the last 12 months": =SUM(OFFSET(B1, COUNTA(B:B)-12, 0, 12, 1)). Also volatile. In 365, TAKE/DROP (=SUM(TAKE(B2:B200,-12))) do the same job without the volatility.

CHOOSE · ROW · COLUMN

CHOOSE(index_num, value1, [value2, …]) · ROW([ref]) · COLUMN([ref])

CHOOSE(3,"Q1","Q2","Q3","Q4") returns "Q3". ROW() and COLUMN() return the current row/column number — handy for alternating formats or building sequences in older Excel (365 has SEQUENCE).

Referring to other sheets and workbooks

=Factors!B2                         // sheet named Factors, cell B2
='Site data 2026'!B2                // sheet names with spaces need single quotes
=SUM(Jan:Dec!B2)                    // 3-D reference: B2 on every sheet from Jan to Dec
='C:\Reports\[Factors 2026.xlsx]Factors'!$B$2   // another workbook (a "workbook link", see chapter 6)

Tip: instead of typing these, start the formula, then click the other sheet tab and the cell — Excel writes the reference correctly, quotes and all.

SUMIFS · COUNTIFS · AVERAGEIFS · MAXIFS · MINIFS Basic

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, …]) · COUNTIFS(criteria_range1, criteria1, …)

Use the plural …IFS versions even for one condition — the argument order is consistent and you can add conditions later. (SUMIF/COUNTIF are the older singular forms with the sum range last.)

=SUMIFS(Energy[kWh], Energy[Site], "Oxford office", Energy[Fuel], "Electricity")
=COUNTIFS(Energy[Fuel], "Natural gas", Energy[kWh], ">10000")
=AVERAGEIFS(Energy[kWh], Energy[Site], F2)
=MAXIFS(Energy[kWh], Energy[Fuel], "Natural gas")

Criteria syntax — the part people get wrong

Criteria are text strings, even when they compare numbers. Anything other than "equals this cell" needs quotes, and cell references are glued on with &:

OR conditions aren't supported directly. Either add two SUMIFS together, or pass an array: =SUM(SUMIFS(D:D, C:C, {"Diesel","Petrol"})).

SUMPRODUCT Advanced

SUMPRODUCT(array1, [array2, …])

Multiplies arrays element by element, then sums. Two jobs: (1) weighted sums in one go — =SUMPRODUCT(C2:C9, D2:D9) is Σ(kWh × factor) without a helper column; (2) conditional sums with logic SUMIFS can't express (OR across columns, conditions on calculated values, year of a date).

=SUMPRODUCT(Energy[kWh], Energy[Factor]) / 1000        // total tonnes, no helper column
=SUMPRODUCT((YEAR(Energy[Date])=2026) * (Energy[Fuel]="Natural gas") * Energy[kWh])
// (condition) * (condition) * values — see Boolean maths in chapter 4

Reading tip: each (…) in brackets is a column of TRUE/FALSE; the * turns them into 1/0 and combines them with AND. Step through the walk-through in chapter 2.

SUBTOTAL · AGGREGATE

SUBTOTAL(function_num, ref1, …) · AGGREGATE(function_num, options, ref1, …)

SUBTOTAL aggregates only the visible rows — so a total above a filtered Table changes as you filter. function_num 9 = SUM, 1 = AVERAGE, 3 = COUNTA; add 100 (109, 101, 103) to also ignore manually hidden rows. It also ignores other SUBTOTALs, so nested subtotals don't double count. The Table "Total Row" uses it.

AGGREGATE is SUBTOTAL's big sibling: 19 functions and an options code — 6 = ignore errors, which makes =AGGREGATE(9, 6, D2:D500) a "sum, skipping the #N/As" without touching the errors.

FILTER 365

FILTER(array, include, [if_empty])

Returns the rows of array where include is TRUE. include is a column of TRUE/FALSE — combine tests with * (AND) and + (OR). Always set if_empty or you'll get #CALC! when nothing matches.

=FILTER(A2:C9, B2:B9="Natural gas", "none")
=FILTER(Energy, (Energy[Fuel]="Natural gas") * (Energy[kWh]>50000), "none")
=FILTER(Energy, (Energy[Site]="Oxford office") + (Energy[Site]="Lab"), "none")   // OR

SORT · SORTBY 365

SORT(array, [sort_index], [sort_order], [by_col]) · SORTBY(array, by_array1, [order1, …])

SORT orders by a column inside the array (index number, 1 = ascending, -1 = descending). SORTBY orders by any other range, and can take several keys.

=SORT(FILTER(A2:C9, B2:B9="Natural gas"), 3, -1)     // gas rows, biggest kWh first
=SORTBY(Energy[Site], Energy[tCO2e], -1)              // sites ordered by emissions

UNIQUE 365

UNIQUE(array, [by_col], [exactly_once])

The distinct values, in order of first appearance. Feed it to SORT for an alphabetical list, or to COUNTA to count distinct items. exactly_once = TRUE returns only the values that appear a single time.

=SORT(UNIQUE(Energy[Site]))
=COUNTA(UNIQUE(Energy[Site]))          // how many distinct sites

SEQUENCE 365

SEQUENCE(rows, [columns], [start], [step])

Generates numbers. =SEQUENCE(12) → 1 to 12 down a column; =EDATE(DATE(2026,1,1), SEQUENCE(12,1,0)) → the 12 month-start dates of 2026 — the usual way to build a report header that never needs retyping.

TAKE · DROP · CHOOSECOLS · VSTACK / HSTACK 365 Newer

Array "plumbing": TAKE(x, -12) the last 12 rows; DROP(x, 1) skip the header; CHOOSECOLS(x, 1, 3) pick columns; VSTACK(a, b) append one list under another (e.g. combine two sites' Tables into one). They replace most OFFSET and INDEX-with-SEQUENCE tricks.

GROUPBY · PIVOTBY 365 Newest

GROUPBY(row_fields, values, function, […]) · PIVOTBY(row_fields, col_fields, values, function, […])

A PivotTable as a formula — it updates live, no Refresh. =GROUPBY(Energy[Site], Energy[tCO2e], SUM) gives a site total list; PIVOTBY adds column fields. Rolling out across 365 channels during 2024–25; check your version has it before building on it.

LAMBDA 365 Advanced

LAMBDA([parameter1, …], calculation)

Define it in Name Manager (Ctrl+F3 → New → Refers to) and call it by name anywhere in the workbook:

// Name: TONNES   Refers to:
=LAMBDA(qty, fuel, qty * XLOOKUP(fuel, Factors[Fuel], Factors[Factor]) / 1000)
// then in any cell:
=TONNES([@kWh], [@Fuel])

Good for a calculation that appears in ten places and must stay consistent. The downside: colleagues can't see the logic in the cell, so document the name's Comment field in Name Manager.

BYROW · BYCOL · MAP · SCAN 365 Advanced

Apply a LAMBDA to each row, column, or element of an array and get an array back. =BYROW(B2:E9, LAMBDA(r, SUM(r))) gives per-row totals as one spilled formula; =SCAN(0, C2:C9, LAMBDA(acc, x, acc + x)) is a running total. Rarely essential — but they remove the need for helper columns.

Finding invisible characters: CODE · UNICODE · CHAR

CODE(text) · UNICODE(text) · CHAR(number)

Two cells look identical but don't match. Compare lengths, then look at each character's code: 32 is a normal space, 160 is a non-breaking space from web pages, 10 is a line break, 9 a tab.

=LEN(A2)                         → 7    (but the visible text is 6 characters)
=UNICODE(RIGHT(A2,1))            → 160  (non-breaking space — TRIM won't remove it)
=SUBSTITUTE(A2, CHAR(160), "")   // fix

FORMULATEXT · ISFORMULA

FORMULATEXT(reference) · ISFORMULA(reference)

Show a neighbouring cell's formula as text (useful in documentation columns or for auditing a model), or test whether a cell contains a formula at all — combined with Conditional Formatting, =NOT(ISFORMULA(A1)) highlights every hard-coded value in a calculation block.