Every formula starts with = and is built from references, numbers, operators and functions. The golden rule: never type a number inside a formula if it could change — put it in a cell and point at it.
1 Anatomy of a formula
- = declares that what follows is a calculation, not text.
- ROUND is a function name, with its arguments inside the brackets, separated by a comma.
- B2 is a relative reference that moves as you fill down.
- $F$1 is an absolute reference, pinned in place.
- 2 is a constant — the number of decimal places.
Comma or semicolon?
Excel separates function arguments with a comma , or a semicolon ; depending on your system’s language settings. If Excel rejects a formula that looks perfectly correct, try switching the separator.
2 The three kinds of reference
| Kind | Form | Behaviour when filled | When to use it |
|---|---|---|---|
| Relative | B2 | Moves horizontally and vertically | A calculation done per row |
| Absolute | $B$2 | Never moves | A rate or fixed price held in one cell |
| Mixed | $B2 or B$2 | Pins only the column or only the row | Multiplication tables and matrices |
Press F4 while editing a formula to cycle through the four forms without typing dollar signs by hand.
The zeros in the table without dollars raise no error message at all — which is why this mistake slips into reports so easily. Press F4 after typing a reference and Excel adds the dollars for you.
The tax rate sits in F1, and the amounts are in column B:
| A | B | C | F | |
|---|---|---|---|---|
| 1 | Invoice | Amount | Tax | 15% |
| 2 | INV-001 | 10,000 | 1,500 | |
| 3 | INV-002 | 4,000 | 600 | |
| 4 | INV-003 | 25,500 | 3,825 |
If you wrote =B2*F1 with no dollars: filling down to C3 makes it =B3*F2 — and F2 is an empty cell, so zeros appear. This is the single most common error in Excel.
And why not write =B2*0.15? Because the day the rate changes, you have to edit every formula in the file. With a cell, you edit it once.
3 Order of operations
( )
Brackets first
^
Exponent
* ÷
Multiplication and division
+ −
Addition and subtraction
&
Joining text
= < >
Comparison last
=2+3*4 equals 14, not 20 — and brackets settle what you meant
4 Error messages and what they mean
| Error | What it means | The fix |
|---|---|---|
| #DIV/0! | Division by zero or by an empty cell | Wrap it in IFERROR, or check the denominator |
| #VALUE! | The wrong data type (text in an arithmetic operation) | Convert the text to a number |
| #REF! | A deleted reference — you removed a row or column the formula pointed to | Undo immediately with Ctrl+Z and rebuild the reference |
| #N/A | The value was not found in the lookup range | Check spaces and formatting, or use IFNA |
| #NAME? | A misspelt function or range name | Correct the name or define the range |
| ##### | Not an error — the column is narrower than the number | Widen the column |
| A | B | C | |
|---|---|---|---|
| 1 | Average | #DIV/0! | Dividing by a count of zero |
| 2 | Total | #VALUE! | “SAR 1,200” is text, not a number |
| 3 | Item price | #N/A | The code is not in the price table |
Do not hide an error before you understand it
IFERROR hides every error, including the real ones. Use it after you have established the cause, not before, and prefer IFNA when “not found” is the only thing you expect.
Lesson summary
- Every formula starts with = and is read in the formula bar.
- Put changeable constants in cells and point at them with an absolute reference.
- F4 cycles between relative, absolute and mixed.
- Brackets come before multiplication, and multiplication before addition.
- #REF! means you deleted something the formula depended on — undo at once.
5 Test your understanding
Three quick questions
Pick the answer you believe is correct and you will see the result immediately.
1. The commission rate is in cell H1 and will be used for every row. The correct formula in C2 is:
B2 is relative so it moves with the rows, and H1 is absolute so it stays put when you fill down.
2. =10+2*5 equals:
Multiplication before addition: 2×5=10, then +10 = 20. If you wanted 60, write =(10+2)*5.
3. #REF! appeared across a whole column after you deleted another column. What do you do?
#REF! is not fixed by hiding it — the reference is genuinely gone, and undo is the fastest way back.