How to Calculate Percentage of Total (Excel, Sheets, Pivot, Tableau)

The short answer

Divide the part by the whole, then multiply by 100. An order worth $4,750 out of $19,000 of total sales is 4,750 ÷ 19,000 = 0.25, and 0.25 × 100 = 25%. That is the entire calculation; everything else is presentation.

In a spreadsheet the same thing is one formula: =B2/SUM($B$2:$B$6). The division returns 0.25, and if you format the cell as a percentage it displays 25% without you multiplying by 100 anywhere. If you leave it as plain number format, then do multiply by 100, because 0.25 on its own is not 25.

• By hand: part ÷ whole × 100.
• Excel or Google Sheets: =B2/SUM($B$2:$B$6), formatted as a percentage.
• Pivot table: right-click the value and choose Show Values As → % of Column Total.
• Tableau: SUM([Sales]) / TOTAL(SUM([Sales])), displayed as a percentage.
• Two numbers and no spreadsheet: the browser calculator linked at the bottom of this page, mode 2.

What a percentage of total actually is

“Percent” means “per hundred”, so a percentage is just a ratio rescaled so that the whole becomes 100. The division part ÷ whole gives you the ratio as a fraction of one — 0.25 means the part is a quarter of the whole — and multiplying by 100 only changes the units you read it in. Because it is a ratio, a percentage of total has no unit of its own: it is the same whether the amounts are dollars, tonnes, or rows in a table.

The useful property is comparability. A $3,000 line item tells you nothing about importance on its own, but “20% of total sales” places it against everything else, and lets you compare a small region's mix with a large region's mix even though the underlying amounts differ by an order of magnitude. This is why “share” and “percentage of total” mean the same thing in reports.

The catch is that the denominator decides the meaning. The part has to be inside the whole, and both have to cover the same scope and the same period. $4,750 as a share of one quarter's $19,000 is 25%; the same order against $60,000 of annual sales is 7.92%. Both numbers are correct arithmetic and only one answers your question, so the real work is choosing the total deliberately: the rows, the dates, and the filter that the total is supposed to represent.

One more distinction that causes endless arguments: a percentage of total describes a share at a point in time, while a percentage change compares the same thing across two points in time. If share moves from 20% to 25%, that is a 5 percentage-point rise in share — but a 25% increase in the share itself. Reporting the second number as “up 25%” is how a composition change gets mistaken for growth.

The arithmetic, worked

42 correct answers out of 48 questions: 42 ÷ 48 = 0.875, and 0.875 × 100 = 87.5%. A $2,150 line in an $8,640 cost base: 2,150 ÷ 8,640 = 0.24884, which is 24.88% to two decimals. A $4,750 sale against a $3,800 target: 4,750 ÷ 3,800 = 1.25, so 125% of target — percentages above 100 are perfectly normal when the “total” is a plan rather than a sum of the parts.

When you are doing several of these against the same total, invert the order once and reuse a multiplier. With a total of 15,000, every value's share is the value × (100 ÷ 15,000), and 100 ÷ 15,000 = 0.0066667. So 3,000 × 0.0066667 = 20%, and 2,750 × 0.0066667 = 18.33%. That is the same arithmetic a spreadsheet runs, and it is also a fast sanity check: if your hand estimate and your formula disagree, the denominator is where to look.

Rounding is a reporting choice, not a correction. Round to two decimals for money-derived shares and to one for headcounts; whatever you pick, do the rounding only at the end. Rounding each part and then summing is how a column of shares ends at 99.99% or 100.02%.

In Excel

Excel has no dedicated “percent of total” command on a normal grid; you write the division yourself. What you need is the part in one cell and the whole in another, where the whole is normally a SUM over a fixed range.

The one thing to get right before you type anything is which range is the whole. Anchoring that range with dollar signs is what lets you fill the formula down a column without each row quietly dividing by a different total, and it is the difference between a share column you can trust and one you have to re-check cell by cell.

