Excel Formula for Percentage of Total: Clear Examples

Formulas & functions · Updated November 18, 2026

To calculate a percentage of a total in Excel, divide the part by the total: if the value is in B2 and the total is in B10, enter =B2/$B$10 in C2, then format the result as a percentage. The dollar signs keep the total cell fixed when you copy the formula down.

What “percentage of total” means

A percentage of total expresses one value as a share of a larger value. The calculation is the part divided by the whole, multiplied by 100 when stated as a percent. Excel stores percentages as decimal numbers, so the division alone produces the right underlying result: 0.25 is displayed as 25% when percentage formatting is applied.

For example, if a product accounts for 45 units out of 180 units sold, its share is 25%. This is different from calculating a percentage change, which compares a new value with an earlier value. Percentage of total answers “what share of the whole is this?”; percentage change answers “how much did this value increase or decrease?”

The denominator is what defines the result. You might compare each department’s spending with all company spending, each item’s sales with total sales, or each expense line with a monthly budget. Choose the total that matches the question before writing the formula.

Basic formula with a worked example

Suppose a worksheet lists four categories in A2:A5, their values in B2:B5, and the total in B6. The values are 120, 80, 50, and 150, for a total of 400. With the value to evaluate in B2 and the overall total in B6, enter this in C2:

=B2/$B$6

Copy the formula from C2 through C5. The dollar signs make B6 an absolute reference, so each copied formula continues to divide by the same total. The first result is 120 divided by 400, or 0.3; when formatted as a percentage it displays as 30%. The other results are 20%, 12.5%, and 37.5%. Together they add to 100%.

To include a total row in the worksheet, with values in B2:B5 and the total in B6, calculate the total with:

=SUM(B2:B5)

Then apply the percentage formula to each category. If you already have the total in a separate cell, there is no need to repeat the SUM calculation inside every percentage formula. Keeping the total visible can make the worksheet easier to inspect and explain.

Format the result as a percentage

After entering the division formula, select the result cells and choose Home > Number > Percent Style. In Excel for Microsoft 365 on Windows, the Percent Style button is in the Number group on the Home tab. The same ribbon command is available in Excel for Microsoft 365 for Mac, though the ribbon layout may look slightly different.

Formatting changes how the value is displayed; it does not change the underlying calculation. If the formula returns 0.3, percentage formatting displays 30%. If you instead multiply the formula by 100 and then apply percentage formatting, Excel will display 3,000%. Usually, do not multiply by 100 when you intend to use the Percent Style format.

Use the increase or decrease decimal buttons in the Number group when readers need more or fewer digits. For instance, 12.5% may be useful for a category breakdown, while 13% may be sufficient in a short summary. Rounding the display does not necessarily change the stored value, so displayed percentages may appear to sum to 100% even when their unrounded values differ slightly—or appear to total 99% or 101% because each value was rounded.

Copy the formula down without moving the total

Excel references can be relative, absolute, or mixed. In =B2/$B$6, B2 is relative: when copied down one row, it changes to B3. The reference $B$6 is absolute: both the column and row stay fixed. This combination is useful when one column contains the parts and one cell contains the shared total.

You can make a reference absolute by typing the dollar signs or by selecting the reference in the formula bar and pressing F4 on Windows. On many Mac keyboards, use Command+T to cycle reference types; depending on the keyboard settings, you may need the Fn key with a function-key command. You can also simply type the dollar signs if the shortcut differs on your Mac.

A mixed reference fixes only one part of a cell address. For a formula copied down one column, fixing the total’s row as B$6 would also keep the denominator on row 6, although the column could shift if copied sideways. For ordinary percentage-of-total calculations copied down, $B$6 is the clearest choice because it fixes both parts.

Calculate the total inside the formula

You do not have to put the total in a separate cell. If values are in B2:B5 and each row should be compared with the sum of that range, enter this formula in C2 and copy it down:

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

The numerator moves from B2 to B3, B4, and B5, while the absolute range in SUM stays the same. This method keeps the worksheet compact, but it can be less transparent when the total is important to the reader. A separate total cell is often preferable for a report because people can see and check the denominator.

Be careful about whether the total range includes the row containing the formula. If the formula sits in the same range it sums, Excel may report a circular reference. Put the percentages in a separate column and sum only the original values, or place the total outside the range being calculated.

Choose the right denominator: grand total or subtotal

