How Excel calculates

The idea

A spreadsheet is a grid. Every box in it is a cell, named by its column letter and row number: B2 is column B, row 2. A cell holds one of two things, and the whole tool follows from the difference.

A value is something you typed: a number, some text, a date. It sits there and means what it says. A formula starts with = and is a small instruction: "work this out from other cells". The cell stores the instruction and displays the answer.

That split is what makes a spreadsheet more than a table on paper. If C2 changes from 31.99 to 34.99, D2 is not stale: it is the current answer to a question you wrote once. The skill of Excel is writing the question so that it keeps being right.

Why it exists

Before spreadsheets, an analyst with a calculator wrote down 287.91, added 35.97 by hand, and wrote down 323.88. Change one quantity and you started over. Every number on the page was an answer to a question nobody had saved.

Excel saves the question. Harbor & Pine's order 1001 has two lines; the order total is not a number somebody pasted, it is =SUM(D2:D3). A manager can change a quantity, a price or a discount and the total follows, which is why the same workbook can be reused for next month's data or a "what if" about a price rise. It is also why a typed number sitting in the middle of a column of formulas is dangerous: it will never update and nobody will notice.

How it works under the hood

References are pointers

B2*C2 does not contain the numbers, it contains addresses. Excel looks at those addresses when it calculates. A range such as D2:D3 means every cell in the rectangle between the two corners, and functions like SUM and AVERAGE accept ranges as inputs.

Copying keeps the relationship

Here is the part that confuses most beginners. When you copy a formula, or drag the fill handle down a column, Excel does not copy the text. It copies the relationship: "two cells to my left" stays "two cells to my left", wherever the formula lands. That is why one formula written in D2 and filled down gives a correct formula in every row. A reference like B2 is relative: it moves with the copy.

Sometimes a pointer must not move. A single tax rate in E1 should be read by every row. Putting $ in front of the column letter, the row number or both locks that part: $E$1 is absolute, $E1 locks only the column and E$1 only the row. Pressing F4 while the cursor is inside a reference cycles through the four forms.

The dependency chain

Excel keeps a map of who reads whom. When you edit a cell, it finds every cell that reads it, recalculates those, then every cell that reads those, and so on, in the right order. It does not recalculate the whole workbook, so a sheet with thousands of formulas still updates instantly.

This also explains two errors. A circular reference is a formula that eventually reads itself, so there is no right order and Excel warns you. A formula pasted as values has lost its pointers: it is now an ordinary typed number, and it will stay wrong when the source changes.

Ranges and Tables

A plain range is just a rectangle of cells. Nothing says that A1:D400 is "the orders": if a new row arrives in row 401, every formula still points at the old rectangle. An Excel Table (Insert → Table, or Ctrl+T) is a range that knows it is a table. It has a name, headers, and it grows when a row is added, and formulas can point at columns by name instead of by address. You meet Tables properly in the last chapters of this track; for now the rule is that a range is a rectangle you describe, a Table is a rectangle Excel keeps track of.

Formatting is not content

One more thing sits between a stored value and what you see: the number format. A date is stored as a number and displayed as a date; 0.0825 can be shown as 8.25%; 31.99 can be shown as 32. Changing the format changes the display, never the stored value, and a column that looks like numbers may hold text that SUM will ignore. When a result looks odd, check what is stored before you suspect the formula.

Common misconceptions

"The number in the cell is the number the cell holds"

What you see is the result of the formula, not the content. Click the cell and look at the formula bar: it shows what is actually stored. If a total looks right but won't change when the data does, it was probably typed or pasted as a value.

"Copy and paste copies the formula text"

It copies the relationship, so relative references shift. That is a feature (one formula fills a thousand rows) until a reference that should have stayed put slides away. When a filled-down column goes wrong, click the last row and check where its pointers ended up.

"A blank cell is an error"

A blank cell counts as zero in arithmetic and is skipped by SUM and AVERAGE. Pointing at the wrong cell therefore rarely produces an error; it produces a plausible wrong number. Always spot-check one row by hand.