Percentages in Excel: share of a total, change and increase
Three percentage formulas cover most work: part of a total, change from old to new, and adding a percentage. Each with an example and the usual mistake.
Three formulas cover most percentage work in Excel. Share of a total is
=part/total. Change is
=(new-old)/old. Adding a percentage is
=value*(1+rate).
There is no percentage function, because there is nothing for one to do. Each formula is a division or a multiplication. The percent sign is only how the cell shows the answer.
First: a percentage is a fraction
25% and 0.25 are the same number in Excel. Type 25% into a cell and the cell holds 0.25. The format shows it with a percent sign.
So a formula that gives 0.375 is already a percentage. Select the cell and press Ctrl+Shift+%. It shows 38%, because the format has no decimal places. Add one decimal place and it shows 37.5%.
Do not multiply by 100 first. Format 37.5 as a percentage and it shows 3750%. The format always multiplies by 100 for you.
1. Share of a total
A share is the part divided by the whole. Here are sales for 4 regions.
| Row | A | B | C |
|---|---|---|---|
| 1 | Region | Sales | Share |
| 2 | North | 3000 | 37.5% |
| 3 | South | 2600 | 32.5% |
| 4 | East | 1400 | 17.5% |
| 5 | West | 1000 | 12.5% |
| 6 | Total | 8000 |
In C2, type =B2/$B$6. It gives 0.375, which shows as 37.5%.
Fill it down to C5.
The 4 shares add up to 1, which is 100%. That is the quick test that every share is right.
The usual mistake: no dollar signs on the total. Type
=B2/B6 and C2 is still right. But fill it down and C3 holds
=B3/B7. B7 is empty, so C3 shows
#DIV/0!. The total must stay on B6, so it needs $B$6.
2. Change from old to new
A change is the difference divided by the old value. The old value is always the one you divide by.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Sales by product | |||
| 2 | Product | Last year | This year | Change |
| 3 | Tea | 250 | 300 | 20% |
| 4 | Coffee | 400 | 340 | -15% |
In D3, type =(C3-B3)/B3. Tea went from 250 to 300. The answer
is 0.2, which is 20%.
Fill it down. Coffee went from 400 to 340, so D4 gives -0.15. That is a fall of 15%. A negative change is a decrease.
The usual mistake: no brackets. =C3-B3/B3 gives
299, not 0.2. Excel divides before it subtracts. So it works out B3/B3, which is 1, and takes
that from 300.
The second mistake is dividing by the new value. =(C3-B3)/C3
gives about 16.7%. That answers a different question. Always divide by where you started.
=C3/B3-1 gives the same 20%. Some people find it shorter. Use
the one you can read.
Percent or percentage points?
Say a rate goes from 10% to 12%. That is 2 percentage points higher. It is also a 20% change, because 2 is 20% of 10.
Both are true, and they answer different questions. =12%-10%
gives 0.02, the points. =(12%-10%)/10% gives 0.2, the change.
Say which one you mean.
3. Add or take off a percentage
To add 20% to a price, multiply by 1 plus the rate. To take 15% off, multiply by 1 minus the rate. Here B2 holds a price of 80.
-
Add 20%:
=B2*(1+20%)gives 96. -
Take off 15%:
=B2*(1-15%)gives 68. -
Just the 15%:
=B2*15%gives 12.
Put the rate in its own cell, not inside the formula. Then you change one cell when the rate changes. Lock it with dollar signs if you fill the formula down.
The usual mistake: =B2+20%. It gives 80.2, not
96. 20% is the number 0.2, so Excel adds 0.2 to the price.
Taking a percentage back off
A price of 96 already has 20% tax added. What was it before the tax? Many people type
=96*(1-20%). That gives 76.8, which is wrong.
Divide instead. =96/(1+20%) gives 80.
The 20% was added to 80, not to 96. So you undo a multiplication by 1.2 with a division by 1.2.
Questions people ask
Why does my change show #DIV/0!?
The old value is 0 or empty. A change from nothing has no percentage. Show an empty cell
instead with =IF(B3=0,"",(C3-B3)/B3).
Why do my shares not add up to 100%?
Click each share and read the total it divides by. If one points at the wrong cell, the shares no longer add up. A total without dollar signs is one way this happens.
Rounding can also fool you. Shares shown with no decimal places can add up to 99% or 101% on
screen. Check with =SUM(C2:C5), which adds the real values.
How do I round a percentage?
A format only changes what you see. To change the value, use ROUND.
=ROUND(0.375,2) gives 0.38, which shows as 38%.
The Excel practice lessons include one on percentages. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.