Excel Help

Percentages in Excel: Four Simple Formulas You Will Use Again and Again

Discounts, price increases, growth, shares of a total. Learn the four percentage formulas that cover almost everything, with no maths degree required.

About 9 minutes to read

Percentages Are Everywhere

"Everything is 20% off." "Sales grew by 12%." "Add 25% VAT." Percentages show up in prices, reports and budgets, and at some point you will want Excel to work them out for you.

The good news: there are only four basic percentage formulas, and none of them is hard. Once you recognize which question you are asking, you know which formula to use.

In one sentence: a percentage is just a number out of 100, and Excel can calculate it, show it and apply it for you.

The One Thing to Know First

Behind the scenes, Excel stores a percentage as an ordinary number. 25% is stored as 0.25, and 100% is stored as 1. The percent sign is only a way of showing the number.

You see	Excel stores
5%	0.05
25%	0.25
100%	1
150%	1.5

This is why you never need to multiply by 100 or divide by 100 in a formula. You just calculate, and then tell Excel to show the result as a percentage.

How to show a number as a percentage: select the cell and click the % button on the Home tab, or press Ctrl + Shift + 5.

A handy trick: when you type a percentage into a cell, type the percent sign too (20%). Excel then knows you mean 0.20.

Formula 1: What Is 20% of 250?

This is the most common question: how much is a part of a number? Maybe a discount, a tip or a tax.

	A	B	C	D
1	Item	Price	Discount	Saving
2	Jacket	250	20%	50
3	Shoes	80	10%	8

=B2*C2

You simply multiply the number (blue) by the percentage (orange). 250 × 20% = 50. The jacket is reduced by 50.

Watch out: if C2 contains 20 instead of 20%, Excel multiplies by twenty and you get 5000. Make sure the cell shows the percent sign.

Formula 2: What Percent Is 45 of 60?

Now the question is turned around. You know two numbers and want to know what percentage one is of the other. Perhaps 45 of 60 tasks are done.

	A	B	C	D
1	Name	Done	Total	Progress
2	Anna	45	60	75%
3	Ben	30	40	75%

=B2/C2

You divide the part (orange) by the whole (blue), then format the cell as a percentage. 45 ÷ 60 = 0.75, which Excel shows as 75%.

If you see 0.75 instead of 75%: the formula is right, the cell just needs the percent format. Click the % button.

Formula 3: How Much Did It Grow or Shrink?

This is the percentage change. Sales were 80 last year and 100 this year. By what percentage did they grow?

	A	B	C	D
1	Month	Last year	This year	Change
2	January	80	100	25%
3	February	120	90	-25%

=(C2-B2)/B2

In plain words: "new minus old, divided by old." (100 − 80) ÷ 80 = 0.25, or 25%. A positive result (green) means growth. A negative result, like February's −25%, means a decrease.

Don't forget the brackets. Without them, Excel divides first and subtracts afterwards, and the answer is wrong. The subtraction must be in brackets.

Always divide by the old number, the one you are comparing against. Dividing by the new number is the most common mistake.

Formula 4: Add or Subtract a Percentage

A price goes up by 15%, or a discount takes 20% off. What is the new price?

	A	B	C
1	Price	Change	New price
2	200	15%	230
3	200	-20%	160

=A2*(1+B2)

The trick is the 1 +. The "1" stands for 100% of the original price, and you add the change to it. With 15% that gives 115% of 200, which is 230.

The same formula handles decreases too. Just type the change as a negative percentage (−20%). Then 1 + (−0.20) = 0.80, and 80% of 200 is 160. One formula, both directions.

Don't do this: =A2*B2 only gives the amount of the change (30), not the new price. To get the new price you need the 1+.

Quick Guide: Which Formula Do I Need?

Your question	Formula
What is 20% of 250?	`=250*20%`
What percent is 45 of 60?	`=45/60`
How much did it change, from 80 to 100?	`=(100-80)/80`
What is 200 plus 15%?	`=200*(1+15%)`
What is 200 minus 20%?	`=200*(1-20%)`

In real spreadsheets, replace the numbers with cell references (B2, C2), as in the examples above. That way your results update when the numbers change.

Decimal Point or Comma?

If you type 0.25 into a cell, it may not work in some countries. In many European countries the decimal mark is a comma, so you must type 0,25 there.

The easiest way to avoid the problem is to type the number with a percent sign: 25% works the same way everywhere. The formulas in this article use only + - * / and brackets, so they are identical in every country, with no commas or semicolons to worry about.

Advanced

Share of a Total and the Dollar Sign

A common task is to show what share of the total each item represents. Here are three products and their sales, with the total in B5.

	A	B	C
1	Product	Sales	Share
2	Notebook	400	40%
3	Pen set	250	25%
4	Desk lamp	350	35%
5	Total	1000	100%

=B2/$B$5

This is Formula 2 (part divided by whole), with one difference: the dollar signs in $B$5.

Why the dollar signs?

You will want to copy this formula down to the other rows. When you copy a formula, Excel normally moves the cell references along with it. B2 becomes B3, then B4. That is what we want for the sales numbers, but not for the total. The total must stay in B5 for every row.

The dollar signs lock the reference. $B$5 always means B5, no matter where you copy the formula. (If your keyboard makes the dollar sign hard to find, click the reference in the formula and press F4.)

Working backwards

A jacket costs 160 after a 20% discount. What did it cost before?

=160/(1-20%)

After a 20% discount you pay 80% of the original price, so you divide by 0.80 to get back to 100%. The answer is 200.

A common mistake: adding 20% to 160 gives 192, not 200. The 20% was taken off the original price, not off 160, so you cannot simply add it back.

Common Problems and How to Fix Them

You see 0.25 instead of 25%

The formula is right, but the cell is formatted as a regular number. Select the cell and click the % button, or press Ctrl + Shift + 5.

You see 2500% instead of 25%

The formula multiplied by 100 and then the cell was also formatted as a percentage, so the factor is applied twice. Remove the "× 100" from the formula. The percent format does that for you.

You see #DIV/0!

Excel is dividing by zero or by an empty cell. This often happens in a percentage change when the old value is 0 or blank. You can hide the error with =IFERROR((C2-B2)/B2, ""). (With semicolons in European Excel: =IFERROR((C2-B2)/B2; "").)

The percentage looks rounded

Excel might show 33% when the real value is 33.333%. The calculation is still exact. To show decimals, click the Increase Decimal button on the Home tab.

The answer is wildly too big

You probably typed 20 instead of 20%. Type the percent sign, or enter the rate as 0.2 (or 0,2 in European Excel).

The percentage change has the wrong sign

Check the order of subtraction. It must be new minus old. Swapping them turns growth into a decrease and the other way around.

Percent versus percentage points

If a rate rises from 10% to 12%, it has risen by 2 percentage points, but by 20% in relative terms (2 ÷ 10). Be clear about which one you want before choosing a formula. Subtract the two for percentage points, and use Formula 3 for the relative change.

The Short Version

Excel stores 25% as 0.25. Calculate, then format the cell as a percentage.
A percentage of a number: multiply (=B2*C2).
What percent one number is of another: divide (=B2/C2).
Percentage change: new minus old, divided by old (=(C2-B2)/B2).
Add or subtract a percentage: =A2*(1+B2), with a negative percentage for a decrease.
Use $ to lock a total when copying a formula down.