A formula is a sentence. Once you can name the parts — references, ranges, functions, operators — you can read any formula you meet, and write one that says what you mean. Click the pieces below to take a formula apart.
Pick a formula, then click any part of it. Each piece has a name and a job.
Click a highlighted part. Greyed pieces (parentheses, commas) are punctuation.
Click any colored part of the formula above to see what it is called and what it does.
The words analysts use for the parts of a formula. You do not have to memorize these — you have to recognize them when you see them.
The dollar sign $ locks part of a reference so it does not move when
you copy or fill the formula. This is the single most common beginner bug — a missing
$ makes a filled-down formula drift.
| You type | What is locked | Fill it down and… |
|---|---|---|
B2 | nothing (relative) | becomes B3, B4, B5 — it moves |
$B$2 | column and row (absolute) | stays B2 always |
$B2 | column only (mixed) | column stays B, row moves |
B$2 | row only (mixed) | row stays 2, column moves |
Tip: select a reference in the formula bar and press
F4 to cycle through the four forms.
Most formulas return one value. An array formula returns many at once —
=B2:B500*C2:C500 multiplies 499 pairs. When the result is more than one cell,
it spills into the cells below or to the right, marked with a faint blue border.
Reference the whole spilled range with the spill operator #, as in =SUM(E2#).
Excel does not read strictly left to right. It follows operator precedence, and it evaluates functions from the inside out. Read a formula the same way and it stops being a wall of symbols.
( ) — parentheses. Anything inside is calculated first. When in doubt, add them.^ — exponent (power).* and / — multiply and divide, left to right.+ and - — add and subtract, left to right.= < > <= >= — comparisons, last.So =A1+B1/2 divides before it adds. If you meant the average,
you need =(A1+B1)/2.
: — the range operator. F2:F500 means "F2 through F500"., — the argument separator (and the union operator). It splits a
function's inputs: AVERAGEIFS(what to average,
where to look, what to match).Turn the symbols into a sentence, functions inside-out:
=IF(B2<=0,"",(B2-C2)/B2)Before you open each one, say out loud what every part is and what the whole formula returns. Then check yourself.
Parts: D2 a relative reference (this row's value);
/ division; $D$1 an absolute reference (locked).
Reads as: this row's value as a share of the fixed total in D1. The
$ signs keep the denominator from drifting when you fill the formula down
the column — every row divides by the same D1.
Parts: AVERAGEIFS a function;
F2:F500 and D2:D500 ranges; "Energy" a text criterion;
commas separate the arguments.
Reads as: average the numbers in F2:F500, but only for the rows where the
matching cell in D2:D500 equals "Energy". One number out: the average for the Energy sector.
Parts: XLOOKUP a function;
[@firm_id] a structured reference to this row's firm_id in the current table;
firms[id] and firms[sector] structured references into a Table named
"firms".
Reads as: take this row's firm_id, find it in the firms table's id column, and
return that firm's sector. Lookups read right past the raw cell addresses — the Table
names tell you what the columns mean.
Here is a formula you have not taken apart yet. In the box, write in plain English what each part is and what the whole thing returns. Then ask the AI tutor to check your reading — it coaches what you missed, it does not hand you the answer.