Excel guide · 2 min read

How to calculate percentage of total in Excel

Q: How do I calculate percentage of total in Excel?

Use =value/total with the total locked, such as =B2/SUM($B$2:$B$7), then format as Percent. Each row shows its share of the whole.

Q: Can I do it for the whole column at once?

In Excel 365 and 2021, =B2:B7/SUM(B2:B7) spills a percentage for every row in one formula. In older versions, use the locked formula and fill down.

Quick answerDivide each value by the total and lock the total with dollar signs: =B2/SUM($B$2:$B$7). Fill it down, then format the column as Percent. The shares will add up to 100%.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C
1	Category	Spend	Type
2	Rent	1800	Fixed
3	Groceries	620	Variable
4	Utilities	240	Fixed
5	Transport	310	Variable
6	Dining out	280	Variable
7	Savings	750	Fixed

Method 1: Divide by a locked total

This monthly budget totals 4,000. To see what share rent takes, divide rent by the total. The dollar signs in $B$2:$B$7 keep the total range fixed when you fill the formula down to the other rows. Only B2 changes to B3, B4 and so on.

Format column D as Percent. Rent shows 45%, Groceries 15.5% (with one decimal), and the column sums to 100%.

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

Result: 0.45 shows as 45% with Percent format

Returns 0.45, so rent is 45% of the total.

Method 2: Point at a total cell

If you already have a total row, say =SUM(B2:B7) in B8, divide by that cell instead: =B2/$B$8. It is easier to read and the total only gets calculated once. Just make sure the total row is not inside the range you are dividing, or it will show as 100% of itself.

Method 3: Percentage of total for a group

To find what share a whole category takes, add up the group with SUMIF and divide by the grand total. Here the fixed costs (rent, utilities, savings) are compared with all spending.

=SUMIF(C2:C7,"Fixed",B2:B7)/SUM(B2:B7)

Result: 0.6975 shows as 69.8% with Percent format

Returns 0.6975, so fixed costs are about 69.8% of the budget.

Method 4: PivotTable "% of Grand Total"

For large lists a PivotTable does this without any formulas, and it updates when you refresh.

Click inside the data and choose Insert > PivotTable, then OK.
Drag Category to Rows and Spend to Values.
Right-click any number in the Spend column and pick Show Values As > % of Grand Total.
Drag Spend into Values a second time if you want the amount and the percentage side by side.

Common mistakes

Forgetting the dollar signs

Without them, the total range slides down as you fill (B3:B8, B4:B9...) and the percentages go wrong or hit #DIV/0!. Press F4 after selecting the range to add $ signs.

Including the total row in the range

If B8 holds the total and you divide by SUM(B2:B8), every share is halved. Sum only the item rows.

Percentages that add to 99% or 101%

That is display rounding, not a math error. Add a decimal place, or accept it and note that figures are rounded.

FAQ

How do I calculate percentage of total in Excel?

Use =value/total with the total locked, such as =B2/SUM($B$2:$B$7), then format as Percent. Each row shows its share of the whole.

How do I get percentage of total in a PivotTable?

Right-click a value in the PivotTable, choose Show Values As, then % of Grand Total. Use % of Column Total or % of Parent Row Total for subgroup shares.

Can I do it for the whole column at once?

In Excel 365 and 2021, =B2:B7/SUM(B2:B7) spills a percentage for every row in one formula. In older versions, use the locked formula and fill down.

Functions used

Related guides

Updated October 31, 2026