How to Subtract a Percentage in Excel
To subtract a percentage in Excel, multiply the number by 1 minus the percentage: =A1*(1-B1), where A1 holds the value and B1 holds the percentage. For example, to take 20% off $50 you type =50*(1-20%), which returns $40. This single formula handles discounts, markdowns, and any “take X% off” situation, and it works identically on Windows and Mac.
The reason it works is simple math. If you remove 20% from a number, you keep 80% of it. So 1 - 20% equals 0.8, and 50 × 0.8 = 40. Excel does the conversion from a percent to a decimal for you behind the scenes, which is exactly where most mistakes come from. This guide walks through every variation, shows you how to apply a discount down a whole column, and ends with a troubleshooting section for when the answer comes out wildly wrong.
The two formulas that subtract a percentage
There are two equivalent ways to write the calculation, and both give the same answer.
The compact version keeps 100% minus the discount:
=A1*(1-B1)
The explicit version calculates the discount amount, then subtracts it:
=A1-(A1*B1)
If A1 is 50 and B1 is 20%, both return 40. The first multiplies 50 by 0.8. The second multiplies 50 by 0.2 to get the $10 discount, then subtracts that from 50. Use whichever reads more clearly to you. The explicit form is handy when you also want to show the discount amount in its own cell, since =A1*B1 gives you the $10 figure on its own.
Both rely on the same subtraction logic Excel uses everywhere. If you want a refresher on the underlying operation, see our guides on how to subtract in Excel and the subtract formula.
The single most important rule: percent vs decimal vs whole number
This is where results go wrong, so read it carefully. How you store the percentage decides whether your formula works.
- A cell formatted as a percent that shows
20%actually holds the value0.2underneath. The display is just formatting. With=A1*(1-B1), B1 = 20% works perfectly because1 - 0.2 = 0.8. - A decimal like
0.2typed into a plain (unformatted) cell holds the value0.2too. The formula works exactly the same:=A1*(1-B1)gives 0.8 × A1. The only difference from the case above is cosmetic — one cell shows20%, the other shows0.2. - A whole number like
20typed into a plain cell holds the value20, not0.2. If you plug that into=A1*(1-B1), Excel computes1 - 20 = -19and multiplies, giving a huge negative number. To use a whole number, divide by 100 inside the formula:=A1*(1-B1/100).
So the rule is: if the cell shows a % sign or holds a decimal, use =A1*(1-B1). If the cell holds a plain whole number like 20, use =A1*(1-B1/100). Mixing these up is the number-one cause of nonsense answers, and we cover the fix again in troubleshooting below.
Formatting a cell as a percentage
To format a cell so 0.2 displays as 20%, select it and apply Percent format. The keyboard shortcut differs slightly by platform:
- Windows:
Ctrl + Shift + % - Mac:
Control + Shift + %(use the Control key, not Command)
You can also click the % button in the Number group on the Home tab on either platform. Note that applying percent format to a cell that already contains 20 will display it as 2000% — Excel multiplies by 100 for display — so format the cell first, then type, or type the decimal 0.2.
A real worked example: take 20% off a $50 price
Say a $50 item is on sale at 20% off. Here is the cleanest setup:
- Type
50in cell A2 (the original price). - Type
20%in cell B2 (the discount). Excel stores this as 0.2. - In C2, type
=A2*(1-B2)and press Enter.
C2 returns 40. To also show the savings, put =A2*B2 in D2, which returns 10. Format C2 and D2 as currency with Ctrl + Shift + $ (Windows) or Control + Shift + $ (Mac) to display $40.00 and $10.00.
If you would rather skip the helper cell and hardcode the discount, =50*(1-20%) typed directly into any cell also returns 40.
Subtract a percentage down a whole column with absolute references
When you have a column of prices and one shared discount rate, you don’t want to retype the rate in every formula. Put the rate in one cell and lock it with a dollar-sign reference so it doesn’t shift as you fill down.
Suppose prices are in A2:A6 and the discount is in cell E1 (formatted as 20%):
- In B2, type
=A2*(1-$E$1). - Press Enter, then drag the fill handle (the small square at the bottom-right of the cell) down through B6.
The $E$1 is an absolute reference — the dollar signs lock both the column and the row, so every copied formula still points at E1. Meanwhile A2 is relative, so it becomes A3, A4, and so on as you fill down. The result is each price reduced by 20% with a single source rate you can change in one place. For a deeper look at when and why to lock references, read our guide to absolute references in Excel. If you are subtracting different fixed amounts rather than a shared rate, the techniques in subtracting multiple cells in Excel apply.
Percentage decrease: “what percent did it drop?”
Subtracting a percentage takes a number down by a known rate. The reverse question — given an old and a new value, what percentage did it fall? — uses a different formula:
=(new-old)/old
If sales dropped from 500 (old, in A2) to 400 (new, in B2), then =(B2-A2)/A2 returns -0.2, which displays as -20% once you apply percent format. The negative sign tells you it’s a decrease. This is the standard percentage-change calculation; for the full treatment, see calculate percentage change in Excel and the closely related calculate percent change walkthrough. If instead you want the size of the gap between two numbers regardless of direction, our percentage difference guide covers that.
Applying a discount (and the markup mirror)
A discount is just subtracting a percentage, so the formula is identical: =Price*(1-DiscountRate). A 15% discount on $80 is =80*(1-15%), which equals $68.
The mirror operation — adding a percentage, such as a markup, tax, or markup margin — flips the minus to a plus: =Price*(1+Rate). Adding 15% to $80 is =80*(1+15%) = $92. We cover that direction fully in adding a percentage to a number. The general multiply-by-a-factor idea behind both is explained in how to multiply in Excel. If you’re working out a sale price and want to confirm it still clears a target margin, see calculate profit margin in Excel.
Chaining (compounding) multiple percentage reductions
A common mistake is to add two discounts together. Taking 10% off and then 20% off is not the same as taking 30% off. Each reduction applies to the already-reduced amount, so you multiply the factors:
=A1*(1-B1)*(1-C1)
Start with $1000 in A1, 10% in B1, and 20% in C1. The formula does 1000 × 0.9 × 0.8 = 720. A flat 30% off would have given 1000 × 0.7 = 700, so compounding leaves you $20 higher. The order of the reductions doesn’t change the final number, because multiplication is commutative — 0.9 × 0.8 equals 0.8 × 0.9. Add as many *(1-...) factors as you have stacked discounts.
Quick reference
| Goal | Formula | Example result |
|---|---|---|
| Subtract a percentage (percent/decimal cell) | =A1*(1-B1) | 50, 20% → 40 |
| Subtract a percentage (explicit) | =A1-(A1*B1) | 50, 20% → 40 |
| Subtract a whole-number percent (e.g. 20) | =A1*(1-B1/100) | 50, 20 → 40 |
| Discount amount only | =A1*B1 | 50, 20% → 10 |
| Down a column, shared rate | =A2*(1-$E$1) | locks E1 |
| Percentage decrease | =(new-old)/old | 500→400 → -20% |
| Add a percentage (markup/tax) | =A1*(1+B1) | 50, 20% → 60 |
| Chain two reductions | =A1*(1-B1)*(1-C1) | 1000, 10%, 20% → 720 |
Troubleshooting
The result is way off (huge or negative). You almost certainly used a whole number like 20 where Excel expected 0.2. With =A1*(1-B1) and B1 = 20, Excel computes 1 - 20 = -19. Either format B1 as a percentage (so it holds 0.2), type the decimal 0.2, or switch the formula to =A1*(1-B1/100).
The answer is right but shows as a percentage. Excel sometimes copies the format of a referenced cell. If your discounted price displays as 4000% instead of 40, the result cell inherited percent formatting. Select it and apply Number or Currency format from the Home tab. This is purely cosmetic — the stored value is correct.
You forgot the parentheses. Excel follows order of operations, so =A1*1-B1 is read as (A1*1) - B1, not A1*(1-B1). With A1 = 50 and B1 = 0.2 that gives 50 - 0.2 = 49.8 instead of 40. Always wrap the 1-B1 part in parentheses.
You tried to subtract two percentages directly. You cannot meaningfully do 20% - 5% to discount a price — those are rates, not amounts. To stack reductions, multiply the factors as shown in the chaining section. To find the gap between two percentage figures, just subtract the cells (=A1-B1) and format the result as a percentage; if you need it rounded cleanly, see rounding percentages in Excel.
Frequently Asked Questions
How do I subtract a percentage in Excel?
Use =number*(1-percent). If the original value is in A1 and the percentage (formatted as a percent, like 20%) is in B1, the formula =A1*(1-B1) returns the value with that percentage removed. The equivalent =A1-(A1*B1) gives the same answer by subtracting the discount amount explicitly.
How do I subtract a percentage from a number in Excel?
Multiply the number by 1 minus the percentage. For 20% off 80, type =80*(1-20%) to get 64. If your percentage sits in a cell as a plain whole number (like 20 rather than 20% or 0.2), divide by 100 inside the formula: =80*(1-20/100).
How do I take a percentage off a price in Excel?
Put the price in one cell and the discount rate in another formatted as a percentage, then use =Price*(1-Rate). To take 20% off a $50 price, =50*(1-20%) returns $40. For a whole column of prices sharing one rate, lock the rate cell with an absolute reference like =A2*(1-$E$1) and fill down.
Why is my percentage subtraction giving a strange result?
The usual cause is a percentage stored as a whole number. A cell showing 20% holds 0.2 internally, so 1-20% is 0.8. But a cell holding a plain 20 makes 1-20 equal -19, producing a huge negative result. Format the cell as a percentage with Ctrl + Shift + % (Windows) or Control + Shift + % (Mac), or divide by 100 in the formula.
How do I calculate what percentage a price dropped?
Use =(new-old)/old and format the result as a percentage. If a price fell from $500 to $400, =(400-500)/500 returns -0.2, shown as -20%. The negative sign confirms it’s a decrease rather than an increase.
How do I subtract two percentages from a number at once?
Don’t add the percentages together — apply them in sequence by multiplying the factors. The formula =A1*(1-B1)*(1-C1) takes both reductions off the already-reduced amount. Removing 10% then 20% from 1000 gives 720, not the 700 you’d get from a flat 30% off.
