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

Q: 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.

Q: How does this differ from percent of row total?

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

Formula (Excel and Google Sheets)

=B2/SUM($B$2:$D$5)

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

Result on the example: one result per row in F2, filled down to H5
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in F2, filled down to H5. Change any input and the result updates, or edit the formula to experiment.

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.

=B2/SUM($B$2:$D$5)

Result0.06

B242
The value in B2.
$B$2:$D$54 rows × 3 cols: 42, 38, 51, 96, …
A range: 4 rows × 3 columns.
SUM($B$2:$D$5)700
SUM: Adds numbers and ranges. Skips text and blanks.
- number1$B$2:$D$54 rows × 3 cols: 42, 38, 51, 96, …
B2/SUM($B$2:$D$5)0.06
The / operator divides.

In Google Sheets

The same formula works in Google Sheets: =B2/SUM($B$2:$D$5). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=B2/SUM($B$2:$B$5,$C$2:$C$5,$D$2:$D$5)	6.0% 5.4% 7.3%, 13.7% 14.9% 12.6%, 4.3% 3.6% 5.1%…	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%.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate percent of grand total in a table

In F2, calculate January desk sales as a share of all units in the table, then fill across to column H and down to row 5.

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.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.