Calculate Percentage in Excel: Formulas and Table Tips

Formulas & functions · Updated November 18, 2026

To calculate a percentage in Excel, divide the part by the total, then format the result as a percentage. For example, if the part is in B2 and the total is in C2, enter =B2/C2 in D2; 25 divided by 200 displays as 12.5% when D2 is formatted as a percentage. In an Excel Table, you can use column names instead of ordinary cell references, and Excel can copy the formula down the column for you.

This guide covers percentages of a total, percentage change, formatting, and ways to fix common errors in Excel for Microsoft 365 on Windows. The same formulas work in recent Excel versions, and the notes below explain where the steps differ on Mac or in Excel for the web.

What You’ll Need

  • An Excel workbook with numeric values, such as quantities, scores, or sales.
  • A clear definition of the part and the total, or the old and new values if you are calculating a change.
  • Optional: an Excel Table. To create one, select your data, including its headers, and choose Insert > Table. Check My table has headers if the first row contains column names.

Step-by-Step

  1. Arrange and check your data. Put each type of information in its own column and include a descriptive header in the first row. For a share-of-total example, use Item in column A, Sales in column B, and Share in column C. Enter amounts as numbers, not text containing a percent sign. If you already have an Excel Table, click inside it to see the Table Design tab and confirm that its headers are clear. A Table is optional, but it makes formulas easier to read and normally fills a formula down the whole column.
  2. Decide which percentage you need. For a part as a share of a whole, divide the part by the whole. If a student gets 18 points out of 24, the calculation is 18 divided by 24, or 0.75; formatted as a percentage, the answer is 75%. For a percentage increase or decrease, compare the new value with the old value and divide the difference by the old value. These are different questions, so choose the formula that matches the result you want.
  3. Enter a basic percentage formula. With the part in B2, the total in C2, and the answer intended for D2, enter =B2/C2 in D2. For example, if B2 contains 25 and C2 contains 200, the raw result is 0.125. That decimal is the correct underlying value for 12.5%; do not multiply it by 100 if you plan to use Excel’s percentage formatting. Press Enter to calculate. If you are working down a normal range, drag the fill handle at the bottom-right corner of D2 down to copy the formula to other rows.
  4. Use Table column names for a share of a total. Suppose your Excel Table is named SalesTable and has columns Sales and Share. In the first data row of the Share column, enter =[@Sales]/SUM(SalesTable[Sales]). This divides the current row’s Sales by the sum of the Sales column. If the row contains 120 and all sales add up to 400, the result is 0.3, which displays as 30%. Excel Tables usually copy a calculated-column formula to the other rows automatically. The @ symbol means the value in this row; the full column reference inside SUM includes all sales in the Table.
  5. Calculate percentage change when that is the question. With the old value in A2 and the new value in B2, enter =(B2-A2)/A2 in C2. If A2 is 80 and B2 is 100, the difference is 20 and 20 divided by 80 is 0.25, so the change is 25%. If the new value is 60 instead, the result is -25%, a decrease. The formula uses the old value as its base; reversing the order changes the meaning of the result.
  6. Format the result as a percentage. Select the result cell or the whole results column, then choose Home > Number > Percent Style. In Windows, Ctrl+Shift+% applies percentage formatting quickly; on Mac, use the Home ribbon’s Percent Style button if the shortcut differs in your keyboard layout. In Excel for the web, select the cells and use the Number Format controls on the Home tab. Choose Increase Decimal or Decrease Decimal on the Home tab to show the precision you need. For instance, 0.125 can display as 13% with zero decimal places or 12.5% with one; the stored value has not changed.
  7. Check the formula and the displayed result. Click a result cell and inspect the formula bar. Confirm that the numerator is the part or change you intended and that the denominator is the correct total or old value. Check one row with simple arithmetic, such as 25 out of 200, before relying on a whole column. If the calculation is in a Table, add a new data row and verify that Excel extends the formula. If it does not, copy the formula into the new row or check that calculated-column filling is enabled in Excel’s options.

Common Mistakes

