# How to calculate the percentage of a total in Excel

Divide each value by the total, with the total locked: =B2/SUM($B$2:$B$7), or =B2/$B$8 if the total is in a cell.

**Excel:** `=B2/SUM($B$2:$B$7)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Channel | Revenue | Share |
| Online | 182000 |  |
| Retail | 126500 |  |
| Wholesale | 64500 |  |
| Partners | 21000 |  |
| Events | 6000 |  |
| Other | 0 |  |

Formula in C2, filled to C7: `=B2/SUM($B$2:$B$7)`

Result: 45.5%; 31.6%; 16.1%; 5.3%; 1.5%; 0.0%

## How it works

A share is part divided by whole. The whole, `SUM($B$2:$B$7)`, is locked with $ so it stays the same in every row while `B2` moves down. Formatted as percentages, the shares add up to 100%.

## Other ways

- `=B2/SUM(B$2:B$7)`: Locking only the rows is enough for a single column.

## Common mistakes

- Without $, filling down shrinks the total range and the shares stop adding to 100%.
- Rounded percentages can add to 99.9% or 100.1%. That's rounding, not an error.

## Questions

**How do I show the share of a category with several rows?**

Use SUMIF for the part: `=SUMIF(A2:A20,"Online",B2:B20)/SUM(B2:B20)`.

**How do I get the percentage of a grand total in a pivot table?**

Use Show Values As → % of Grand Total in the value field settings.

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

Source: https://spreadsheetpractice.com/formulas/percentage-of-total