How to Calculate Percentage of Total in Excel: Formula, PivotTable and Examples

shape
shape
shape
shape
shape
shape
shape
shape
How to Calculate Percentage of Total in Excel: Formula, PivotTable and Examples - featured image

A percentage of total shows how big each part is compared with the whole. It answers questions such as what share of sales each product made, what part of the budget goes to rent, or how many of the tasks are finished. In Excel you calculate it by dividing each value by the total and formatting the result as a percentage. The only trick is to lock the total cell with a dollar sign, so the formula can be copied down. This guide shows the basic formula with a worked example, how to use SUM and SUMIF for the total, how to do it in a PivotTable, how to show a running percentage, how to check that the parts add up to 100 percent, and the common mistakes.

Updated November 2026

In this guide

  1. The formula
  2. Example data
  3. Step 1: calculate the total
  4. Step 2: divide each value by the total
  5. Step 3: format as a percentage
  6. Step 4: check that the shares add up to 100%
  7. Percentage of total with SUMIF
  8. Percentage of total in a PivotTable
  9. Running percentage of total
  10. Percent complete and percent of target
  11. Show percentages in a chart
  12. Rounding and the 100% problem
  13. Percentage of total across a row
  14. Common mistakes
  15. Practice problems
  16. Quick recap

The formula

The percentage of total is the part divided by the total, multiplied by 100 if you want the number out of 100. In Excel you do not need to multiply by 100, because the percent format does it for you. The formula is =part/total. If the part is in B2 and the total is in B7, use =B2/B7 and then apply the Percent Style from the Home tab. If B2 is 450 and B7 is 1,000, the result is 0.45, which Excel displays as 45%. The formula shows the same value as 45 divided by 100.

Example data

A shop sold four products last month.

ProductSales
Laptops450
Phones300
Tablets150
Accessories100
Total1,000

The total of 450, 300, 150 and 100 is 1,000. We will calculate the share of each product. By hand, laptops are 45 percent, phones 30 percent, tablets 15 percent and accessories 10 percent, and the shares add up to 100 percent.

Step 1: calculate the total

Put the product names in A2:A5 and the sales in B2:B5. In B6 type =SUM(B2:B5), which gives 1,000. A separate total cell is a good idea, because every share refers to it. If the data grows, convert the list to a table with Ctrl+T, as explained in our table guide, and the total can use the table column. Make sure the total does not include itself or any subtotal, or the percentages will be wrong.

Step 2: divide each value by the total

In C2, type =B2/B6 and press Enter. The result is 0.45. This works for the first row, but if you copy it down, the reference to B6 moves to B7 and the formula returns an error or a wrong result. To prevent this, lock the total with dollar signs: =B2/$B$6. Press F4 after typing B6 to add them. Now copy the formula down to C5. The other cells show 0.3, 0.15 and 0.1.

Step 3: format as a percentage

Select C2:C5 and click the Percent Style button on the Home tab, or press Ctrl+Shift+5. The values show as 45%, 30%, 15% and 10%. Use the Increase Decimal button if you want one or two decimals, such as 45.0%. Remember that formatting only changes the display. The cell still contains 0.45. If you format the cells as percent before typing, Excel may treat a typed 45 as 45 percent, which can confuse a formula, so type the formula first.

Step 4: check that the shares add up to 100%

Add a total in C6 with =SUM(C2:C5). It should show 100%. This is a quick check that the formula and the total are correct. If you see 99% or 101%, the shares have been rounded for display, and the exact values may still add to 1. If you see a very different number, such as 200%, the total in the denominator includes something that it should not. Our percent change guide shows other percentage checks.

Percentage of total with SUMIF

When the data has many rows and repeated categories, use SUMIF to get the total of each category. Suppose column A has the product for each sale, and column B has the amount. The share of laptops is =SUMIF(A2:A100,"Laptops",B2:B100)/SUM(B2:B100). If laptops total 450 and all sales total 1,000, the result is 45%. Put the category name in a cell and refer to it, as in =SUMIF($A$2:$A$100,E2,$B$2:$B$100)/SUM($B$2:$B$100), so you can fill the formula down a list of categories. Our SUMIFS guide explains the function.

