beginnerSUM

Calculate Each Item's Percentage of the Total

You have amounts by category — spend, revenue, headcount — and need each row expressed as a share of the total, so 2,500 becomes "50% of everything."

Quick formula

=B2/SUM($B$2:$B$6)

Sample input

	A	B
1	Category	Spend
2	Payroll	2500
3	Marketing	1500
4	Software	1000

Result

	A	B	C
1	Category	Spend	% of Total
2	Payroll	2500	50%
3	Marketing	1500	30%
4	Software	1000	20%

Excel & Google Sheets

=B2/SUM($B$2:$B$6)

This formula works in both Excel and Google Sheets.

How it works

Each row's value is divided by the same grand total. The $ signs are the whole trick: $B$2:$B$6 is an absolute reference, so when you copy the formula down, the numerator moves row by row while the denominator stays locked on the full range. Format the column as a percentage and the shares appear — and they always add up to 100%, which makes this the fastest sanity check on any breakdown.

B2: This row's value — moves as you copy the formula down.
SUM($B$2:$B$6): The grand total. The $ anchors keep it fixed on every row.

When to use it

Use it for any "what share of the whole" question: spend by category, revenue by product, tickets by team, survey answers by option.

Common mistakes

Forgetting the $ anchors on the total.
=B2/SUM(B2:B6) breaks when copied down — the total range shifts and the shares are wrong. Anchor it: SUM($B$2:$B$6).
The column shows 0.5 instead of 50%.
The math is fine; the cell just isn't formatted. Apply the percentage number format.
Shares don't add to 100%.
The total range is missing rows or includes extras. Make the SUM range match exactly the rows you're dividing.

Did this formula help?

Engine-verified against the sample data aboveLast reviewed 2026-07-08