OmniDocHow to Calculate Percentages in Excel With Formulas
Learn how to calculate percentage of total and percentage change in Excel with simple formulas, plus checks for the errors that usually cause wrong results.
If you have a column of sales figures and need each one as a share of the total, or you need to know how much a budget line grew from last quarter, you need a percentage formula. Many people guess at the math, multiply by 100 in the wrong place, and end up with a number that looks plausible but is not. This guide shows you the two formulas you will use most often and how to confirm the results are right.
You will learn how to calculate a part-of-total percentage, a percentage change between two values, and how to copy these formulas down a column without breaking them. The examples use numbers you can reproduce in any blank spreadsheet, so you can test each step before using it on real data.
The core of this topic is simple: percentages in Excel are fractions displayed with a percent sign. Once you understand that, the formulas become easy to remember.
Which percentage formula should you use? The short answer
Use part divided by total to find what share one value is of a whole, and (new minus old) divided by old to find how much a value changed. Format the result cell as a percentage, and Excel will display the decimal as a percent.
| Your situation | What to do | Why |
|---|---|---|
| You want each item's share of a total | Divide the item by the total, for example =B2/$B$9 | The result is the fraction of the whole, which displays as a percentage |
| You want to know how much a value grew or shrank | Use =(C2-B2)/B2 | The change is measured against the original value, so the old value goes in the denominator |
| You want to add a percentage to a number | Use =B2*(1+C2), where C2 holds the rate as a decimal such as 0.08 | Multiplying by (1 plus the rate) gives the new total in one step |
| You want to take a percentage off a number | Use =B2*(1-C2) | This works the same way for discounts or cuts |
| You want a percentage of a number, such as a tip on a bill | Use =B2*C2 | Excel reads a value formatted as a percentage as a decimal, so multiplication works directly |
Before you start
Check these items before you type any formulas. Each one prevents a common cause of wrong results.
- Confirm your numbers are numeric. If values are left-aligned in their cells or have a small green triangle, they may be stored as text. Text values are ignored by sums and produce wrong or error results in division.
- Decide what the base value is. For percentage change, the base is the original number. For share of total, the base is the total you are comparing against. Mixing these up is a common error.
- Know where your total lives. If the total sits in one cell, such as B9, you will reference it in every row. Plan to lock that reference with dollar signs so it does not shift.
- Keep a copy of the original data. Build your formulas in a new column or a new sheet so you can compare them against the source values if something looks off.
- Use a small test range. Five rows is enough to confirm the formulas work before you apply them to a 2,000-row file.
Calculating a share of total and a percentage change, step by step
This sequence uses a small example: monthly expenses in column B, with the total in cell B9. You can rebuild it in a blank sheet in about two minutes.
- Enter your values. Put item labels in column A and amounts in column B, starting at row 2 and running through row 8. Add the total in B9 with a sum formula, such as =SUM(B2:B8), so the denominator is calculated, not typed.
- Write the share-of-total formula in C2. Type =B2/$B$9. The dollar signs lock the total so the reference stays on B9 when you copy the formula down.
- Copy the formula down the column. Drag the fill handle from C2 to C8, or double-click it. Each row now divides its own amount by the same total.
- Format the column as a percentage. Select C2:C8 and apply a percentage number format, then choose how many decimal places you want. The cell values stay the same; only the display changes.
- Set up a percentage change example. In column E, place last month's value in E2 and this month's value in F2. In G2, type =(F2-E2)/E2. For example, if last month was 250 and this month is 300, the formula returns 0.2, which you format as a percentage.
- Format the change column as a percentage. Apply the same percentage format to G2 and copy the formula down as needed. Negative results show a minus sign, which tells you the value decreased.
- Check one row by hand. Pick a single row, calculate the answer on paper or with a calculator, and compare it to the cell. If they match, the rest of the column is almost certainly correct.
- Protect the formulas. If others will edit the file, consider locking the total cell and the formula columns so that accidental typing does not overwrite them.
Check the result
Once the formulas are in place, verify the output before you share or use it. These checks catch most problems.
| Check | How to see it | If it's wrong |
|---|---|---|
| Shares match the whole | Sum the percentage column; it should match the total, allowing for rounding | Look for a total cell that is off, a hidden row, or a value stored as text |
| The result is a fraction, not 100 times too large | A share of 0.3 should display as a percentage of 30, not 3000 | Multiplying by 100 in addition to formatting as a percent causes this; remove the extra multiplication |
| Percentage change has the right sign | A value that rose should show a positive percentage | You may have subtracted in the wrong order; the formula must be (new minus old), not (old minus new) |
| No error values | Cells show #DIV/0! or #VALUE! | Check for a zero or blank base value, or for text where a number should be |
| The total reference did not shift | Click a formula in a lower row; it should still point to $B$9 | Add the dollar signs before copying the formula |
If the percentages look reasonable but a few do not match your hand calculation, look for values that were typed with a comma or a currency symbol. Some spreadsheets read those as text, so the formula silently skips them.
If your numbers are in a PDF
Sometimes the figures you need are locked inside a PDF table rather than in a spreadsheet. In that case, you can move the data into Excel format first. The OmniDoc desktop client, available at download.omnidoc.top, includes a PDF editor that can export content to Excel format. If the PDF is a scan, run OCR on it first so the text is selectable, then export. Review the exported cells before you build formulas, because a scanned table can introduce small character errors such as a 5 read as an S. Once the values are numeric and correct, apply the steps above as normal.
❓ FAQ
1. Why does my result show 0.3 instead of a percentage?
The cell is formatted as a number rather than a percentage. Select the cell and apply a percentage format. The underlying value, 0.3, is correct; only the display changes.
2. What is the difference between percentage change and percentage difference?
Percentage change always uses the original value as the base, so going from 100 to 150 is a 50% increase, but going from 150 back to 100 is a 33% decrease. Percentage difference uses the average of the two values as the base, which makes the result the same in either direction. Most everyday budget and sales work uses percentage change.
3. How do I subtract a discount percentage from a price?
Put the price in one cell and the discount rate, stored as a decimal, in another, then use =price*(1-discount). For a 200 item with a discount rate of 0.15, the formula returns 170.
4. Why does my formula return #DIV/0!?
The denominator is zero or empty. This happens when the base value in a percentage change formula has not been filled in yet. You can wrap the formula in IFERROR to show a blank instead, but it is better to find the missing value first.
5. Can I calculate a running percentage of a growing total?
Yes. Use a cumulative sum in one column and divide each cumulative value by the total in the same way as the share-of-total formula. Keep the total reference locked with dollar signs so every row points to the same denominator.
📝 Summary
Percentage calculations in Excel come down to two formulas: part divided by total for share, and (new minus old) divided by old for change. Lock your total with dollar signs, format the results as percentages, and check a few rows by hand before you rely on the whole column.
