How to Read an Excel Formula

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.

Built-in AI tutor. This page has a tutor. Open it with the "Ask AI" button at the bottom-right. When Chrome can run it on your device, it does. Otherwise your question and your reading-box text go to the class AI server. Either way, the course saves your questions and the tutor's replies. Read a formula yourself first, then ask it to check you. It coaches. It will not hand you the answer.

Take a formula apart

Pick a formula, then click any part of it. Each piece has a name and a job.

Click any part, or Tab to it and press Enter. Parts with no underline (parentheses, commas) are punctuation. They have a job too.

Tip

Click any part of the formula above to see what it is called and what it does.

cell reference range function operator literal / criterion named / structured

The vocabulary

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.

References come in three flavors

The dollar sign $ locks part of a reference so it does not move when you copy or fill the formula. A missing $ makes a filled-down formula drift.

You typeWhat is lockedCopy it down or across and…
B2nothing (relative)Down: B3, B4. Across: C2, D2. It moves both ways.
$B$2column and row (absolute)Stays $B$2 both ways.
$B2column only (mixed)Down: $B3, $B4. Across: stays $B2.
B$2row only (mixed)Down: stays B$2. Across: C$2, D$2.

Tip: select a reference in the formula bar and press F4 (Windows) or Cmd+T (Mac) to cycle through the four forms. On a Mac laptop, Fn+F4 also works.

Arrays and the spill

Most formulas return one value. An array formula returns many at once. For example, =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#).

The order Excel reads in

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.

Operator precedence, highest first

  1. ( ): parentheses. Anything inside is calculated first. When in doubt, add them.
  2. -: negation, as in -A1.
  3. %: percent.
  4. ^: exponent (power).
  5. * and /: multiply and divide, left to right.
  6. + and -: add and subtract, left to right.
  7. &: joins text.
  8. = < > <= >= <>: comparisons, last.

Negation comes before the exponent, so =-2^2 returns 4. Write =-(2^2) if you mean -4. The range colon and the comma, covered below, rank above every operator on this list.

So =A1+B1/2 divides before it adds. If you meant the average, you need =(A1+B1)/2.

Two punctuation marks that change meaning

Read it aloud

Turn the symbols into a sentence, functions inside-out:

=IF(B2<=0,"",(B2-C2)/B2)
→ "If revenue (B2) is zero or below, leave it blank; otherwise take revenue minus cost and divide by revenue. That is the gross margin."

Your turn: read these

Before you open each one, say out loud what every part is and what the whole formula returns. Then check yourself.

=D2/$D$1

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.

=AVERAGEIFS(F2:F500, D2:D500, "Energy")

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.

=XLOOKUP([@ticker], firms[ticker], firms[sector])

Parts: XLOOKUP a function; [@ticker] a structured reference to this row's ticker in the current table; firms[ticker] and firms[sector] structured references into a Table named "firms".
Reads as: take this row's ticker, find it in the firms table's ticker column, and return that firm's sector. Lookups read past the raw cell addresses. The Table names tell you what the columns mean.

Read it, then let the tutor check you

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.

In this example, K2 holds a firm's age in years.