How to Calculate Percentage of Total in Excel

To find the percentage of total in Excel, divide each value by the total, lock the total with $ signs, and format the result as a percentage. It answers questions like "what share of sales came from each region?" or "what part of the budget goes on rent?". This guide shows the formula step by step, the one mistake that breaks it, and quicker options for big tables, including SUMIF and pivot tables.

The percentage of total formula#

The formula is the part divided by the whole:

=value/total

All the examples use this sales table. The total in B6 is =SUM(B2:B5):

How to calculate percentage of total step by step#

1. Click C2 and type:

   Excel formula

   =B2/$B$6

2. Press Enter. The result is 0.3.

3. Select C2:C5 and press Ctrl+D (⌘D on a Mac) to fill down.

4. With C2:C5 still selected, press Ctrl+Shift+% (Control+Shift+% on a Mac) to show percentages.

You get 30% for North, 21% for South, 24% for East and 25% for West. Click Increase Decimal on the Home tab to see the exact values: 21.25% and 23.75%. Excel stores a percentage as a decimal, so never multiply by 100. The guide to the percentage formula in Excel explains this and four other everyday percentage calculations.

Why you need the $ signs#

The $ signs lock the total so it doesn't move when you fill the formula down. Without them, C2 has =B2/B6, which is right. But C3 gets =B3/B7, and B7 is empty, so you see #DIV/0!. The rows below fail the same way.

With $B$6, every row divides by the same total: C3 becomes =B3/$B$6, C4 becomes =B4/$B$6, and so on. While typing, put the cursor on B6 and press F4 to add the signs (fn+F4 or ⌘T on a Mac). The relative vs absolute references lesson practises exactly this.

Percentage of total without a total row#

If you don't want a total row, put SUM inside the formula:

=B2/SUM($B$2:$B$5)

North still returns 30%. Lock the whole range with $, or it shifts down as you fill. This version is also handy when the total would get in the way, for example when you plan to sort the table. The SUM function guide covers range tips.

How to check your percentages add up to 100%#

Add the percentage column: =SUM(C2:C5) should return 100%. If it doesn't, check two things.

• Rounding. Each share is rounded for display, so the visible numbers can add up to 99% or 101%. Three equal shares each show 33%, which looks like 99%, but the stored values still add to exactly 100%. Show more decimals to confirm.
• A wrong total. If the shares add to about 50%, the total probably counts the data twice, for example a SUM that includes a subtotal. If they add to more than 100%, the total misses some rows. The guide on fixing a SUM that gives the wrong total walks through the causes.

How to find a category's share of the total#

When the same category appears on many rows, total it with SUMIF first. Take this order list in A1:B6:

North's share of all orders is:

=SUMIF(A2:A6,"North",B2:B6)/SUM(B2:B6)

That is 1,200 / 2,000 = 60%. Put the region name in a cell and point the formula at it to build a summary table for every region.

Percentage of total in an Excel Table#

If your data is an Excel Table (Ctrl+T, or ⌘T on a Mac), you don't need $ signs at all. Use structured references, which name the column instead of the cells:

=[@Sales]/SUM([Sales])

[@Sales] means the Sales value in the same row, and SUM([Sales]) is the total of the whole column. Excel fills the formula down the table automatically, and new rows you add are included in the total. Don't add a total row inside the Sales column itself, or SUM would count it; use the table's built-in Total Row instead, which sits outside the data.

Percentage of total in a pivot table#

For big tables, a pivot table does the whole job without formulas. Build a pivot with the category in Rows and the amount in Values, then right-click any value and choose Show Values As > % of Grand Total. Each row shows its share, and the grand total shows 100%.

Add the amount field a second time to see the number and the percentage side by side. The pivot table percent of total guide covers the other options, such as % of Column Total and % of Parent Total.

Running percentage of total#

A running (cumulative) percentage shows how much of the total you have reached so far, row by row. Lock only the start of the range:

=SUM($B$2:B2)/$B$6

Filled down, it returns 30%, 51.25%, 75% and 100% for the sales table above. This is useful for ranking reports, such as "which regions make up the first 75% of sales?". The guide to running totals in Excel explains the anchored range. To go the other way and grow a value by a percentage, see how to increase a number by a percentage.

Practise it#

Percentage of total combines two tested skills: dividing correctly and locking a cell with $. Practise percentages in a live spreadsheet, then lock a fixed total in the relative vs absolute references lesson. You type each formula and it is checked instantly. Try the regional-share exercise on this page first.