Percentages in Excel
Excel has no percentage function. You use plain arithmetic and the % format. The share of a total is =part/total, and the percentage change is =(new-old)/old. Format the result cell as Percentage to see 25% instead of 0.25.
=part/total, (new-old)/old, amount*(1+percent)
part/total- What share of the whole a value is. The result is a fraction, shown as a percentage once the cell has the % format.
(new-old)/old- How much a value changed, relative to where it started. Positive means growth, negative means a drop.
amount*(1+percent)- Adds a percent to an amount. Use
amount*(1-percent)to take a percent off.
How percentages work in Excel
A percentage is a number divided by 100. Excel stores 20% as 0.2. The percent sign is only a way of displaying that number. This one fact explains most percentage mistakes.
When you type 20% into a cell, Excel stores 0.2 and formats the cell as a percentage. When a formula returns 0.25 and you press the % button on the Home tab, the cell shows 25%. The value does not change, only the display. Because of this, you never multiply by 100 in the formula, and you write *20% or *0.2, never *20.
Percent of a total
The share a part has of a whole is the part divided by the whole. In the sheet below, North sold 1,500 in 2025 and all regions together sold 5,000:
=C2/C6
The result is 0.3, which displays as 30% in a percentage cell.
To work out a percent of an amount, multiply instead: 20% of 80 is =80*20%, which is 16.
Fill down with a fixed total
To get the share for every region, you fill the formula down. A plain =C2/C6 becomes =C3/C7 in the next row, and the total moves out of the table. The total must stay put, so lock it with dollar signs: =C2/$C$6. Press F4 after clicking the cell reference to add them. The part (C2) has no dollar signs, so it moves down one row at a time.
Percentage change
To compare an old and a new value, work out the difference and divide it by the old value:
=(C2-B2)/B2
- The brackets make Excel subtract first. Without them, division runs before subtraction and the result is wrong.
- North went from 1,200 to 1,500. The difference is 300, and 300 divided by 1,200 is 0.25, or +25%.
- A negative result is a fall. South went from 800 to 720, which gives -10%.
Always divide by the old value. It is the starting point, so the change is measured against it.
Adding or taking off a percent
To raise an amount by a percent, multiply by 1 plus the percent. To lower it, multiply by 1 minus the percent.
=B11*(1+C11) =B10*(1-C10)
Adding 22% to 80 is 80*(1+22%), which is 80*1.22, or 97.60. Taking 20% off 80 is 80*(1-20%), which is 80*0.8, or 64. The same thing written the long way is =B10-B10*C10.
Percent and percentage points
A change from 10% to 12% is 2 percentage points, but it is a 20% increase (2 divided by 10). Subtract two percentages to get percentage points, and use (new-old)/old to get a percentage change.
Practise Percentages
Click a numbered cell, type a formula and press Enter. Each answer is checked straight away, then tested again on changed data, so the result has to come from the formula. While typing a formula, click or drag across cells to insert their references. Yellow cells are inputs you can change.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | 2024 | 2025 | Change | Share of 2025 | |
| 2 | North | 1,200 | 1,500 | |||
| 3 | South | 800 | 720 | |||
| 4 | East | 1,000 | 1,280 | |||
| 5 | West | 1,000 | 1,500 | |||
| 6 | Total | 4,000 | 5,000 | |||
| 7 | ||||||
| 8 | Calculation | Amount | Rate | Result | ||
| 9 | Discount amount | 80.00 | 20% | |||
| 10 | Price after discount | 80.00 | 20% | |||
| 11 | Price with VAT | 80.00 | 22% | |||
| 12 | ||||||
| 13 |
-
1In
D9, work out the discount amount: the rate inC9(20%) of the amount inB9.Show answer
=B9*C9 -
2In
D10, work out the price after taking the discount rate inC10off the amount inB10.Show answer
=B10*(1-C10) -
3In
D11, add the VAT rate inC11to the amount inB11, to get the price including VAT.Show answer
=B11*(1+C11) -
4In
D2, work out the percentage change from 2024 (column B) to 2025 (column C). It is filled down toD5for you, and the column is already formatted as a percentage.Show answer
=(C2-B2)/B2 -
5Write one formula in
E2that gives each region's share of the 2025 total inC6. It is filled down toE5, and all four rows must be right.Show answer
=C2/$C$6
Common Percentages problems
The result shows 0.25 instead of 25%
The formula is fine, the cell just does not have the percentage format. Select it and press Ctrl+Shift+% or click the % button on the Home tab.
The result shows 2500%
You multiplied by 100 and the cell is also formatted as a percentage. Remove the *100 or change the cell format to Number.
#DIV/0!
You divided by zero or by an empty cell. This happens in percentage change when the old value is 0 or blank, and when a fill-down formula moves the total out of the table. Check the divisor, and lock it with $ if it must not move. You can also return nothing: =IF(B2=0, "", (C2-B2)/B2).
A wrong answer that looks plausible
Check the brackets and the order. Excel multiplies and divides before it adds and subtracts, so =C2-B2/B2 and =(C2-B2)/B2 are completely different. Type a simple case you can check in your head, such as 100 to 150.
Typing a rate into a cell
Type the percent sign yourself, 20%, so there is no doubt that you mean 0.2. A bare 20 in a cell that is not formatted as a percentage is the number 20, and a formula that multiplies by it gives a result 100 times too large.
The shares are wrong after filling down
The total is probably not locked. Use dollar signs on any cell that must stay fixed when the formula is copied: $C$6.
Questions
How do I calculate a percentage of a total in Excel?
Divide the part by the total, =B2/B10, and format the result as a percentage. To fill the formula down a column, lock the total with dollar signs: =B2/$B$10.
How do I calculate percentage increase or decrease in Excel?
Use =(new-old)/old, for example =(C2-B2)/B2, and format the cell as a percentage. A positive result is an increase, a negative result is a decrease.
How do I add a percentage to a number in Excel?
Multiply by 1 plus the percentage: =B2*(1+20%) adds 20%. To take a percentage off, use =B2*(1-20%).
What is the difference between a percent and a percentage point?
Going from 10% to 12% is a rise of 2 percentage points (12% minus 10%), but a 20% increase (2 divided by 10). Mixing the two up is a common error in reports.
Why does Excel show 0.25 instead of 25%?
Excel stores percentages as fractions, and 25% is 0.25. Format the cell as Percentage (Ctrl+Shift+%) to display it with a percent sign.
How do I calculate a percentage change when the old value is zero?
You cannot divide by zero, so the formula returns #DIV/0!. Check first: =IF(B2=0, "", (C2-B2)/B2). Whether to show a blank, "n/a" or something else depends on what the report is for.