Start for free

All Excel functions

Calculating percentages in Excel: how it works

To Excel, 20% is simply 0.2. A percentage of an amount is =amount*20%, and the amount plus 20% is =amount*(1+20%).

Try the first exercises for freeIn the lesson Working with percentages

How does Excel calculate with percentages?

To Excel, a percentage is just a number divided by 100: 20% is 0.2. If you type 20% into a cell, Excel stores 0.2 and shows 20%. That's why you can calculate with it straight away: =A2*20% gives 20% of A2.

Almost every calculation with percentages is one of these four: part of an amount, an amount plus a percentage, an amount minus a percentage, or the difference between two numbers as a percentage.

When do you calculate with percentages?

  • The tax on an amount: =A2*20%.
  • A price with the tax added: =A2*(1+20%).
  • A price after a 15% discount: =A2*(1-15%).
  • How much something has grown, as a percentage: =(B2-A2)/A2.
  • What share of the total one region is: =B2/B6, with percentage formatting.

Step by step

B2 contains =A2*(1+20%). 100 plus 20%, for example a tax or a markup. Change the numbers or the formula and see what happens.

B2
A
B
C
D
E
F
G
H
1
Price
Plus 20%
2
100
120
3
4
5
6
7
8
9
10
Ready

This is how to go about it:

  1. Click B2, where the price plus 20% should go, and start with the cell with the price.=A2*
  2. You want 100% of the price, plus 20% on top. 100% is 1, so you put 1 plus the percentage in brackets.=A2*(1+20%
  3. Close the bracket and press Enter: 100 becomes 120.=A2*(1+20%)

More examples

Taking the tax out of a price that includes it

B2 contains =A2/(1+20%). Dividing by 1.2 gives the amount without tax. Taking 20% off 120 would be wrong: that gives 96.

B2
A
B
C
D
E
F
G
H
1
Incl. tax
Excl. tax
2
120
100
3
4
5
6
7
8
9
10
Ready

A discount

C2 contains =A2*(1-B2). If you get a 25% discount, you still pay 75%: 80 × 0.75 = 60.

C2
A
B
C
D
E
F
G
H
1
Price
Discount
After discount
2
80
25%
60
3
4
5
6
7
8
9
10
Ready

Growth as a percentage

C2 contains =(B2-A2)/A2. The difference, 150, divided by the old number, 1250: 0.12, which is 12%. C2 has percentage formatting.

C2
A
B
C
D
E
F
G
H
1
Last year
This year
Growth
2
1250
1400
12%
3
4
5
6
7
8
9
10
Ready

The formulas

Growth: =(new-old)/old. Discount: =amount*(1-discount). Taking 20% back off an amount that already includes it: =amount/1.2.

Where it often goes wrong

  • 20% of an amount that already includes 20% isn't the right share: divide by 1.2 instead.
  • If you type 20 in a cell with percentage formatting, it becomes 20%.
Want to try it yourself?
Practise this in the lesson Working with percentages. The first exercises are free.

Try the first exercises for free

Frequently asked questions

How do I work out a percentage of a total?

Divide the value by the total and give the cell percentage formatting.

Read next

Creating a formulaFilling and copying formulasOrder of operationsAbsolute references ($)Working with multiple sheets