Excel Lessons

How to write an Excel formula, step by step

Click the cell, type =, add the function and the range, press Enter. Each step with one small sheet, then IF, copying down, and five first mistakes.

Published . 6 min read.

By Ali, Founder, Excel Lessons

To write a formula in Excel, click the cell where the answer goes and type =. Then type a function or the maths, point at the cells, and press Enter. Here is each step in order, with one small sheet to follow.

AB
1ItemPrice
2Tea3.50
3Bread2.20
4Milk1.30
5Eggs2.90
6Total9.90

Your first formula, step by step

The goal: the total of the four prices, in B6.

  1. Click B6. The answer gets its own cell. Never type a formula over one of the numbers it uses.
  2. Type =. The equals sign tells Excel that a formula follows. Without it, Excel keeps what you type as text.
  3. Type the function and an opening bracket: SUM(. SUM adds numbers. Excel shows a list of functions as you type, and you can pick from it.
  4. Point at the cells. Drag from B2 to B5, or type B2:B5. The colon means "from B2 to B5".
  5. Close the bracket: ). Every opening bracket needs one.
  6. Press Enter. B6 shows 9.90. Click B6 again and the formula bar shows =SUM(B2:B5). The cell shows the answer. The bar shows how it was made.
  7. Check it with a guess. Four prices of about 2 or 3 each should make about 10. 9.90 is close, so the formula is probably right. A total of 990 or 0.99 would mean something is wrong.

The formula points at the cells, not at the numbers in them. So when the price of milk changes, the total changes too. That is the reason to write a formula at all.

A formula with a decision in it: IF

Now label each item. Anything over 3 is "Expensive". Everything else is "OK". In C2, the steps are the same, with three parts inside the brackets:

=IF(B2>3,"Expensive","OK")

  • The test: B2>3. Is the price over 3?
  • If yes: "Expensive"
  • If no: "OK"

Commas separate the parts. Words go in quotation marks. Numbers and cell addresses do not. For tea, C2 shows Expensive.

Copy it down instead of typing it again

Select C2 to C5 and press Ctrl+D. The formula fills down. In C3 it reads =IF(B3>3,"Expensive","OK"). Excel moved B2 to B3 by itself, because the formula now sits one row lower.

Sometimes you do not want a cell to move, for example a tax rate in one cell. Put $ signs in front: $E$1. Then it stays E1 in every row.

Five mistakes that stop a first formula

  • No = at the start. The cell shows your text, not an answer. Click the cell and add = in front.
  • A missing bracket. Excel may offer to add it, or show an error. Count the brackets: each ( needs a ).
  • A word with no quotation marks. =IF(B2>3,Expensive,"OK") gives #NAME?. Excel read Expensive as a name it does not know.
  • The number instead of the cell. =3.50+2.20 gives the right answer today. It is wrong tomorrow, when a price changes.
  • Commas that Excel will not accept. In some countries Excel uses ; between the parts: =IF(B2>3;"Expensive";"OK"). If commas give an error, try semicolons.

Practise with feedback on every step

Reading the steps is not the same as doing them. In the lessons below, you type each formula into a real sheet. When you press Enter, you see at once whether it is right, and if not, why. How your formula is checked shows what happens to it.

Related articles