Percent of total with an anchored SUM
Dividing a value by SUM of its whole column, with the range locked using $ signs, gives each row's share of the total, which you format as a percentage.
=B2/SUM($B$2:$B$100)The sheet: Revenue sheet: column A is Region and column B is Revenue, with no total row in the range. You want each region's share of total revenue in column C.
How it works, step by step
- 01B2 is the revenue for this region, for example 12,500.
- 02SUM($B$2:$B$100) adds the whole column. If the total is 50,000, the formula returns 0.25.
- 03The $ signs lock the range, so every row you fill down divides by the same total.
- 04Format column C as Percentage to show 25%. The cell value stays 0.25.
The same logic in four tools
=B2/SUM($B$2:$B$100)=B2/SUM($B$2:$B$100)SELECT region,
revenue,
1.0 * revenue / SUM(revenue) OVER () AS share
FROM revenue_by_region;import pandas as pd
revenue["share"] = revenue["revenue"] / revenue["revenue"].sum()Mistakes that break it
- !Without the $ signs the SUM range slides down as you fill the formula, so each row is divided by a smaller and different total.
- !If the range includes a total row, the total is counted twice and every share comes out at half its real value.
- !A total of 0 returns #DIV/0!. Wrap the formula in IFERROR if the column can be empty.
- !In SQL, dividing two integer columns can round down to 0. Multiply by 1.0 or cast one side to numeric first.
Common questions
- How do I show the value as a percentage?
- Select the cells and apply the Percentage format, or press Ctrl+Shift+5 in Excel. Do not multiply the formula by 100.
- How do I calculate percent of total within a group?
- Use SUMIF to get the group total: =B2/SUMIF($A$2:$A$100,A2,$B$2:$B$100). In SQL use SUM(revenue) OVER (PARTITION BY region), and in pandas use groupby with transform("sum").
- Why do my percentages add up to more than 100%?
- The range probably includes a subtotal or total row, or some rows are counted twice in the data. Check what the SUM range covers.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.