Cellwise Try it free

Excel guide · 3 min read

How to calculate a discount price in Excel

Quick answerMultiply the original price by 1 minus the discount: =B2*(1-C2). For $129.99 at 25% off, that is $97.49. To find the percentage off from two prices, use =1-new/old. Two discounts in a row multiply, so 30% off plus an extra 10% is 37% off, not 40%.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFGH
1ItemList priceDiscountWasNowExtra code
2Trail running shoes129.990.258059.990.1
3Rain jacket89.50.3
4Daypack 22L640.15
5Insulated bottle24.950.1
6Headlamp39.990.2

Download this sheet as an Excel practice file (.xlsx)

Sale price from a percentage off

Keep the discount as a percentage in its own column. 1 - 25% is 75%, the share of the price the customer still pays. Wrap it in ROUND so the result is a real price in cents; otherwise Excel keeps $97.4925 underneath, and totals of many lines can drift by a cent or two.

Keeping the discount in a column (rather than typing 0.75 into the formula) means you can run a different discount on every line and change a promotion without touching any formulas. Fill the formula down from D2 to D6.

=ROUND(B2*(1-C2),2)

Result: 97.49

$129.99 x 75% = $97.49.

The discount amount (how much you save)

Price times discount gives the money off. Show it next to the sale price on a quote or receipt so the saving is clear. The rain jacket at 30% off saves $26.85.

If you need the total saved across an order, =SUMPRODUCT(B2:B6,C2:C6) multiplies each price by its discount and adds them, no helper column needed.

=B3*C3

Result: 26.85

$89.50 x 30% = $26.85 off.

Percentage off from the old and new price

Divide the new price by the old one to get the share still paid, then subtract from 1. Format as Percentage. An $80 item now at $59.99 is 25% off (25.01% if you show two decimals).

An equivalent version is =(E2-F2)/E2: the saving divided by the original price. Always divide by the original price, never by the sale price.

=1-F2/E2

Result: 0.250125 shows as 25% with Percent format

About 0.25, so the item is 25% off.

Stacking two discounts

An extra 10% off "the sale price" is 10% of the already reduced price, so the discounts multiply: 70% x 90% = 63% of the original, a 37% total discount. Adding them (30% + 10% = 40%) overstates the saving.

The extra code is in H2, so the formula reads: list price, times what is left after the first discount, times what is left after the second.

=ROUND(B3*(1-C3)*(1-$H$2),2)

Result: 56.39

$89.50 x 0.7 x 0.9 = $56.39, which is 37% off the list price, not 40%.

Original price from a sale price

To work backwards, divide the sale price by 1 minus the discount. If the shoes are $97.49 after 25% off, the original was $97.49 / 0.75. Because the sale price was rounded, the result can be off by a fraction of a cent, so ROUND it again.

The trap is sale price x (1 + discount): $97.49 x 1.25 gives $121.86, which is wrong.

=ROUND(97.49/(1-C2),2)

Result: 129.99

$129.99, the original list price.

Common mistakes

Discount typed as 25 instead of 25%

=B2*(1-25) returns a large negative number. Type 25% or 0.25, or divide in the formula: =B2*(1-C2/100).

Writing =B2-C2 for the sale price

That subtracts 0.25 dollars, not 25%. Use =B2*(1-C2), or =B2-B2*C2.

Adding stacked discounts

Successive discounts multiply. Use =price*(1-d1)*(1-d2). Check the terms, because some promotions really do add both percentages to the original price.

Percentage off divided by the sale price

(old-new)/new gives 33.4% for the $80 to $59.99 item. The correct base is the original price: 25%.

FAQ

What is the formula for a discount in Excel?

Sale price = original x (1 - discount), written as =B2*(1-C2). The saving is =B2*C2.

How do I calculate 20% off in Excel?

Multiply by 0.8: =B2*0.8, or =B2*(1-20%). A $39.99 headlamp becomes $31.99 after rounding.

How do I find the discount percentage between two prices?

Use =1-new/old and format as a percentage. $80 down to $60 is =1-60/80, which is 25%.

How do I give a bigger discount for bigger orders?

Make a small table of order sizes and discount rates, sorted from smallest, for example 1 = 0%, 10 = 5%, 50 = 10%. Then use =price*(1-VLOOKUP(qty,table,2,TRUE)), the same approximate-match lookup used for tiered commission.

How do I apply a discount to a whole column of prices?

Put the discount in one cell and use =B2*(1-$C$1), filled down. To overwrite prices in place, copy a cell holding 0.8 and use Paste Special > Multiply.

Functions used

Related guides

Updated