Your first formulas
Write formulas that calculate for you: operators, order of operations, pointing to cells and a worked fees example.
In this lesson you will
- Write formulas with operators
- Predict results using order of operations
- Point formulas at cells
- Show and read formulas
About 14 minutes
Teaching Excel to calculate
Sana runs a small tuition class. Each month she works out fees by hand: the fee per student times the number of students, minus a discount, plus a late charge for some. Every month the numbers change a little and she starts the sums again. With formulas she will type the rule once and let Excel do the rest every month. Formulas are the heart of Excel, and the basics are simple.
The rules of a formula
- Start with =.
- Combine numbers, cell addresses and operators.
- Press Enter. The cell shows the result; the formula stays in the formula bar.
The operators
| Symbol | Meaning | Example |
|---|---|---|
| + | Add | =A1+A2 |
| – | Subtract | =A1-A2 |
| * | Multiply | =A1*A2 |
| / | Divide | =A1/A2 |
| ^ | Power | =A1^2 |
| % | Percent | =A1*10% |
Order of operations
Excel follows the same order as school maths: brackets first, then powers, then multiplication and division, then addition and subtraction. Use brackets when you want something done first. These results were really calculated by a spreadsheet program, in a sheet where B2 is 40, C2 is 12 and B3 is 5:
| Formula | Result |
|---|---|
| =2+3*4 | 14 |
| =(2+3)*4 | 20 |
| =10/4 | 2.5 |
| =2^3 | 8 |
| =50*10% | 5 |
| =B2*C2 | 480 |
| =B2*C2+B3 | 485 |
Look at the first two lines. =2+3*4 gives 14 because the multiplication happens first, but =(2+3)*4 gives 20 because the brackets force the addition first. Same numbers, different answers. When in doubt, add brackets: they never hurt.
Use cell addresses, not typed numbers
You could type =40*12, but then the formula is stuck with those numbers. If you type =B2*C2 instead, the formula reads whatever is in B2 and C2 right now. Change the price and the answer updates. This is the key habit of spreadsheet work: put values in cells, and point formulas at the cells.
Point and click
You do not have to type addresses. Type =, click the cell B2, type *, click C2 and press Enter. Excel writes the addresses for you and colours each one in the formula so you can see which cells are being used. This avoids typing mistakes.
Seeing your formulas
- Select a cell and read the formula bar.
- Press F2 to edit: the cells used are outlined in colour.
- Press Ctrl+` (the key under Esc) to show every formula on the sheet, and again to hide them. On the Formulas tab it is called Show Formulas.
A worked example: tuition fees
| Cell | Content |
|---|---|
| B1 | Fee per student: 1500 |
| B2 | Students: 18 |
| B3 | Discount percent: 10% |
| B4 | =B1*B2 (total before discount) |
| B5 | =B4*B3 (discount amount) |
| B6 | =B4-B5 (total to collect) |
Next month Sana changes B2 to 20 and everything else follows. She never rewrites the formulas.
When a formula shows something odd
| You see | Likely reason |
|---|---|
| The formula text itself | The cell was formatted as Text, or you started without = |
| #### | The column is too narrow for the result; widen it |
| 0 where you expect a number | Calculations may be set to manual, or the cells hold text |
| #DIV/0! | You divided by zero or by an empty cell |
Practice
Rebuild the tuition example. Put the fee, students and discount in cells, then write the three formulas using cell addresses. Then change the number of students to 25 and the discount to 5% and check that the total updates. Finally press Ctrl+` to see all formulas at once.
With a fee of 1500, 18 students and a 10% discount, the total before discount is 27000, the discount is 2700 and the amount to collect is 24300. If your discount cell shows 0.1 instead of 10%, format it as a percentage (the Home tab has a % button).
Typing numbers inside formulas, such as =B4-2700. When the discount changes, the formula will not. Point to cells instead.
Check your answers with a quick estimate
Excel never makes arithmetic mistakes, but you can make mistakes in what you ask it. Before you trust a result, estimate it in your head. If 18 students pay about 1500 each, the total should be near 27000, not 2700 or 270000. A quick estimate catches missing zeros, wrong cells and misplaced brackets in seconds. Make it a habit every time you finish a formula.
Build long formulas in small steps
Beginners often try to write one long formula for the whole problem. A better habit is to break the job into steps, each in its own cell with a label beside it, exactly as the fees example does. Every step can be checked on its own, and anyone reading the sheet can follow the logic. Later, once you are confident, you may combine steps, but clear sheets beat clever ones.
- Label every input and every result in the cell next to it.
- Keep inputs (fee, students, discount) together at the top, so they are easy to change.
- Use one calculation per cell until you are sure it is right.
- Test with easy numbers such as 10 and 100, where you already know the answer.
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What does =2+3*4 give?
- 20
- 14
- 24
- 9
Multiplication happens before addition.
How do you make the addition happen first?
- Use two equals signs
- Type it in capitals
- Move it to column A
- Put it in brackets
Brackets come first.
Why use =B2*C2 instead of =40*12?
- It is shorter
- It updates when the cells change
- It is a different colour
- It avoids the equals sign
Formulas should point to cells.
What shortcut shows all formulas on the sheet?
- Ctrl+P
- Alt+F4
- Ctrl+W
- Ctrl+`
It toggles Show Formulas.
What does ^ do?
- Raises to a power
- Adds
- Divides
- Starts a formula
2^3 means 2 times 2 times 2.
Summary
- Every formula starts with =.
- Brackets, powers, multiply and divide, then add and subtract.
- Point formulas at cells, not typed numbers.
- Ctrl+` shows every formula.
Questions
Why does Excel show my formula instead of the result?
The cell may be formatted as Text. Change the format to General, then press F2 and Enter.
Can a formula use cells from another sheet?
Yes. Write the sheet name then an exclamation mark, such as =Sheet2!B2.
Are Excel formulas the same in Google Sheets?
The basics are identical. Sheets and Excel differ mainly in newer or specialised functions.
