Concept
1. Anatomy of a formula
Every formula starts with =. Without it, Excel treats your entry as text.
=B2*C2+10
It has three parts: cell references (B2, C2), operators (*, +), and constants (10). Formulas with functions (like SUM) come in the next lesson.
| You type | Excel shows |
|---|---|
5+3 |
5+3 (text) |
=5+3 |
8 |
2. Operators
| Operator | Meaning | Example | Result |
|---|---|---|---|
+ |
add | =10+5 |
15 |
- |
subtract | =10-5 |
5 |
* |
multiply | =10*5 |
50 |
/ |
divide | =10/5 |
2 |
^ |
power | =10^2 |
100 |
& |
join text | ="Ex"&"cel" |
Excel |
3. Use cell references, not numbers
Instead of =120*3, put 120 in B2 and 3 in C2, then write =B2*C2. Now if you change the price or quantity, the result updates automatically. This is the most important habit in Excel.
4. BODMAS — order of calculation
Excel does brackets first, then powers, then multiply/divide, then add/subtract.
=10+5*2 returns 20, not 30. To add first, use brackets: =(10+5)*2 = 30.
5. Relative reference (default)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price | Qty | Total |
| 2 | Pen | 20 | 5 | |
| 3 | Notebook | 60 | 3 | |
| 4 | Stapler | 150 | 2 |
Type =B2*C2 in D2 and drag the fill handle (the small square at the bottom-right corner of the cell) down to D4. Excel automatically changes the formula to =B3*C3 and =B4*C4. This is a relative reference — it adjusts based on position when copied.
6. Absolute reference ($)
Put GST Rate in F1 and 18% in F2. Type =D2*F2 in E2 and drag down.
Problem: E3 becomes =D3*F3, and F3 is empty, so the answer is 0.
Solution: lock F2 — =D2*$F$2. Now $F$2 stays fixed when you drag.
7. The magic of F4
While typing a formula, place the cursor on a cell reference and press F4. Each press cycles through:
| F4 press | Reference | What's locked |
|---|---|---|
| 1 | $F$2 |
column + row |
| 2 | F$2 |
row only |
| 3 | $F2 |
column only |
| 4 | F2 |
nothing (back to relative) |
(On Mac: Cmd + T or Fn + F4.)
8. Mixed reference — multiplication table
Type 1 to 9 in A2:A10, and 1 to 9 in B1:J1. In B2, enter:
=$A2*B$1
Now copy it across the whole range B2:J10. $A2 locks column A (always the number on the left), and B$1 locks row 1 (always the number on top). One formula builds the entire table.
Common mistakes
Forgetting the =, typing numbers directly into formulas (hard to update later), and not putting $ on a fixed cell (like a tax rate), which gives 0 or wrong answers when copied.