# How to calculate percent of row total in Excel

Divide each cell by its row's total with the columns locked: `=B2/SUM($B2:$E2)`, filled across and down.

**Excel:** `=B2/SUM($B2:$E2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G | H | I | J |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Region | Q1 | Q2 | Q3 | Q4 |  | Q1 % | Q2 % | Q3 % | Q4 % |
| East | 120 | 135 | 150 | 95 |  |  |  |  |  |
| West | 80 | 90 | 110 | 120 |  |  |  |  |  |
| North | 60 | 60 | 60 | 70 |  |  |  |  |  |
| South | 150 | 140 | 100 | 110 |  |  |  |  |  |

Formula in G2, filled to J5: `=B2/SUM($B2:$E2)`

Result: 24.0%, 27.0%, 30.0%, 19.0%; 20.0%, 22.5%, 27.5%, 30.0%; 24.0%, 24.0%, 24.0%, 28.0%; 30.0%, 28.0%, 20.0%, 22.0%

## How it works

`$B2:$E2` has the columns locked but not the row. Filled across, it stays on B to E, so every quarter is divided by the same row total; filled down, it moves to the next region. Each row of percentages adds up to 100%.

East did 30% of its year in Q3.

## Other ways

- `=B2/($B2+$C2+$D2+$E2)`: Adds the quarters explicitly, which also works when the columns to total aren't next to each other.

## Common mistakes

- Writing `$B$2:$E$2` locks the row too, so every region is divided by East's total.
- Leaving the $ off the columns makes the range slide right as you fill across.

## Questions

**How do I calculate percent of column total instead?**

Lock the rows instead of the columns: `=B2/SUM(B$2:B$5)`. Each quarter's regions then add to 100%.

**Can a pivot table do this?**

Yes: Show Values As, % of Row Total.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/percent-of-row-total