The formula against a column total

Say sales figures sit in B2:B6. Put =SUM(B2:B6) in B7 to get the whole, then in C2 write =B2/SUM($B$2:$B$6). The dollar signs anchor the range so it stays B2:B6 when you copy the formula down; the numerator B2 is deliberately relative so it becomes B3, B4, B5 and B6 in the rows below. Copy the cell down, and you have the share of each row.

Then select the column and press Ctrl+Shift+Percent to apply Percent Style, which multiplies the displayed value by 100 and appends the sign without changing the stored number. If you would rather keep a plain number, write =B2/SUM($B$2:$B$6)*100 instead — but never both. Applying Percent Style to a number you already multiplied by 100 turns 25 into 2,500%.

When the total is already a row or a column

If a grand total already exists — B7 in the example above — divide by that cell rather than re-summing: =B2/$B$7. Reference the total cell, because if the total is calculated from a filtered subset or includes adjustments, re-summing the visible rows silently gives you a different denominator.

For a row of months across columns, the shape is identical with the range running sideways: =B2/SUM($B2:$G2) gives each month's share of that row's total. The anchor goes on the column letters, not the row number, so copying the formula down keeps each row pointing at its own total.

Percentage of total cost

Cost analysis is the same formula, and it is where the denominator choice bites. Take a month with rent 2,400, labour 5,100, materials 3,300 and shipping 1,200: those add to 12,000, so the shares are 2,400 ÷ 12,000 = 20%, 5,100 ÷ 12,000 = 42.5%, 3,300 ÷ 12,000 = 27.5%, and 1,200 ÷ 12,000 = 10%, which sum to exactly 100%.

Now suppose a vendor credit of −1,200 is in the same column. The total becomes 10,800, every other share grows, and the credit itself shows as a negative percentage — arithmetically correct, but it means “share of total” no longer describes a composition. Net totals with negative components are exactly where you should expect values beyond 100% or below 0%, and it is worth reporting the gross total and the net total separately.

In Google Sheets

Sheets uses the same formulas character for character, so =B2/SUM($B$2:$B$6) works there too, and the same anchoring rule applies. The differences are in the formatting menu and in a couple of features that make a shared denominator cleaner to manage.

Because the arithmetic is identical, a share column that works in Excel works in Sheets without edits — which means the Excel mistakes travel too. An unanchored SUM range filled downward and a Percent Style applied on top of a manual ×100 are the two that show up most often in Sheets, where people are more likely to type the formula into one cell and drag it without checking.

Formatting the result

Choose Format → Number → Percent, or press Ctrl+Shift+5 (Cmd+Shift+5 on a Mac), to display 0.25 as 25%. Percent Style in Sheets is a display setting like Excel's: the cell still holds 0.25, so percentages of anything you have already scaled by 100 come out a hundred times too large.

For a whole column at once, wrap the division in an ARRAYFORMULA: =ARRAYFORMULA(B2:B6/SUM(B2:B6)). The SUM has no anchors but it does not need them, because ARRAYFORMULA evaluates the division as one operation instead of copying a formula down row by row. Put the formula in C2 and the five shares fill C2:C6.

Denominators that follow a filter

Sheets filter views hide rows without deleting them, and SUM keeps counting the hidden rows. If the question is “what share of what I am currently looking at is this row?”, use =B2/SUBTOTAL(109,$B$2:$B$6): function number 109 sums only the visible rows, so the denominator tracks the filter and the column re-shares itself whenever you filter.

The alternative is to give the total a name and divide by that instead of by a range. Put the SUM in one cell, define a named range for it, and every share formula reads =B2/Total_Sales — which is worth the small setup whenever several sheets use the same whole, because the denominator then has exactly one definition.

In a pivot table

A pivot table is the right tool when the grouping is the point: it re-computes the total from whatever rows survive the filters, so you never anchor a range and never risk a stale denominator after the data changes.

