Cellwise

Excel guide · 2 min read

How to calculate percentage of total in Excel

Divide 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 formula on this page was calculated live against this sheet.

ABC
1CategorySpendType
2Rent1800Fixed
3Groceries620Variable
4Utilities240Fixed
5Transport310Variable
6Dining out280Variable
7Savings750Fixed

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%.

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

Result: 0.45

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.

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

Result: 0.6975

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.

  1. Click inside the data and choose Insert > PivotTable, then OK.
  2. Drag Category to Rows and Spend to Values.
  3. Right-click any number in the Spend column and pick Show Values As > % of Grand Total.
  4. 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

Keep learning

Updated 2026-10-31