The Basic Formula for Any Percentage

To calculate a percentage in Excel, divide the part by the whole, then multiply by 100. The formula is =(part/whole)*100. If you want to find what percentage 25 is of 200, you would enter =25/200*100 in a cell, and Excel returns 12.5.

You can also write it as =(part/whole) and format the cell as a percentage — Excel will multiply by 100 automatically and display the % symbol. Both methods give the same result; the second one is cleaner if you are building a spreadsheet others will read.

The real power comes when you reference cells instead of typing numbers. If cell A1 holds 25 and cell B1 holds 200, you write =A1/B1, format as percentage, and you have a formula that updates if either number changes.

Key Takeaways

  • The percentage formula is (part ÷ whole) × 100, or you can divide and format the cell as a percentage to let Excel handle the multiplication.
  • Use cell references like =A1/B1 instead of typing numbers, so your formula updates when the data changes.
  • The dollar sign ($) locks a cell reference in place when you copy a formula across rows or columns.
  • Percentage change uses the formula ((new value − old value) ÷ old value) × 100 to show growth or decline.
  • Excel's percentage format rounds the display but keeps the full decimal value underneath, so calculations stay accurate.

Percentage of a Total Using Cell References

When you have a list of numbers and want to see what percentage each one represents of the total, use absolute and relative cell references. Say column A holds sales figures for five months, and you want column B to show each month as a percentage of the year total.

In cell B1, write =A1/SUM($A$1:$A$5)*100. The dollar signs around the SUM range mean "always use this total," but A1 changes to A2, A3, and so on when you copy the formula down. Copy that formula to B2 through B5, and each cell divides its month by the fixed total. Without the dollar signs, the range would shift as you copy, giving you wrong answers.

If you prefer to format as percentage instead, write =A1/SUM($A$1:$A$5) and format column B as percentage. The result looks the same, and the formula is shorter.

Calculating Percentage Change Between Two Numbers

Percentage change shows how much something grew or shrank. The formula is ((new value − old value) ÷ old value) × 100. If a price was $50 last month and is $60 now, the change is ((60−50)/50)*100, which equals 20 percent.

In a spreadsheet, if the old value is in A1 and the new value is in B1, write =(B1−A1)/A1 and format as percentage. A positive result means growth; a negative result means decline. This formula works for any two-point comparison: website visitors, inventory counts, test scores, or revenue.

One common mistake is dividing by the new value instead of the old one. That gives you a different percentage and is usually wrong. Always divide by the starting point.

Formatting Cells to Display Percentages Correctly

After you write a percentage formula, the way it displays depends on the cell format. If you see 0.125 instead of 12.5%, the cell is formatted as a number, not a percentage. Right-click the cell, choose Format Cells, and select Percentage from the Category list. Excel multiplies by 100 and adds the % symbol.

You can also use the percentage button in the toolbar — it is usually marked with a % symbol. One click applies percentage formatting to whatever cells you have selected. If you want to control decimal places, right-click again and choose Format Cells, then set the number of decimal places you want to see.

Remember that formatting only changes how the number looks. The actual value underneath stays the same. If a cell shows 12.5%, it still holds 0.125 for calculation purposes, so other formulas that reference it will work correctly.

Using Percentage Formulas in Larger Spreadsheets

In a real spreadsheet, you often calculate percentages across many rows. Build the formula once in the first row, then copy it down to all the others. Select the cell with your formula, copy it (Ctrl+C on Windows, Cmd+C on Mac), then select the range where you want it to go and paste (Ctrl+V or Cmd+V).

Excel adjusts the cell references automatically — relative references like A1 shift to A2, A3, and so on, while absolute references with dollar signs stay locked. This saves you from typing the same formula dozens of times and reduces the chance of errors.

If you need to see the formula itself instead of the result, press Ctrl+` (grave accent, usually near the 1 key) to toggle formula view on and off. This helps you check that your references are correct before you copy the formula to hundreds of rows.

Common Mistakes When Calculating Percentages

The most frequent error is forgetting to divide by the right number. For a percentage of a total, divide by the total, not by the count of items. For percentage change, divide by the old value, not the new one. Reading the formula aloud before you press Enter — "25 divided by 200" — often catches the mistake.

Another mistake is mixing formatted and unformatted cells. If you write =A1*B1 where A1 is 0.5 (formatted as 50%) and B1 is 100, you get 50, not 5000. The format is just display; the value is still 0.5. Multiply by 100 explicitly if you need the result in whole numbers, or be clear about what the decimal means.

A third trap is copying a formula without checking whether your cell references should be absolute or relative. If you meant to always divide by the same total but forgot the dollar signs, copying the formula breaks it. Test the formula in a few cells before you copy it across the whole sheet.

Percentage Formulas for Discounts and Markups

Retail and pricing use percentages constantly. To find a discounted price, multiply the original price by (1 − discount percentage). If an item costs $100 and is 20% off, the new price is =100*(1−0.20), which equals $80. In a spreadsheet, if the original price is in A1 and the discount rate (as a decimal) is in B1, write =A1*(1−B1).

For a markup, use =original price*(1+markup percentage). A $50 item marked up 30% costs =50*(1+0.30), or $65. If you have the percentage in a cell formatted as a percentage, Excel treats it as a decimal automatically, so the formula works without extra steps.

These formulas scale to any number of items. Build it once for one row, copy it down, and you have the discounted or marked-up price for your entire inventory list.

Frequently Asked Questions

Do I need to multiply by 100 if I format the cell as percentage?

No. If you format as percentage, write =A1/B1 without the *100. Excel treats the cell as a percentage and multiplies by 100 for display. If you write =A1/B1*100 and then format as percentage, you get 1250% instead of 12.5%.

What does the dollar sign do in a cell reference?

The dollar sign locks that part of the reference. $A$1 never changes when you copy the formula. A$1 locks the row but lets the column change. $A1 locks the column but lets the row change. Use this to keep totals or rates fixed while other parts of the formula adjust.

How do I show a percentage with two decimal places?

Right-click the cell, choose Format Cells, select Percentage, and set the decimal places to 2. You can also use the increase/decrease decimal buttons in the toolbar if your percentage format is already applied.

Can I calculate what percentage one number is of another without formatting?

Yes. Write =(A1/B1)*100 and leave the cell formatted as a number. It displays as 12.5 instead of 12.5%, but the calculation is the same. Most people prefer percentage formatting because it is clearer.

What if my percentage formula returns an error?

The most common cause is dividing by zero — if the denominator is empty or zero, Excel shows #DIV/0!. Check that the cell you are dividing by actually contains a number. If it should be empty sometimes, use =IFERROR(A1/B1,"—") to show a dash instead of an error.