The trade-off is that the pivot decides the denominator for you, based on where the field sits in the layout. That is usually what you want, but it means the share answers “of what this table currently totals” rather than “of the whole data set”, and the two differ as soon as a filter or a page field is active. Read the grand total cell before you quote any percentage out of a pivot.

Excel: Show Values As

Build the pivot with your category field in Rows and the amount in Values. Then right-click any value cell, choose Show Values As → % of Column Total. Each row now displays its own value divided by the column total, and the grand total row reads 100%. The same option is available from the PivotTable Fields pane via Value Field Settings → Show Values As, and choosing Normal reverses it.

In a two-dimensional pivot the three variants mean three different things: % of Column Total is each row's share down its column, % of Row Total is each cell's share across its row, and % of Grand Total is every cell's share of the whole grid. Only % of Grand Total gives shares that all add to 100 across the entire table.

Google Sheets: Show data as

In the pivot table editor, under Values, click the dropdown next to the summed field and choose “Show data as → Percent of column total”. Sheets offers the same family of options as Excel, including percent of row total and percent of grand total, and you can add the field twice — once as a raw Sum and once as a percentage — so the table shows the amount and the share side by side.

One caveat shared by both tools: a pivot percentage answers “share of the filtered total”, so a date or region filter in the report changes the answer without changing your formula. When the intended denominator is the whole data set, apply the filter after the share is computed, or compute the share against an unfiltered total.

In Tableau, as a calculated field

Tableau gives you a measure, not a grid, so percentage of total means dividing the aggregated measure by the total of that same aggregation across the table. There are three ways to get it, and they differ in what happens when you filter.

Which one you choose is a reporting question rather than a style preference: the quick table calculation and TOTAL() both follow the view, while a level-of-detail denominator can be made to ignore it. Decide what the number is supposed to be a share of, and the right construction follows.

The quick table calculation

Put your dimension on Rows and SUM of the measure on Columns, right-click the green measure pill on the shelf, choose Quick Table Calculation → Percent of Total. Tableau wraps the aggregation in a table calculation whose denominator is the total of the table, which is exactly the share you want, and the bars become shares summing to 100%.

The same menu is where you adjust what “the table” means: Compute Using controls which dimensions the calculation spans, so a share across a table with Region and Category on Rows changes meaning depending on whether it walks across columns or down panes. If the percentages look right but sum to 100% in the wrong direction, that setting is the fix, not the formula.

The calculated field version

For reusable logic, create a calculated field: SUM([Sales]) / TOTAL(SUM([Sales])). TOTAL() with no arguments spans the whole table, and the inner aggregation is required — you are dividing an aggregate by an aggregate, not a row value by a column. Drag it onto Columns or Rows like any measure.

Then set the display format to Percentage, either on the field's default format or on the pill in the view. Without that you get 0.25 on the axis where you wanted 25%, which is the single most common complaint about this calculation.

The LOD version, and when you want it

If you want the denominator fixed to the entire data source instead of the current view, use a level-of-detail expression: SUM([Sales]) / SUM({ FIXED : SUM([Sales]) }). The empty dimension list after FIXED means “one value for everything”.

Know the difference before you pick it. Table calculations such as TOTAL() are computed after dimension filters, so filtering to one region rebases the shares to that region. A FIXED expression is computed before ordinary dimension filters, so the denominator can still include rows your view has excluded unless you put those filters in context. That makes LOD the correct choice for “share of the company-wide total shown in this regional view”, and the wrong choice for “share of what is on screen”.

Three phrasings of the same question

“Percentage of total sales” is a composition question: a product line worth 3,000 in a quarter totalling 15,000 is 20%; the five lines 1,250 / 3,000 / 2,750 / 5,000 / 3,000 share out as 8.33%, 20%, 18.33%, 33.33% and 20%. Note that the rounded column adds to 99.99%, not 100% — that is rounding, not an error, and it is why finance decks state that components may not sum due to rounding.

