How to Calculate Discounts and Sale Prices in Excel
By ExcelReady TeamUpdated 6 min read
The short answer
To calculate a discounted price in Excel, multiply the original price by one minus the discount: =B2*(1-C2), where B2 is the price and C2 is the discount as a percentage, such as 20%. The amount saved is =B2*C2. To find the discount percent from two prices, use =1-SalePrice/OriginalPrice and format it as a percentage.
Key takeaways
- Sale price is =Price*(1-Discount): 80 with 25% off is =80*(1-25%), which is 60.
- The amount saved is =Price*Discount, and the discount percent is =1-Sale/Original.
- To get the original price back from a sale price, divide: =Sale/(1-Discount).
- Two discounts in a row multiply, so 20% then 10% off is 28% off, not 30%.
- Wrap prices in ROUND(...,2) so totals use the same cents the customer sees.
On this page+
To calculate a discount in Excel, you need one idea: a price with 25% off is 75% of the price. So the sale price is =Price*(1-Discount), and the amount saved is =Price*Discount. This guide shows both, plus how to work backwards to the discount percent or the original price, how to stack two discounts, and how to round to the cent.
How discounts work in Excel#
A discount is a percentage of the original price. In Excel, a percentage is just a decimal with a % format: 25% is stored as 0.25. That means you can multiply by it directly.
| You want | Formula | Example (80 with 25% off) |
|---|---|---|
| Amount saved | =B2*C2 | 20 |
| Sale price | =B2*(1-C2) | 60 |
| Sale price (other way) | =B2-B2*C2 | 60 |
The brackets in (1-C2) matter. Excel multiplies before it subtracts, so =B2*1-C2 would give 79.75, not 60. The brackets make Excel work out 1 minus the discount first.
All the examples below use this price list in A1:D6:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Price | Discount | Sale price |
| 2 | Headphones | 80.00 | 25% | |
| 3 | Backpack | 45.00 | 10% | |
| 4 | Desk lamp | 32.99 | 15% | |
| 5 | Monitor | 219.00 | 30% | |
| 6 | Keyboard | 59.50 | 20% |
How to calculate the sale price#
Multiply each price by one minus its discount, then fill the formula down the column.
- Click D2 and type
=B2*(1-C2). - Press Enter. The result is 60.
- Select D2:D6 and fill down (Windows: Ctrl+D; Mac: ⌘D).
The sale prices are 60, 40.50, 28.0415, 153.30 and 47.60. The desk lamp shows the problem with percentages: 32.99 × 0.85 is 28.0415, which is not a real price. The next section fixes that.
If every product gets the same discount, put the discount in one cell, say F1, and lock it with dollar signs: =B2*(1-$F$1). The $ keeps the formula pointing at F1 as you fill down. The lesson on percentages and % change walks through this pattern step by step.
How to round discounted prices to the cent#
Wrap the formula in ROUND with 2 decimal places, so the stored value matches what the customer pays.
=ROUND(B4*(1-C4),2)For the desk lamp this returns 28.04. Formatting the cell to 2 decimals would also show 28.04, but Excel would still calculate with 28.0415. Add up a few hundred lines like that and your total can be a few cents off the receipts. ROUND changes the value itself.
You can also round in a fixed direction:
=ROUNDUP(B4*(1-C4),2)always rounds up, giving 28.05.=ROUNDDOWN(B4*(1-C4),2)always rounds down, giving 28.04, which is kinder to the customer.
The ROUND function guide has the full syntax, and the ROUND lesson lets you practise all three. For psychological prices like 27.99, see how MOD and INT split numbers into parts.
How to calculate the discount percentage from two prices#
When you know the original price and the sale price, the discount percent is the drop divided by the original price.
=(B2-C2)/B2Here B2 holds the original price and C2 the sale price. If something drops from 120 to 90, the result is 0.25. Format the cell as a percentage (Home > %, or Ctrl+Shift+% on Windows and ⌃⇧% on Mac) to see 25%.
A shorter version gives the same answer:
=1-C2/B2Always divide by the original price, not the sale price. 30 divided by 90 is 33%, which is the wrong answer for "how much off". It is the same rule as any percent change, which the guide to the percent change formula explains in more detail.
How to find the original price before a discount#
Divide the sale price by one minus the discount. This undoes the multiplication.
=C2/(1-D2)If a jacket costs 63 after 30% off, =63/(1-30%) returns 90. A common mistake is to add 30% back on: 63 × 1.3 is 81.90, which is too low. That happens because 30% of 63 is smaller than 30% of 90. The same trap appears when you raise prices, so read how to increase a number by a percentage if you work in both directions.
How to apply two discounts in a row#
Stacked discounts multiply, they don't add. A coat with 20% off, plus an extra 10% off at the till, costs:
=B2*(1-20%)*(1-10%)On a price of 100 this gives 72, which is a total discount of 28%, not 30%. The second discount is taken from the already reduced price. To show the combined discount as one percentage, use =1-(1-20%)*(1-10%), which returns 28%.
If the discounts sit in cells C2 and D2, write =B2*(1-C2)*(1-D2) and fill down.
Common discount mistakes to avoid#
Most wrong discount results come from one of these:
- Discount typed as a whole number. If C2 holds 20 instead of 20%,
=B2*(1-C2)multiplies by -19. Type the % sign, or use=B2*(1-C2/100). - Missing brackets.
=B2*1-C2subtracts the discount from the price as a number. Keep(1-C2)in brackets. - Dividing by the wrong price. The discount percent is based on the original price.
- Adding stacked discounts. 20% plus 10% is 28% off, not 30%.
- Rounding only with formatting. Use ROUND when the result is money that will be totalled.
A discount is the mirror image of a profit margin. Once you have the sale price, you can check what you still earn on it with the profit margin and markup formulas. For setting money aside rather than spending it, the savings goal calculator uses the same percentage maths.
Practise it#
Discount questions are a favourite in basic Excel tests because they check percentages, brackets and rounding in one line. Practise percentages and % change in a live spreadsheet: you type the formula and it checks it instantly. Then practise ROUND, ROUNDUP and ROUNDDOWN to keep every price to the cent. When you feel ready, try the basic Excel test under time pressure.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Find the sale price of the Desk lamp, rounded to cents. Click D4, multiply the price in B4 by one minus the discount in C4, and wrap it in ROUND with 2 decimals.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Sale price | ||||
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
| 6 | |||||
| 7 | |||||
| 8 | |||||
| 9 |
Tip: Start with =ROUND( then B4*(1-C4), then a comma and 2.
Practise this in a lesson
Frequently asked questions
What is the formula for a discount in Excel?
For the sale price, use =Price*(1-Discount), for example =B2*(1-C2). For the amount saved, use =Price*Discount. Enter the discount as a percentage, like 20%, or as a decimal, like 0.2.
How do I calculate the discount percentage between two prices?
Use =1-SalePrice/OriginalPrice, or =(OriginalPrice-SalePrice)/OriginalPrice, and format the cell as a percentage. A price that drops from 120 to 90 gives 25%.
How do I take 20% off a price in Excel?
Multiply the price by 80%, which is 1 minus 20%. If the price is in B2, type =B2*(1-20%) or =B2*0.8. For 50 the result is 40.
How do I find the original price before a discount?
Divide the sale price by one minus the discount: =SalePrice/(1-Discount). If something costs 63 after 30% off, =63/(1-30%) returns 90.
Why does my discount formula show a huge number?
The discount was probably typed as 20 instead of 20% or 0.2. Excel then multiplies by 1-20, which is -19. Type 20% with the percent sign, or divide the number by 100.