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

ABCD
1RegionSales
2North120030%
3South800
4West400
5East1600

In D2: =B2/SUM($B$2:$B$5) → 30%

1,200 out of 4,000.

How it works

  1. SUM adds up the whole column to get the total.
  2. Dividing one row by that total gives a number between 0 and 1: here, 0.3.
  3. The percent format shows 0.3 as 30%. The $ signs keep the total in place when the formula is filled down.

Variations

  1. With one decimal

    ABCD
    1RegionSales
    2North120030.0%
    3South800
    4West400
    5East1600

    In D2: =B2/SUM($B$2:$B$5) → 30.0%

  2. With the total in its own cell

    ABCD
    1RegionSales
    2North120030%
    3South800
    4West400
    5East1600
    6Total4000

    In D2: =B2/$B$6 → 30%

    Lock the total with $ signs, or it moves down with the formula.

  3. A percentage of a number

    ABCD
    1RegionSales
    2North1200180
    3South800
    4West400
    5East1600

    In D2: =B2*15% → 180

    Going the other way: 15% of 1,200.

Watch out for

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

Practise it

Related