Calculate a Discount in Excel and Google Sheets
Purpose
Calculating discounts in a spreadsheet is one of those daily tasks that sounds trivial but has a few variations worth…
Calculating discounts in a spreadsheet is one of those daily tasks that sounds trivial but has a few variations worth getting right. The discounted price — what the customer actually pays — is the original price multiplied by (1 minus the discount rate). The discount amount — the money saved — is just the original price multiplied by the rate. And the reverse calculation — finding the original price from a sale price when you know the discount percentage — is a division that catches a surprising number of people. I use all three variations regularly: pricing sheets where every product has a tiered discount, margin reports where the discount amount feeds into profit calculations, and sourcing checks where I need to confirm what a vendor's original price was from the discounted quote they sent. Once you have these three formulas in muscle memory, any discount-related question in a spreadsheet becomes a two-second job. The percentage change formula is the natural companion — it calculates how much a value increased or decreased as a percentage, which is the inverse of working from a discount rate.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=original_price * (1 - discount_rate)
For the discount amount only: =original_price * discount_rate
Arguments
| Argument | Required | Description |
|---|---|---|
| original_price | Required | The price before the discount is applied. A cell reference like A2 or a literal number. |
| discount_rate | Required | The discount as a decimal (0.20 for 20%) or as a percentage-formatted cell which stores 0.20 internally. Do not divide by 100 if the cell is already formatted as a percentage. |
How it works
The discounted price formula works by expressing what percentage of the original price the customer pays. A 20% discount means the customer pays 80% of the original price. Multiplying by (1-0.20) = 0.80 applies that 80% directly. This is more reliable than calculating the discount amount first and subtracting it because it is a single step with no intermediate rounding. The discount amount formula is simpler — just multiply by the rate — and gives you the savings figure rather than the final price. For the reverse calculation, dividing the discounted price by (1-rate) undoes the discount: if 68 is 80% of the original, then 68/0.80 = 85 is the original. This is the reverse percentage formula. When discount rates are stored in cells formatted as percentages, Excel and Google Sheets store them as decimals internally (20% is stored as 0.20), so the formula =A2*(1-B2) works correctly without any conversion.
Examples
1
Calculate the discounted price for each product after applying a 20% discount.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Price | Discount | Sale Price |
| 2 | Laptop | 850.00 | 20% | =B2*(1-C2) |
| 3 | Mouse | 29.99 | 20% | 23.99 |
| 4 | Keyboard | 65.00 | 20% | 52.00 |
| 5 | Monitor | 289.99 | 20% | 231.99 |
| 6 | Webcam | 79.00 | 20% | 63.20 |
Row 2: 680.00
— 850 multiplied by (1-0.20) = 850 * 0.80 = 680.00. The 20% discount saves 170.00 and the customer pays 680.00.
The Discount column is formatted as percentage — Excel stores 20% internally as 0.20, so the formula =B2*(1-C2) works correctly without dividing by 100. If the discount were stored as the number 20 (not percentage-formatted), you would need =B2*(1-C2/100).
2
Calculate the discount amount — the money saved — separately from the discounted price.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Price | Discount | You Save | You Pay |
| 2 | Laptop | 850.00 | 20% | =B2*C2 | 680.00 |
| 3 | Mouse | 29.99 | 15% | 4.50 | 25.49 |
| 4 | Keyboard | 65.00 | 10% | 6.50 | 58.50 |
| 5 | Monitor | 289.99 | 25% | 72.50 | 217.49 |
| 6 | Webcam | 79.00 | 20% | 15.80 | 63.20 |
Row 2: 170.00
— 850 multiplied by 0.20 = 170.00 — the saving on the Laptop. The You Pay column uses =B2*(1-C2) to show the final price after the discount.
Showing both the saving and the final price in separate columns is the standard retail receipt format. The You Save amount feeds margin and promotional reports. Each product has a different discount rate in this example — the formula references C2 so it picks up the correct rate for each row automatically.
3
Find the original price when you know the sale price and the discount percentage — the reverse percentage calculation.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Sale Price | Discount | Original Price |
| 2 | Laptop | 680.00 | 20% | =B2/(1-C2) |
| 3 | Mouse | 25.49 | 15% | 29.99 |
| 4 | Keyboard | 58.50 | 10% | 65.00 |
| 5 | Monitor | 217.49 | 25% | 289.99 |
| 6 | Webcam | 63.20 | 20% | 79.00 |
Row 2: 850.00
— 680.00 divided by (1-0.20) = 680/0.80 = 850.00. Dividing by the remaining percentage reverses the discount and recovers the original price.
This reverse calculation is useful when a supplier sends a discounted price and you need to verify or record the original list price. It is also used to calculate markup from a margin percentage, where the logic is identical.
Common use cases
1. Calculate sale prices for a product catalogue after applying a seasonal discount percentage
=B2*(1-C2)
2. Show the discount amount saved in a quote or receipt alongside the discounted final price
=B2*C2
3. Apply different discount rates to different customer tiers by referencing a rate column
=B2*(1-C2)
4. Reverse-calculate the original price from a sale price when the discount percentage is known
=B2/(1-C2)
5. Use IFS to apply a tiered discount automatically based on order quantity before calculating the final price
=B2*(1-IFS(C2>=100,0.20,C2>=50,0.10,TRUE,0.05))
Common errors
Result is much lower than expected
The discount rate is stored as a whole number (20) rather than a decimal (0.20). Multiplying by (1-20) = -19 gives a negative result.
Fix: Format the discount column as Percentage so Excel stores it as 0.20 internally, or divide the raw number by 100 in the formula: =B2*(1-C2/100).
#DIV/0! in the reverse calculation
The discount rate is 100% (stored as 1.0) making the denominator (1-1) = 0.
Fix: A 100% discount means the item is free and has no original price to calculate. Add IFERROR: =IFERROR(B2/(1-C2),"Free").
Rounding causes penny discrepancies
Intermediate calculations create floating-point rounding. The final cell displays correctly but ROUND-based comparisons may fail.
Fix: Wrap the final price formula in ROUND to two decimal places: =ROUND(B2*(1-C2),2). This ensures displayed and stored values match.
Tips and variations
Multiply by (1-rate) not subtract separately for cleaner formulas
=B2*(1-C2) is one step. Calculating the discount first =B2*C2 then subtracting =B2-D2 is two steps and introduces an extra column. The one-step version also avoids rounding differences between the two cells.
=B2*(1-C2)
Apply tiered discounts by using IFS to look up the rate
When discount rates depend on order size, customer tier, or category, use IFS inside the formula to select the right rate automatically rather than maintaining a separate discount column.
=B2*(1-IFS(C2>=100,0.20,C2>=50,0.10,TRUE,0.05))
Use ROUND to avoid penny discrepancies in financial reports
Floating-point arithmetic can produce prices like 23.990000001 which rounds incorrectly in some contexts. Wrapping the result in ROUND to 2 decimal places ensures the stored value matches the displayed value exactly.
=ROUND(B2*(1-C2),2)
Excel vs Google Sheets
Excel vs Google Sheets
The discount formulas use only arithmetic operators and work identically in Excel 2003 and newer and every version of Google Sheets. Percentage formatting behaviour is the same in both applications — a cell formatted as percentage stores the decimal value internally. Formulas copy between the two without changes.
Related reading
Frequently asked questions
To find the discounted price, multiply the original price by (1 minus the discount rate): =A2*(1-B2). For example, =85*(1-0.20) gives 68, which is the price after a 20% discount. To calculate the discount amount itself, multiply the original price by the discount rate: =A2*B2, which gives 17 for a 20% discount on 85.
Use =A2*(1-0.20) or =A2*0.80. Both return the same result — 80% of the original price, which is the price after removing 20%. If the discount percentage is in a cell like B2 as either 0.20 or 20%, then =A2*(1-B2) works regardless of how the percentage is formatted, as long as the cell stores the decimal value.
Divide the discounted price by (1 minus the discount rate): =A2/(1-B2). For example, if an item costs 68 after a 20% discount, =68/(1-0.20) returns 85 — the original price. This is the reverse percentage calculation.
The discount amount is the money saved: =original*discount_rate, e.g. =85*0.20 = 17. The discounted price is what you pay: =original*(1-discount_rate), e.g. =85*(1-0.20) = 68. Most retail formulas want the discounted price. Most margin and savings reports want the discount amount.
Put discount rates in a column next to prices and use a formula that references both: =A2*(1-B2). When the formula is copied down, each row uses its own price and rate. For tiered discounts based on quantity or customer type, use IFS or VLOOKUP to look up the applicable rate first: =A2*(1-VLOOKUP(C2,DiscountTable,2,FALSE)).