“Percentage of total cost” is the same arithmetic pointed at an expense base, and it is the version people most often get wrong by including the total row in the SUM. If B7 already holds =SUM(B2:B6) and your formula says =B2/SUM($B$2:$B$7), you have divided by a total that counts itself, every share shrinks, and the column sums to something near 50% instead of 100%. That signature — all the shares roughly halved — is the giveaway.

“Percentage of total amount” usually means something else again: a proportion against a stated figure rather than a sum of parts. Paying 4,750 against an invoice total of 19,000 is 25% paid; 4,750 against a remaining balance of 14,250 is 33.33%; 4,750 against a 3,800 target is 125% attainment. Same numbers, three legitimate answers, and the question you were actually asked decides which total belongs in the denominator.

• Composition (share of a sum): parts must be inside the whole and all non-negative, so shares land between 0% and 100% and add to 100%.
• Attainment (against a plan or target): values above 100% are expected and mean you beat the plan.
• Progress (against a balance): the denominator shrinks as the balance shrinks, so the same amount is a different percentage every time you ask.

Doing it in the browser without a spreadsheet

For one or two numbers, the linked browser calculator is faster than opening a spreadsheet. Its second mode is exactly this calculation: you type the part into the first field and the whole into the second, and it returns (part ÷ whole) × 100 — so 2,150 and 8,640 come back as 24.88%. The first mode does the reverse direction (a stated percentage of a given amount), the third adds or subtracts a flat percentage from an amount, and the fourth recovers the original figure before a discount or a markup, which is the one people need when a sticker price is the number after tax.

Two output details worth knowing before you quote a result. Answers are rounded to two decimals by default, and for very large or very small results the tool switches to four significant digits to keep them readable. A dash appears wherever a field is empty or not a readable number, because there is nothing to calculate yet; if the whole you enter is 0, the second field is asking how much of nothing a number is, and the tool shows an infinity symbol rather than an error.

It is also honest about its limits, which matter for this particular question: it takes two numbers at a time, so it will not total a list for you, will not import a spreadsheet, and does not weight or group anything. Get the total from SUM in Excel or Sheets, or add it up yourself, and then feed part and whole into mode 2. Everything runs in the page in plain JavaScript — nothing you type is sent anywhere, which is the property you want when the numbers are payroll costs or an invoice.

Common problems

The shares do not add to 100%. Usually one of four things: rounding at two decimals (99.99% is fine), a part that is not in the total, a component with a negative value, or the SUM range missing rows you thought were included.

Copying the formula down gives increasingly absurd numbers. Your range was not anchored: SUM(B2:B6) becomes SUM(B3:B7) in the next row and SUM(B4:B8) after that, so the denominator loses a row at the top and starts picking up whatever sits below the data — most often the total row itself. Fix it with $ signs on the range before you copy, not after: a column of already-dragged formulas has a different range in every row, so there is nothing one fix repairs.

A cell shows 2,500% or 0.0025. Either the Percent Style was applied to a value already multiplied by 100, or the multiplication was skipped and the raw ratio is being read as a percent. Decide on one representation per column and stick to it.

The formula returns #DIV/0! or an empty result. The whole is zero or blank. A zero total is not the same as a missing total: ask explicitly which one it is, because “0% of zero” has no defined answer and any percentage built on it is meaningless.

The pivot or Tableau number disagrees with your formula. Different denominator scope, almost always — a filter, a hidden row set, or a table calculation computed after filtering versus a fixed level-of-detail total. Put the intended total on screen next to the share and compare once, and the disagreement resolves itself.

• Anchor the total with $ so copying cannot move it.
• Reference the existing total cell instead of re-summing when the total may include adjustments.
• Use percent formatting or ×100, never both.
• Check that the denominator covers the same period, rows and filters as the numerator.
• Expect values beyond 100% only when the whole is a target or a net figure, and label it when you do.

Frequently asked questions