Your First Formulas: Cell References and the $ Sign

A formula is what makes Excel more than a table. Instead of typing a total, you tell Excel how to work it out, and it recalculates when the data changes. If you change a price, every amount that depends on it updates at once. This lesson teaches the rules of formulas and the single most important idea in Excel: cell references, including the dollar sign that confuses so many beginners.

The basics

• A formula always starts with an equals sign =.
• Use the operators + add, - subtract, * multiply, / divide and ^ power.
• Excel does multiplication and division before addition and subtraction. Use brackets to control the order, like in school maths: =(A1+A2)*3.
• Refer to other cells by their address instead of typing the numbers: =D2*E2.

Click cell G2 in the sales sheet, type the following and press Enter.

=D2*E2

It multiplies the Qty (10) by the Price (120), and G2 shows 1200. Now click G2 again and look at the formula bar. The cell shows the result, and the formula bar shows the formula. If you change D2 to 12, G2 becomes 1440 immediately.

Typing with the mouse

You do not have to type the addresses. Type =, click D2, type *, click E2, and press Enter. Excel writes =D2*E2 for you. This is faster and avoids typing mistakes. You can also use the arrow keys.

Copy the formula down

You need an amount for all ten rows. Do not retype. Select G2 and double-click the fill handle (the small square in the bottom right corner), or drag it down to G11. Excel copies the formula, and adjusts it for each row.

G2:  =D2*E2   -> 1200
G3:  =D3*E3   -> 2000
G4:  =D4*E4   -> 2800
...
G11: =D11*E11 -> 1400

This is called a relative reference. When you copy a formula down by one row, every row number in it goes up by one. When you copy it right by one column, every column letter shifts by one. The formula remembers the relationship, “the two cells to my left”, rather than the exact addresses. This is what makes copying formulas so powerful.

Absolute references: the dollar sign

Sometimes you want a reference to stay fixed when you copy. Suppose a tax rate of 5% sits in cell J1 and you want a Tax column with =G2*J1. Copy that down and in row 3 it becomes =G3*J2, which points to an empty cell. The rate must not move.

Put dollar signs in front of the column letter and row number: $J$1. The formula =G2*$J$1 is copied down as =G3*$J$1, =G4*$J$1 and so on. This is an absolute reference.

Relative:   J1     moves when copied
Absolute:   $J$1   never moves
Mixed:      $J1    column fixed, row moves
            J$1    row fixed, column moves

There is a shortcut. Click inside the reference in the formula bar and press F4 (Cmd+T on a Mac). Each press cycles through J1, $J$1, J$1 and $J1. Use the one you need.

Practice: a tax column

Add a label Tax rate in I1 and the value 5% in J1. Add a heading Tax in H1. In H2 type =G2*$J$1 and copy it down. For the first row, 1200 times 5% gives 60. Use the tax rate of your own country and product when you build a real sheet. The 5% here is only an example.

Formulas that point to other sheets

You can refer to a cell on another sheet by putting the sheet name and an exclamation mark in front: =Sales!G2. If the sheet name has spaces, put it in quotes: ='Jan sales'!G2. Click the other sheet while typing the formula and Excel writes it for you.

Seeing and checking formulas

• Press Ctrl+` (the key above Tab, called the grave accent or backtick) to show all formulas on the sheet instead of results. Press it again to go back.
• Select a cell and use Formulas, Trace Precedents to see arrows to the cells it uses.
• Click a cell with a formula and press F2. Excel colour-codes the referenced cells so you can see which ones are used.

Common mistakes

• Forgetting the equals sign. Typing D2*E2 shows the text, not the answer.
• Typing numbers inside formulas. =D2*120 works until the price changes. Refer to the cell.
• Forgetting the dollar sign and getting wrong results lower down a copied formula.
• Mixing text and numbers, for example a number with “pcs” attached. The formula returns a #VALUE! error.
• Deleting a cell that formulas use, which gives #REF!.

Your task

1. Fill G2:G11 with =D2*E2 and copy it down.
2. Add the tax-rate cell and a Tax column with an absolute reference, as described.
3. Change the Notebook price in E2 to 150 and see G2 and H2 change.
4. Show all formulas with Ctrl+` and then switch back.

How to check it worked

• G2 = 1200, G3 = 2000, G4 = 2800, G5 = 1800, G6 = 1950, G7 = 2800, G8 = 2400, G9 = 1400, G10 = 2400 and G11 = 1400 (with the original prices).
• Every row of the Tax column points to $J$1.
• You can say in one sentence the difference between J1 and $J$1.