How to / Percentages and rounding
How to calculate a percentage of a total in Excel
Divide the part by the whole: =B2/SUM($B$2:$B$5). Then format the cell as a percentage.
One region's share of all sales
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Sales | ||
| 2 | North | 1200 | 30% | |
| 3 | South | 800 | ||
| 4 | West | 400 | ||
| 5 | East | 1600 |
In D2: =B2/SUM($B$2:$B$5) → 30%
1,200 out of 4,000.
How it works
- SUM adds up the whole column to get the total.
- Dividing one row by that total gives a number between 0 and 1: here, 0.3.
- The percent format shows 0.3 as 30%. The $ signs keep the total in place when the formula is filled down.
Variations
With one decimal
A B C D 1 Region Sales 2 North 1200 30.0% 3 South 800 4 West 400 5 East 1600 In D2:
=B2/SUM($B$2:$B$5)→ 30.0%With the total in its own cell
A B C D 1 Region Sales 2 North 1200 30% 3 South 800 4 West 400 5 East 1600 6 Total 4000 In D2:
=B2/$B$6→ 30%Lock the total with $ signs, or it moves down with the formula.
A percentage of a number
A B C D 1 Region Sales 2 North 1200 180 3 South 800 4 West 400 5 East 1600 In D2:
=B2*15%→ 180Going the other way: 15% of 1,200.
Watch out for
- Multiplying by 100. Do not write
=B2/SUM($B$2:$B$5)*100. Formatting as a percentage already shows 0.3 as 30%; multiplying as well would show 3000%. - Shares that do not add up to 100%. Each share is rounded for display, so the visible percentages can add up to 99% or 101%. The values behind them still add up to exactly 1.
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- SUM: Adds up numbers, cells and ranges.
- ROUND: Rounds a number to a given number of digits.
- SUMIF: Adds the cells that meet one condition.