Percentages in Excel
Last updated
Excel has no percentage function: you divide. =B2/$B$7 gives a part’s share of the total, =(C2-B2)/B2 the change from an old to a new value, and =B2*(1+$F$1) raises a price by the rate in F1. Format the result as a percentage to show 0.25 as 25%.
The percentage formula
=part/total
- part
- The piece you measure: one product’s sales, the amount of a discount, or the difference between two values.
- total
- What 100% stands for: the grand total, or the old value when you measure a change. Lock it with
$($B$7) when you copy the formula down.
Practice percentages here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Share of the total
Fill C2:C6 with each product’s share of the total sales in B7. Write the formula once in C2, lock the total with $, then copy it down (Ctrl + D).
| Product | Sales ($) | Share of total |
| Coffee beans | 4,250 | |
| Tea | 3,180 | |
| Pastries | 2,640 | |
| Sandwiches | 1,920 | |
| Mugs | 1,510 | |
| Total | 13,500 |
How to calculate a percentage, step by step
- Click the cell for the result and type
=. - Select the part, type
/, then select the total. Press F4 to turn the total into$B$7if you will copy the formula. - Press Enter, then click Percent Style (Ctrl + Shift + %) on the Home tab so 0.3148 shows as 31%.
- Copy the formula down with Ctrl + D. Only the part moves; the locked total stays put.
Percentage formula examples
Share of a total
=B2/$B$7
Sales of 4,250 out of 13,500 give 0.3148, shown as 31.48%.
Percent change
=(C2-B2)/B2
From 48,200 to 52,600 is a change of 9.13%. A drop gives a negative percentage.
Increase by a percent
=B2*(1+$F$1)
With 4% in F1, a price of 25 becomes 26.
Decrease by a percent
=B2*(1-C2)
A 10% discount in C2 turns 89 into 80.10.
Price before tax
=B2/(1+$F$1)
With a 20% tax rate in F1, 24 including tax is 20 before tax.
Common percentage problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| #DIV/0! | The total or the old value is empty or zero. It often happens when the total wasn’t locked and the copied formula points to an empty cell. | Lock the total with $B$7, or skip empty rows with =IF(B2=0,"",(C2-B2)/B2). |
| Shows 0.25 instead of 25% | The result is right, but the cell has a General or Number format. | Apply the Percent Style format (Ctrl + Shift + %). Don’t multiply by 100. |
| Shows 2500% | The formula multiplies by 100 and the cell is also formatted as a percentage, so it’s scaled twice. | Remove the *100: the percent format already does it. |
| Total changes when copied | The total is a relative reference (B7), so it moves to B8, B9… as you copy down. | Lock it with $B$7: press F4 right after selecting it. |
Percent change vs percentage points
A percent change and a change in percentage points are not the same. If a margin goes from 20% to 25%, it rose by 5 percentage points (25% − 20%), but by 25% in relative terms ((25% − 20%) ÷ 20%). The percent format only changes the display: 0.25 and 25% are the same value, so formulas use 0.25. That is why you type 4% or 0.04 in a rate cell, never 4.
Frequently asked questions
Is there a percentage function in Excel?
No. A percentage is a division: =part/total. The percent format then shows the result as 25% instead of 0.25.
How do I add 10% to a price?
Multiply by 1 + 10%: =B2*(1+10%), or =B2*(1+$F$1) with the rate in F1. =B2+10% would only add 0.1.
How do I calculate a percentage decrease?
Use the same formula as an increase: =(new-old)/old. A drop simply comes out negative, for example −6.84%.
How do I remove tax from a price?
Divide by 1 + the rate: =B2/(1+20%). Taking 20% off the price including tax gives too little, because the tax was 20% of the price before tax.
Keep practising
All 5 percentage exercises, with explanations and progress tracking.
Open the percentage practice set