Percentage of Total in Excel: Formula and $ Signs
· 6 min read
To get a percentage of total in Excel, divide each value by the total and lock the total cell with $ signs so it stays put when you copy the formula down. Then format the column as a percentage. If the values are in B2:B4 and the total is in B5, the formula in C2 is:
=B2/$B$5Fill it down to C4, select C2:C4 and press Ctrl+Shift+% (or click the % button on the Home tab). The shares should add up to 100%.
Worked example
Sales by product category for one quarter:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Category | Revenue | Share | No total row | Total row included |
| 2 | Office | 72,000 | 45.0% | 45.0% | 22.5% |
| 3 | Kitchen | 48,000 | 30.0% | 30.0% | 15.0% |
| 4 | Cleaning | 40,000 | 25.0% | 25.0% | 12.5% |
| 5 | Total | 160,000 | 100.0% | 100.0% | 50.0% |
B5 holds =SUM(B2:B4). C2 holds =B2/$B$5, filled down to C4:
- C2: 72,000 / 160,000 = 0.45, shown as 45%
- C3: 48,000 / 160,000 = 0.30, shown as 30%
- C4: 40,000 / 160,000 = 0.25, shown as 25%
The result of the division is a fraction (0.45). The percentage format only changes how it is displayed. Do not multiply by 100 as well, or 0.45 becomes 45, which the % format shows as 4500%.
Why the $ signs matter: absolute reference
When you copy a formula down, Excel shifts every cell reference by one row. That is what you want for the value (B2, then B3, then B4) but not for the total. Here is what happens to =B2/B5 without $:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Revenue | Without $ | With $ |
| 2 | Office | 72,000 | 45% | 45% |
| 3 | Kitchen | 48,000 | #DIV/0! | 30% |
| 4 | Cleaning | 40,000 | #DIV/0! | 25% |
| 5 | Total | 160,000 | ||
| 6 | ||||
| 7 |
B6 and B7 are empty, and dividing by an empty cell is dividing by zero. A reference with $ in front of both the column and the row, like $B$5, is an absolute reference. It does not move when copied. A reference without $, like B2, is a relative reference.
How to lock a cell in a formula
While typing or editing the formula, click on the total cell reference and press F4. Each press cycles through the four options:
| Press F4 | Reference | What stays fixed |
|---|---|---|
| once | $B$5 | column and row |
| twice | B$5 | row only |
| three times | $B5 | column only |
| four times | B5 | nothing |
On a Mac, the shortcut is Cmd+T, or Fn+F4 on some keyboards.
B$5 (a mixed reference) is enough if you only fill down, because only the row changes. $B$5 is the safe default when you are not sure.
Other ways to write the excel formula for percentage of total
Without a total row
If there is no total cell, put the SUM inside the formula and lock the whole range:
=B2/SUM($B$2:$B$4)Same results: 45%, 30%, 25% (column D of the first sheet). Make sure the range covers only the data rows, not a total row.
In an Excel table
If the data is formatted as a table (Ctrl+T) with a column called Revenue, structured references do not need $ signs at all:
=[@Revenue]/SUM([Revenue])[@Revenue] means "this row" and [Revenue] means "the whole column", so the formula copies correctly by itself.
Share of a group from raw rows
Often you have one row per order and want each region's share. Combine SUMIF with SUM. With regions in A2:A7, amounts in B2:B7, and region names in D2:D5:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Amount | Region | Share | AI's formula | |
| 2 | North | 300 | North | 25.0% | 25.0% | |
| 3 | West | 500 | West | 30.0% | 35.3% | |
| 4 | North | 200 | East | 20.0% | 33.3% | |
| 5 | East | 400 | South | 25.0% | 50.0% | |
| 6 | West | 100 | Total | 100.0% | 143.6% | |
| 7 | South | 500 |
=SUMIF($A$2:$A$7, D2, $B$2:$B$7) / SUM($B$2:$B$7)Filled down for North, West, East and South this gives 25%, 30%, 20% and 25%, which add up to 100%. Every range is locked except D2, which should move to the next region name. For more on SUMIF and SUMIFS, see SUMIFS with multiple criteria.
In a pivot table
Put the category in Rows and revenue in Values. Right-click a value, choose Show Values As, then % of Grand Total. Use % of Column Total or % of Parent Row Total when you want shares within a group. No formulas needed, and it updates when you refresh the pivot.
Common mistakes
Forgetting to lock the total. The #DIV/0! errors in the second sheet above are the friendly version, because you see them. The unfriendly version is a formula that keeps working but points at the wrong cells.
Locked in one place, unlocked in another. AI assistants often get the first row right and then fill down a SUMIF without $ on its ranges. Take the region example above and write =SUMIF(A2:A7, D2, B2:B7)/SUM(B2:B7) in F2, as in column F of the region sheet above. Filled down, the ranges slide: F3 looks at rows 3 to 8, F4 at rows 4 to 9. The results become 25%, 35.3%, 33.3% and 50%. No errors, and every number looks like a reasonable percentage. They add up to about 144%.
The check that catches it in seconds: add up the shares. Percentages of a total must sum to 100% (allowing for rounding in the display). Then click the last cell of the column and read its formula. If the ranges have moved, they need $.
Including the total in the denominator. =B2/SUM($B$2:$B$5) adds the Total row to itself, so the denominator is 320,000 and the shares come out as 22.5%, 15% and 12.5% (column E of the first sheet). They add up to 50%, which is the giveaway.
Multiplying by 100 and formatting as %. Pick one. Either keep the fraction and use the % format, or multiply by 100 and use a plain number format with a "%" in the header.
Rounded shares that do not add to 100%. If you show whole percentages, three shares of 33.3% each display as 33%, 33% and 33%, which seems to add to 99%. The underlying values are fine. If a report must add to exactly 100, round deliberately with ROUND and adjust the largest share, and say so in a note. See how to round to 2 decimal places for how ROUND differs from formatting.
FAQ
What is the formula for percentage of total in Excel?
=value/total, with the total locked by $ signs, for example =B2/$B$5. Format the result as a percentage.
How do I lock a cell in a formula?
Click the reference in the formula and press F4 until it shows $B$5. You can also type the $ signs yourself.
What is the difference between absolute and relative references?
A relative reference (B2) moves when you copy the formula. An absolute reference ($B$2) always points at the same cell. A mixed reference ($B2 or B$2) locks only the column or only the row.
Why does my percentage formula show #DIV/0!?
The formula is dividing by an empty cell or a zero. Usually the total cell was not locked and the reference moved down when you filled the formula. Add $ signs to the total.
How do I show percentage of total in a pivot table?
Right-click a number in the Values area, choose Show Values As, then % of Grand Total.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Each region’s share” →