One of the most common sources of a surprising result is using the wrong denominator. If regional sales are listed in rows 2 through 20 and the question is each region’s share of all sales, divide each region’s value by the sum of all the values. If the question is each region’s share of its own product group, the denominator should instead be that group’s subtotal.

For example, suppose a department spends $30,000 from a company-wide budget of $200,000. Its share of the overall budget is 15%. If its team has a $50,000 allocation, its share of that allocation is 60%. Both calculations can be correct; they answer different questions. Label the result column clearly, such as “% of company total” or “% of team budget,” so readers understand the basis.

Subtotals also matter when categories overlap. If each record belongs to exactly one category, category percentages should generally sum to 100%, subject to rounding. If records can appear in multiple categories, or if the groups use different denominators, the percentages may add up to more than 100%. That is not automatically a formula error, but it should be explained in the report.

Use an Excel Table for expanding data

For a list that grows over time, an Excel Table can make formulas easier to maintain. Select the data and choose Insert > Table, then confirm whether the table has headers. In this example, assume the table is named Sales and has a numeric column named Amount. In a new table column, use:

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

The structured reference [@Amount] means the Amount value on the current row, while Sales[Amount] refers to the Amount column in the Sales table. Excel normally fills a calculated-column formula through the table, and new table rows can inherit it. Format that new column as a percentage.

Check the table’s total-row settings if you turn on a total row: the total row is a summary, not another data record to include as a part. Structured references adjust as table data changes, but the formula still depends on the values being numeric and on the table column having the expected name.

Common errors and how to fix them

  • The result looks 100 times too large: Remove any unnecessary multiplication by 100 if you are also using percentage formatting. A result of 0.4 should display as 40%.
  • The copied results use different totals: Lock the denominator with dollar signs, for example $B$6, before filling the formula down.
  • The formula returns #DIV/0!: The total is zero or blank. Confirm that the denominator points to the intended total and that the source data contains numeric values.
  • The percentages do not add to 100%: Check that every relevant category is included once, that all formulas use the same total, and that the displayed values are not simply rounded.
  • A value is unexpectedly above 100%: The part may be larger than the denominator, or the denominator may be a subtotal rather than a grand total. Verify that this matches the question.
  • Numbers are treated as text: Imported values can look numeric without being stored as numbers. Convert or clean the source values, then check that SUM includes them.

If a zero total is a legitimate possibility and you want to display 0% instead of an error, with the part in B2 and the total in B6, use:

=IF($B$6=0,0,B2/$B$6)

This formula explicitly checks for a zero denominator and returns zero in that case. Use it only if zero is a meaningful display for your report; a blank or a note such as “not available” may be more informative when the total has not yet been entered. Avoid hiding every error automatically if doing so could conceal a mistaken cell reference.

Percentage of total versus percentage change

These two calculations are easy to confuse because both use percentages. Percentage of total divides one component by the overall amount. Percentage change measures the difference between a new amount and an old amount relative to the old amount.

For example, if monthly sales are $30,000 and annual sales are $240,000, the month represents 12.5% of the annual total. If sales rose from $24,000 last month to $30,000 this month, the increase is 25% relative to the previous month. Use the first kind of formula for composition or contribution, and the second for growth or decline. A percentage-of-total formula is not a growth-rate formula just because its result is formatted with a percent sign.

Frequently Asked Questions

What is the simplest Excel formula for percentage of total?
Divide the part by the total, such as =B2/$B$10 when the value is in B2 and the total is in B10. Format the result as a percentage. The absolute reference keeps the denominator fixed when you copy the formula down.

Do I multiply by 100 in an Excel percentage formula?
Usually, no. Excel stores 25% as 0.25 and percentage formatting displays that decimal as 25%. Multiplying by 100 and then applying percentage formatting would make the displayed result 100 times too large.

How do I calculate each row as a percentage of a column total?
With the values in B2:B20, enter =B2/SUM($B$2:$B$20) in C2 and copy it down through C20. Apply percentage formatting to C2:C20. This setup compares each row with the sum of the full value range.

Why do my percentages add up to 99% or 101%?
Rounding is a common reason. Excel may store precise decimal results but display each one rounded to a whole percent. Also check for omitted rows, duplicate categories, or formulas that use different totals.

Can the percentage be greater than 100%?
Yes. That happens when the part is larger than the chosen total. It may indicate a wrong denominator, but it can also be valid—for example, when actual spending exceeds a smaller budget or when the value is compared with a partial target.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.