Percentage of total in a PivotTable

A PivotTable can show the percentage of total without a formula. Create a PivotTable with Product in the rows and Sales in the values. Right-click a value, choose Show Values As, and then % of Grand Total. The PivotTable shows 45%, 30%, 15% and 10%, and a grand total of 100%. You can also choose % of Column Total, % of Row Total or % of Parent Total for more detailed views. This method is perfect for large lists, because the percentages update when you refresh the PivotTable.

Running percentage of total

A cumulative percentage shows the share of the total reached so far. In D2 use =SUM($B$2:B2)/$B$6, which divides the running total by the grand total. Fill it down. The results are 45%, 75%, 90% and 100%. Sorting the products from the highest to the lowest before you do this gives the basis of a Pareto analysis, where a few items make most of the total. In our example, laptops and phones make 75 percent of the sales.

Percent complete and percent of target

The same formula works for progress. If a team has finished 18 of 24 tasks, the percent complete is 18 divided by 24, which is 75%. If a salesperson has sold 8,400 against a target of 12,000, the percent of target is 8,400 divided by 12,000, which is 70%. Use conditional formatting to color the cell red below 50%, yellow below 80% and green above, as explained in our conditional formatting guide. Percentages make progress easy to compare across people and projects.

Show percentages in a chart

A pie chart is a natural way to display shares, as explained in our pie chart guide. For more than five categories, a bar chart is easier to read. You can chart either the values or the percentages. If you chart the percentages, make sure they add up to 100 percent. Sort the categories from the largest to the smallest, and put the labels in the chart, so the reader sees the share of each part at a glance.

Rounding and the 100% problem

When you round percentages to whole numbers, the displayed values may add up to 99% or 101%. For example, three shares of 33.33 percent display as 33% each and add up to 99%. This is normal and not an error. If you need the displayed numbers to add up exactly, adjust the largest value, or show one decimal. Do not use ROUND in the formula unless you really need rounded values, since rounding early can make later calculations less accurate. Our rounding guide explains the options.

Percentage of total across a row

Sometimes the data is arranged in columns for months, and you want each month as a share of the yearly total of one product. Put the months in B2:M2 and the total in N2, then use =B2/$N2. The dollar sign before N locks the column, and the row stays relative, so you can copy the formula across and down. For a quarter with sales of 120, 130 and 150, the total is 400, and the shares are 30%, 32.5% and 37.5%. Mixed references like this save a lot of time in larger tables.

Common mistakes

The most common mistake is to forget the dollar signs, so the total reference moves when you copy the formula. Another is to divide by the wrong total, such as a subtotal. A third is to include the total row in the SUM, which doubles the total. A fourth is to type the percent sign inside the formula, such as 45%, and mix it with decimals. A fifth is to forget to format the cells as percent, so they show 0.45. A sixth is to divide by zero when the total is empty, which you can handle with IFERROR.

Practice problems

Try these. Problem 1: 150 out of 600 is 25 percent. Problem 2: if rent is 900 of a budget of 3,000, the share is 30 percent. Problem 3: in the example, the share of tablets is 15 percent. Problem 4: write a running percentage formula and check that the third product reaches 90 percent. Problem 5: if a salesperson sold 8,400 of a 12,000 target, what percent is that, and how much is left? 70 percent, and 3,600 is left.

Quick recap

Divide each part by the total and format the result as a percentage. Lock the total with dollar signs so the formula can be copied, and check that the shares add up to 100 percent. Use SUMIF for repeated categories, a PivotTable with Show Values As for large lists and a running sum for cumulative shares. Use the same idea for progress and targets. Watch for the common mistakes with references, totals and formats, and the percentages will always be correct.

Frequently asked questions

How do I calculate the percentage of a total in Excel?

Divide the part by the total and format the result as a percentage.

Why do I need dollar signs in the formula?

They lock the total cell so the formula can be copied down.

How do I show percent of total in a PivotTable?

Use Show Values As and choose % of Grand Total.

Why do my percentages add up to 99%?

Rounding in the display causes it.

How do I calculate a running percentage?

Divide the running sum by the grand total.

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *