How to show each row as a percentage of the total in Excel

This shows what slice of the whole each row makes up, the percentages that add up to a hundred across the column.

The formula

=B2/SUM($B$2:$B000) in column C

What each part does

B2: This row's value.
SUM($B$2:$B000): The total. The dollar signs stop the range moving as you fill down.

How it works, and what to watch for

Each row is divided by the total of the column. The total is locked with dollar signs so it stays the same all the way down.

Format the result as a percentage rather than multiplying by a hundred, so the underlying figure stays usable elsewhere.

If the percentages do not add to a hundred, the locked total has probably come loose. Check the dollar signs.

Questions

Why do my percentages not add up to 100?

The total in the denominator is shifting as you fill down. Lock it with dollar signs, like SUM($B$2:$B00), so every row divides by the same total.

Why 0.42 instead of 42%?

The number is right; the cell needs percentage formatting. Select it and press Ctrl+Shift+%.

The example sheet

Region	Sales	Share
North	4200
South	2800
East	3000

If this helped, these are close by

Build a running total — Where the dollar signs come in
A total for each name — Summary beside the raw rows
Weighted average — When some rows count for more

When this formula goes wrong

Excel formula returns #DIV/0! and how to hide it — Dividing by an empty or zero cell

More on SUM, and every walkthrough that uses it.

Formula Steps Watch how Excel formulas are built. Download the sheet.

Settings: separator, Excel version

Argument separator

Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.

Excel version

XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.