There’s no PERCENT function in Excel. Percentages are just simple division and multiplication, plus the right formatting. These four formulas cover almost everything you’ll need.
First: how Excel stores percentages
In Excel, 100% is the number 1. So 25% is 0.25, and 5% is 0.05. The % format just displays the number multiplied by 100 with a % sign.
That’s why you should never multiply by 100 in your formulas. Let the formatting do it.
1. Percentage of a total
What percentage of the total is each item?
=B2/B10
where B10 is the total. You’ll get a decimal like 0.1875. With the cell selected, click the % button on the Home tab (or press Ctrl + Shift + %) and it shows as 19%.
Click Increase Decimal next to it to show 18.75%.
Copying it down? Lock the total with a $ sign so it doesn’t move: =B2/$B$10. Click on B10 in the formula and press F4.
2. Percentage change
How much has something gone up or down?
=(New-Old)/Old
For example, last year’s sales in B2 and this year’s in C2:
=(C2-B2)/B2
Format as a percentage. An increase shows as a positive percentage, a drop as a negative one.
3. Add a percentage (e.g. VAT or a price rise)
To add 20% to the amount in B2:
=B2*(1+20%)
or =B2*1.2. You can also put the rate in its own cell, say E1, and use =B2*(1+$E$1). Then change the rate once and every price updates.
To work out just the VAT on its own: =B2*20%.
4. Take a percentage off (e.g. a discount)
To take 15% off the price in B2:
=B2*(1-15%)
To remove VAT from a price that already includes it (working backwards), divide instead of multiplying:
=B2/1.2
Don’t use =B2*80% for this. It gives the wrong answer.
Bonus: what percentage is X of Y?
If 45 out of 60 people passed, what’s the pass rate?
=45/60
Format as a percentage and you get 75%.
Formatting percentages
- Home > % or Ctrl + Shift + % applies percentage format.
- Use Increase / Decrease Decimal to change decimal places.
- For more control, press Ctrl + 1 and choose Percentage.
Troubleshooting
I typed 25 and it shows 2500%. You applied the % format after typing 25, and Excel treated it as 25 × 100%. Either type 0.25, or type 25% with the % sign. If you format empty cells as a percentage first, typing 25 becomes 25%.
My percentage shows as 0%. Increase the decimal places. The value might be 0.4%.
#DIV/0! The total or the “old” value is zero or blank. Use =IFERROR((C2-B2)/B2, "").
Percentage change over 100%. That’s correct if something more than doubled. Going from 50 to 150 is a 200% increase.
Wrapping up
Divide to get a percentage, then press the % button. Use (new-old)/old for change, *(1+rate) to add a percentage and *(1-rate) to take one off. Never multiply by 100.
