Excel Formula for Percentage of Total
By Szabó Gergő · Updated
Divide the row amount by the column total and lock the total with $ so the formula copies cleanly down the sheet.
Syntax and arguments
=IF(SUM($B$2:$B$100)=0,"",B2/SUM($B$2:$B$100))- row amount
- The value on this row, such as B2.
- total range
- The full amount column, locked with $.
Percentage of total examples
01
Share of revenue
Amounts in B2:B20.
=B2/SUM($B$2:$B$20)B2 is this row. $B$2:$B$20 stays the same when you copy down.
02
Guard an empty total
The column can be all blank.
=IF(SUM($B$2:$B$20)=0,"",B2/SUM($B$2:$B$20))Avoids #DIV/0! before any numbers are entered.
03
Percent of a filtered total
Visible rows only, Excel 365.
=B2/SUBTOTAL(109,$B$2:$B$20)SUBTOTAL 109 sums visible cells after a filter.
Common mistakes
Forgetting $ on the total
Use $B$2:$B$20 or the total shifts when you fill down.
Formatting as number instead of percent
Set the result column to Percent format.
Including the header in the SUM
Start the total at the first data row.
Percentage of total FAQ
Should I multiply by 100?
No if the cell is Percent format. Yes only if you want a raw 0-100 number.
Can the total sit in another cell?
Yes. Put =SUM(B2:B20) in B22 and use =B2/$B$22.
How do I show one decimal?
Use a percent format with one decimal, or =ROUND(B2/$B$22,3).