Formula for Percentage of Total in Excel

What this does

Percent of total is a share: one part divided by the sum of all parts. The part reference (B2) must move as you fill down, while the total range ($B$2:$B$5) must stay fixed, which is why it carries dollar signs. Formatting the result as Percentage multiplies the display by 100 without changing the value, so the shares always add up to 100%. It is not percentage change, which compares a new value with an old one. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. For “formula for percentage of total in excel”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected formula change, and only then apply the same setup to the full sheet. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That is what makes a calculation you can defend to a CFO or an auditor useful in real work: repeatable, auditable, and not dependent on memory or luck.

A worked example

Sales in B2:B5 are 120, 80, 200 and 100, a total of 500. In C2 enter =B2/SUM($B$2:$B$5) and fill down to C5, then apply Percentage format: C2 shows 24%, C3 16%, C4 40% and C5 20%, which together make 100%. Shares of a total make categories comparable regardless of size — which product, region or cost line carries the most weight — and they are the numbers behind every pie or 100% stacked chart. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.

In Google Sheets

If you are in Google Sheets rather than Excel, the good news is that the formula shown here is identical and the workflow barely changes — menus sit across the top instead of in a ribbon, and a few function names differ slightly, but anything you build here moves across with little or no rework. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Here is the takeaway for “formula for percentage of total in excel”: copy the answer if you are busy, but if you have a spare few minutes, rebuild the example in Excel yourself with the tool above open beside it. That single pass — type it, run it, watch the result move when you change an input — is what turns a formula you found into a technique you trust. Keep your inputs labelled and referenced, never hard-coded, and the same sheet stays correct and auditable as it grows. Done that way, you will not need to look this up again, and you will be the person others ask.

Common mistakes

• Leaving the total range unlocked, so it shifts as you fill down and the shares are wrong.
• Multiplying by 100 and also applying Percentage format, which shows 2400% instead of 24%.
• Using the percentage-change formula (New-Old)/Old, which answers a different question.
• Including the grand-total row inside the SUM range, which halves every share.

Frequently asked questions