References instead of numbers

In the bill below, the amount for each item and the total are calculated with formulas, and so far everything adds up. But the chair went up in price. Change its price and see what happens to the amounts:

The amount for the chairs in D2 is now 1,200, but the total in D4 is still 2,780. Both cells are calculated with formulas, but the formulas are written differently.

Numbers in a formula know nothing about the table

Select D2 and look at the formula bar: it shows =B2*C2. This formula tells Excel: "take the number from B2 and multiply it by the number from C2." A cell address inside a formula is called a reference.

When a number in a cell changes, Excel recalculates every formula that refers to that cell. That's why D2 updated after the new price.

D4 contains =1000+1780. Someone looked at the amounts and copied them into the formula as numbers. Those numbers have no connection to D2 and D3, so the total fell behind. That's why formulas should hold the addresses of the cells where the numbers are, not the numbers themselves.

An address with a click

You can type an address on the keyboard, but pointing to the cell with the mouse is more reliable:

1. Select the cell for the result and type =.
2. Click the cell with the first number. Its address appears in the formula by itself.
3. Type an operation sign, for example +.
4. Click the second cell and press Enter.

This way you can't get a letter or a row number wrong. Fix the total in the same bill:

You can also refer to a cell that holds a formula too, just as the total refers to the item amounts. Then the whole chain recalculates: the quantity changes, the item amount updates, and the total follows.

To check a finished formula, double-click it. The cells it refers to get colored borders, and the addresses in the formula turn the same colors. If a border is around the wrong cell, you've found the mistake. To leave without changing anything, press Esc.