Percentage of Total in Google Sheets (Rows, Categories and the $ That Matters)

A row’s percentage of the total is its value divided by the sum of all the values. In Google Sheets that’s one short formula, but two details decide whether it’s right when you copy it down: the dollar signs around the total, and which cells the total includes. This guide shows the formula, the per-category version and the mistakes we reproduced, with the result Google Sheets gave each time.

The examples use an expense list with categories in B2:B11 and amounts in E2:E11. The amounts add up to 490.84.

B: Category C: Payee E: Amount
2 Food Grocer 54.20
3 Travel Rail 18.50
4 Food Bakery 6.80
5 Office Paper Co 32.00
6 Food Grocer 71.45
7 Travel Taxi 24.00
8 Office Printer Co 189.99
9 Food Café 12.30
10 Travel Rail 18.50
11 Food Grocer 63.10

Tested in Google Sheets. Every formula on this page was run on November 14, 2026, and the results below are the ones Google Sheets returned. How we test

The formula

In G2, then copied down to G11:

=E2/SUM($E$2:$E$11)

G2 returned 0.1104229484, which is 11.04%. The ten results added up to exactly 1, that is, 100%.

  • E2 has no dollar signs, so it moves to E3, E4… as you copy the formula down.
  • $E$2:$E$11 is locked, so every row divides by the same total.

To show the result as a percentage, select the column and choose Format > Number > Percent. With a “0.00%” format, G2 showed 11.04%, G3 3.77% and G8 38.71%. The value in the cell is still 0.1104…, so you can keep calculating with it.

What happens without the dollar signs

We copied =E2/SUM(E2:E11) down the same way:

Row With $E$2:$E$11 With E2:E11, copied down
2 11.04% 11.04%
3 3.77% 4.24%
8 38.71% 66.92%
10 3.77% 22.67%
11 12.86% 100%

Found in testing: the first row was right, so the mistake was easy to miss. From row 3 on, the range slid down with the formula (E3:E12, E4:E13…), so each row was divided by a smaller and smaller total. The last row was divided by itself and showed 100%. There was no error anywhere.

A total in a single cell needs the dollar signs too. With the total in E12, =E2/E12 copied to the next row becomes =E3/E13, and E13 is empty. Dividing by an empty cell returned #DIV/0!, Function DIVIDE parameter 2 cannot be zero. Write =E2/$E$12 instead.

One formula for the whole column

=ARRAYFORMULA(E2:E11/SUM(E2:E11))

This filled ten cells with the same results as the copied formula. No dollar signs are needed, because the formula isn’t copied. For a list that keeps growing, an open range with a filter for empty rows also matched, and left the empty rows blank:

=ARRAYFORMULA(IF(E2:E="", "", E2:E/SUM(E2:E)))

More on this pattern in our guide to ARRAYFORMULA with IF.

Percentage by category

To see what share of the total each category takes, divide a SUMIF by the total. For Food:

=SUMIF(B2:B11, "Food", E2:E11)/SUM(E2:E11)

returned 0.4234577459 (42.35%). With the category in a cell instead of “Food”, the same formula gave Travel 12.43% and Office 45.23%. Together they make 100%.

Put this in a small summary table, one row per category. If you add it as a column next to every expense instead, each Food row repeats 42.35%: those ten values added up to 3.39, or 339%, which means nothing.

Share within its own category

To compare each expense with its category rather than with the whole list, put the SUMIF in the denominator:

=E2/SUMIF($B$2:$B$11, B2, $E$2:$E$11)

Row 2 returned 26.08% (54.20 of Food’s 207.85), row 3 returned 30.33% of Travel, and row 8 returned 85.58% of Office. The dollar signs keep the ranges in place; B2 moves with each row. To add up categories with more than one condition, see SUMIFS with multiple criteria.

A total row inside the range

If the total sits at the bottom of the column and the SUM range includes it, every percentage comes out at half its value. In a small test, three values (30, 50 and 20) with their total, 100, in the row below:

Formula for the first value Result
Range without the total row 0.30 (30%)
Range including the total row 0.15 (15%)

The total was counted twice, once as the three values and once as itself. End the range on the last value, or keep the total outside the column.

Rounded percentages that add up to 99%

Three equal values are 33.333…% each. Rounded to two decimals with =ROUND(AB2/SUM(AB$2:AB$4), 2), each returned 0.33, and the three added up to 0.99. Rounding each row on its own loses the remainder.

  • If you only need the display rounded, keep the full value and use a percent format with fewer decimals. The sum stays exactly 100%.
  • If the rounded values must be stored, and must add up to 100%, you’ll need to adjust one of them by hand, usually the largest.

When the total is 0 or empty

Formula Result
=5/SUM(AE2:AE4), empty range #DIV/0!, Function DIVIDE parameter 2 cannot be zero.
=IF(SUM(AE2:AE4)=0, "", 5/SUM(AE2:AE4)) (empty)
=IFERROR(5/SUM(AE2:AE4), 0) 0

The IF version only hides this one case. IFERROR also hides every other error in the formula, such as a typo in a range, so use it carefully. Our guide to IFNA and IFERROR explains the difference.

TO_PERCENT, TEXT and multiplying by 100

Formula Shows What’s in the cell
=TO_PERCENT(G2) 11% The number 0.1104…
=TEXT(G2, "0.0%") 11.0% Text: ISTEXT returned TRUE
=E2/SUM(E2:E11)*100 11.04229484 A plain number, without the % sign

TO_PERCENT applies a percent format and keeps the number. TEXT is useful inside a sentence, such as ="Food is "&TEXT(…, "0%")&" of the total", but its result is text, and text is skipped by SUM. Multiplying by 100 gives a number to read as a percentage; don’t also apply a percent format to it.

Related guides