SheetSense

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.

fx
=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

  1. 01B2 is the revenue for this region, for example 12,500.
  2. 02SUM($B$2:$B$100) adds the whole column. If the total is 50,000, the formula returns 0.25.
  3. 03The $ signs lock the range, so every row you fill down divides by the same total.
  4. 04Format column C as Percentage to show 25%. The cell value stays 0.25.

The same logic in four tools

Excel
=B2/SUM($B$2:$B$100)
Google Sheets
=B2/SUM($B$2:$B$100)
SQL
SELECT region,
       revenue,
       1.0 * revenue / SUM(revenue) OVER () AS share
FROM revenue_by_region;
Python (pandas)
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.