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%.
E2has no dollar signs, so it moves to E3, E4… as you copy the formula down.$E$2:$E$11is 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.