Multiplying by 100 and applying percentage formatting. Dividing 25 by 200 gives 0.125. Percentage formatting displays that value as 12.5%. If you also multiply the formula result by 100, formatting it as a percentage displays 1,250%, not 12.5%. Use either a decimal result with percentage formatting or a number multiplied by 100 that you intend to display as an ordinary number, not both.

Using the wrong denominator. A share of total should use the full total, not just the value in the row above or a subtotal that excludes relevant records. For percentage change, divide by the old value. Also check whether blank rows, filtered rows, or excluded categories should be part of the total; the choice depends on what the percentage is meant to describe.

Entering numbers as text or dividing by zero. Values imported with leading apostrophes, spaces, or text symbols may look numeric but will not behave like numbers. A blank or zero total can also cause a division error. Correct the source values and ensure the denominator is a valid, nonzero number before formatting the result.

Tips

Give each column a meaningful header, such as Sales and Share, before turning the range into a Table. Structured references are easier to understand than a formula full of cell addresses, and they are less likely to point to the wrong place when columns move. To rename a Table, click in it, open Table Design, and edit the Table Name box. Use a simple name without spaces, such as SalesTable.

Choose decimal places based on the use of the report. Whole percentages are readable for a quick summary, while one or two decimal places can help when small differences matter. Keep the same precision throughout a column so readers can compare values fairly. If you want the percentage column to stand out, select it and use Home > Number > Percent Style; number formatting changes how a value is shown, not the calculation itself.

For a Table percentage of total, the full-column SUM reference makes each row’s share use the same overall denominator. If you instead want each row’s share within a category or group, calculate a separate total for that group and divide by that total. State the scope of the denominator in a nearby heading or note so the result is not mistaken for a share of all data.

Troubleshooting

The result shows 0.125 instead of 12.5%. The division may be correct; the cell is simply using a general number format. Select it and choose Home > Number > Percent Style. If it then shows 1,250%, remove any multiplication by 100 from the formula.

The result is #DIV/0!. Excel is dividing by zero or by an empty cell. Check the denominator in the source row and the total formula. Enter the real total when it is available. If zero is a valid situation, decide what the report should show for that case rather than silently treating it as a percentage.

The result is unexpectedly large, small, or negative. Check whether the numerator and denominator are in the right places and whether the values are stored as expected. For example, if one value is entered as 15% and another as 15, those represent 0.15 and 15, not the same quantity. For percentage change, a negative answer means the new value is lower than the old one; a result of 100% means it doubled.

The Table formula does not fill down. Confirm that you entered the formula in a data row under the correct header, not in the header or totals row. If only one row changes, copy the formula down or use the fill handle. In Excel for Mac or the web, the Table Design controls and ribbon layout may look different, but the Table and formula concepts are the same. If a structured reference is rejected, check the Table name and column spelling, including spaces.

Frequently Asked Questions

What is the simplest formula to calculate a percentage in Excel?

Divide the part by the total, then format the result cell as a percentage. For example, 30 out of 120 is 30 divided by 120, which displays as 25% after percentage formatting.

Do I need to multiply the formula by 100?

No, not if you apply percentage formatting. Excel stores 25% as 0.25 and displays that decimal as a percentage. Multiplying by 100 as well would make the displayed result one hundred times too large.

How do I calculate a percentage of a total in an Excel Table?

Use the current row’s value divided by a reference to the full Table column total. For a Table named SalesTable with a Sales column, the formula in a Share column is =[@Sales]/SUM(SalesTable[Sales]). Format the Share column as a percentage.

How do I calculate percentage increase or decrease?

Subtract the old value from the new value, then divide by the old value. Format the result as a percentage. A positive result is an increase and a negative result is a decrease.

Why does Excel show too many decimal places?

Percentage formatting may be set to show more precision than you want. Select the cells and use Decrease Decimal on the Home tab. This changes the display, not the underlying calculation.

Can I calculate a percentage when the total is zero?

Not as a standard part-over-total percentage, because division by zero is undefined. Check whether the total is missing or whether a different measure is appropriate, then decide how to represent that case in your report.

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.