Start for free

All Excel functions

Creating a formula in Excel: how it works

A formula always starts with =. Then comes a calculation with cells, such as =B2*C2, or a function, such as =SUM(B2:B6). When a number changes, Excel recalculates straight away.

Start practising hereIn the lesson Getting started with formulas, free

What is a formula?

A formula is an instruction to Excel to calculate something. It always starts with an equals sign: that tells Excel to calculate, and not just copy what you type. Afterwards, the cell shows the result, and the formula bar above the worksheet shows the formula itself.

After the equals sign comes the calculation. Usually you calculate with cells instead of numbers: =B2*C2 multiplies what's in B2 by what's in C2. If you change a number later, Excel recalculates the result straight away.

For more calculation work, such as adding up a whole column, you use a function: a ready-made calculation with a name, such as SUM or AVERAGE. After the name come brackets with the cells the function calculates with: =SUM(D2:D20).

When do you create a formula?

  • You want a total per row, such as price times quantity: =B2*C2.
  • You want to add up a whole column: =SUM(D2:D20).
  • You want to know what's left of a budget: =B2-C2.
  • You want to show a number from another cell, so that it changes along with it: =B2.

Creating a formula, step by step

D2 contains =B2*C2. The price times the quantity. Type 6 in C2, and the total becomes 15.00 straight away. Change the numbers or the formula and see what happens.

D2
A
B
C
D
E
F
G
H
1
Product
Price
Quantity
Total
2
Coffee
£2.50
4
£10.00
3
4
5
6
7
8
9
10
Ready

This is how to go about it:

  1. Click the cell where the result should go, here D2.
  2. Type an equals sign. Excel now knows that a formula is coming.=
  3. Click the cell with the price, B2. Excel puts the reference in your formula and gives the cell a coloured border.=B2
  4. Type the multiplication sign: an asterisk.=B2*
  5. Click the cell with the quantity, C2, and press Enter. The result appears in the cell, and the formula stays in the formula bar.=B2*C2

More examples

Adding up a column with a function

B5 contains =SUM(B2:B4). A function has a name and brackets. Between the brackets is the range B2:B4: all the cells from B2 to B4.

B5
A
B
C
D
E
F
G
H
1
Product
Total
2
Coffee
£10.00
3
Tea
£7.50
4
Water
£6.00
5
£23.50
6
7
8
9
10
Ready

Several operations in one formula

E1 contains =B2*C2+B3*C3. Excel multiplies first and only then adds, as you learnt at school: 10 + 7.50 = 17.50.

E1
A
B
C
D
E
F
G
H
1
Product
Price
Quantity
£17.50
2
Coffee
£2.50
4
3
Tea
£1.50
5
4
5
6
7
8
9
10
Ready

A fixed number in the formula

B2 contains =A2*2. A number that never changes, such as 2 to double something, can go straight into the formula. A price or a rate is better in a cell of its own, so you can change it in one place.

B2
A
B
C
D
E
F
G
H
1
Grams for 4
Grams for 8
2
250
500
3
4
5
6
7
8
9
10
Ready

Where it often goes wrong

  • Without an = at the start, Excel treats what you type as text: 12*3 just stays 12*3 in the cell.
  • Don't type numbers into your formula that are already in a cell. With =B2*C2, Excel recalculates when B2 changes; with =2.5*4, it doesn't.
Want to try it yourself?
Practise this in the lesson Getting started with formulas. It's free.

Start practising here

Frequently asked questions

Where can I see the formula again?

Click the cell: the formula appears in the formula bar above the sheet. To change it, double-click the cell or click in the formula bar.

What's the difference between a formula and a function?

A formula is anything that starts with =. A function is a ready-made calculation with a name, such as SUM or AVERAGE, that you use inside a formula.

Read next

Filling and copying formulasOrder of operationsAbsolute references ($)Calculating percentagesWorking with multiple sheets