How to reverse a percentage in Excel
To recover the value before a percentage change, divide by the multiplier — never subtract the percentage back. In Excel that is =B2/(1-C2) for a decrease and =B2/(1+C2) for an increase.
The formulas
| What you want | Formula | Notes |
|---|---|---|
| Undo a discount | =B2/(1-C2) | B2 = sale price, C2 = discount |
| Undo an increase or markup | =B2/(1+C2) | B2 = final value, C2 = increase |
| Remove 20% UK VAT | =B2/1.2 | Or =B2/(1+$C$1) with the rate in one cell |
| Just the VAT portion | =B2-B2/1.2 | Gross minus net |
| Part ÷ percentage = whole | =A2/B2 | A2 = 30, B2 = 40% gives 75 |
| Two consecutive changes | =B2/((1+C2)*(1-D2)) | Multiply multipliers; never add rates |
The mistake that returns a negative number
By far the most common Excel failure here is entering 20 where the formula expects 20%. Excel reads a bare 20 as 2000%, so (1-C2) evaluates to −19 and an £80 sale price "reverses" to about −£4.21. The answer is so obviously wrong that people usually catch it — the dangerous version is a rate of 0.2 typed as 0.2%, which is only slightly off and looks entirely plausible.
Two fixes. Format the column as Percentage (Ctrl+Shift+%) and type 20%, or keep whole numbers and divide inside the formula: =B2/(1-C2/100). Pick one convention for the entire sheet — mixing them is how one column ends up producing two different answers.
Filling down a column
When one VAT rate applies to every row, put it in a single cell and lock it with an absolute reference so dragging does not walk the reference down the sheet:
C1: 20% <- the rate, entered once
B2: 480 <- gross
D2: =B2/(1+$C$1) <- net, drag this down
E2: =B2-D2 <- the VAT portionWithout the dollar signs, row 3 references C2, row 4 references C3, and the column fills with #DIV/0! or — worse — quietly wrong figures that still look like money.
Rounding and reconciliation
Excel keeps full precision internally but displays a rounded figure, so a net column can show values that appear not to add back to the gross. Where the reconciliation has to hold exactly — an invoice, a VAT return — wrap the result: =ROUND(B2/1.2,2). Round once, at the point the number becomes money, and never round an intermediate step: rounding twice compounds the error instead of removing it.
What the error values mean here
#DIV/0! — the multiplier came out as zero. In a reverse-discount formula that means the discount cell holds 100%: after a 100% reduction the value is zero and no original can be recovered, so Excel is right to refuse. Wrap it if the sheet must stay tidy: =IFERROR(B2/(1-C2),"n/a") — but understand you are hiding an impossible question, not fixing a formula.
#VALUE! — one of the cells contains text. Numbers pasted from a website or a PDF very often arrive as text with a stray space or a non-breaking space attached. =ISNUMBER(B2) tells you in one step; =VALUE(TRIM(B2)) usually repairs it.
A negative result — the percentage exceeds 100%, so (1-C2) went below zero. Either the rate is wrong, or the change was an increase and the formula should use (1+C2).
A result that is far too large — the classic 20-instead-of-20% error above, or a rate entered as 0.2 in a cell already formatted as a percentage, which Excel reads as 0.2%.
Check a formula against the calculator
Enter the same numbers here and open "Show steps". If the two disagree, the spreadsheet is the one that is wrong.
Frequently asked questions
›What is the Excel formula to remove a percentage increase?
Use =B2/(1+C2) where B2 is the final value and C2 is the percentage formatted as a percent. For 20% VAT on a gross figure in B2, =B2/1.2 works just as well.
›What is the Excel formula to find the price before a discount?
Use =B2/(1-C2). If B2 is 80 and C2 is 20%, the result is 100.
›Why does my Excel reverse percentage return a negative number?
Almost always the percentage cell holds 20 rather than 20%. Excel reads that as 2000%, so (1-C2) becomes -19. Format the cell as Percentage, or divide by 100 inside the formula.
›How do I reverse two percentage changes in Excel?
Divide by the product of the multipliers: =B2/((1+C2)*(1-D2)). Do not add or subtract the rates.
›Do these formulas work in Google Sheets?
Yes. Every formula on this page works unchanged in Google Sheets, LibreOffice Calc and Numbers.