# How to calculate percent of grand total in a table in Excel

Divide each cell by the sum of the whole table, fully locked: `=B2/SUM($B$2:$D$5)`, filled across and down.

**Excel:** `=B2/SUM($B$2:$D$5)` (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 |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Product | Jan | Feb | Mar |  | Jan % | Feb % | Mar % |
| Desks | 42 | 38 | 51 |  |  |  |  |
| Chairs | 96 | 104 | 88 |  |  |  |  |
| Lamps | 30 | 25 | 36 |  |  |  |  |
| Monitors | 55 | 61 | 74 |  |  |  |  |

Formula in F2, filled to H5: `=B2/SUM($B$2:$D$5)`

Result: 6.0%, 5.4%, 7.3%; 13.7%, 14.9%, 12.6%; 4.3%, 3.6%, 5.1%; 7.9%, 8.7%, 10.6%

## How it works

The grand total is the sum of the whole block, `$B$2:$D$5`: 700 units. Locking both rows and columns keeps it fixed wherever the formula is filled, while `B2` moves to each cell. All twelve percentages add up to 100%.

February chairs, at 104 units, are the biggest single cell: 14.9% of the quarter.

## Other ways

- `=B2/SUM($B$2:$B$5,$C$2:$C$5,$D$2:$D$5)`: The same total from separate columns, useful when the columns aren't next to each other.

## Common mistakes

- If the table has a total row or column, leave it out of the SUM, or the shares add up to 50%.
- Without full $ locking, the total range shifts as you fill and the shares stop adding to 100%.

## Questions

**How do I put the grand total in a cell first?**

Calculate it once, say in J1 with `=SUM(B2:D5)`, then use `=B2/$J$1`.

**How does this differ from percent of row total?**

Row total compares within a row (`$B2:$D2`); grand total compares with the whole table.

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-grand-total-in-a-table