How to calculate the percentage of a total in Excel
To show each item’s share of a total, divide the item by the total. The trick is making the total stay put when you fill the formula down.
A $ in front of the column letter and row number, as in $B$5, locks the reference. Without it, the total slides down to empty cells and you see #DIV/0!.
The formula
| A | B | |
|---|---|---|
| 1 | Region | Sales |
| 2 | North | 1,200 |
| 3 | South | 800 |
| 4 | East | 2,000 |
| 5 | Total | 4,000 |
=B2/$B$5Result: 30%
North made 30% of total sales. Fill the formula down to see each region’s share.
Step by step
- Click the cell next to the first item.
- Type =B2/ and click the total, then press F4 to add the dollar signs.
- Press Enter, then fill the formula down.
- Format the column as a percentage.
Other ways to write it
=B2/SUM($B$2:$B$4)Works out the total inside the formula, so you don’t need a total row.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- An unlocked total
- Without $, filling down turns B5 into B6, B7 and so on. Lock it as $B$5.
- Including the total in its own total
- If you use SUM, make sure the range stops above the total row, or every share is halved.
Reading isn’t the same as doing
Filling formulas down gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.