A discount in Excel is calculated by multiplying the original price by the discount rate expressed as a decimal, then subtracting the result from the original price; the simplest single-cell formula is =A2*(1-B2), where A2 holds the original price and B2 holds the discount as a decimal (for example, 0.20 for 20% off). If you only need the dollar amount of the discount itself, use =A2*B2. To recover the percentage when you know the original and sale prices, use =(A2-C2)/A2, where C2 is the sale price. These three formulas cover roughly ninety percent of everyday retail and finance work in a spreadsheet, and once you have them down, the rest of this article is about handling the edge cases that catch people out — like stacked coupons, negative numbers, and bulk price lists where you need one formula copied down an entire column.
Spreadsheets are useful for discounts because the math is repetitive: a hundred-row product list, a seasonal promotion, or a comparison of three competing sale prices all involve the same operation applied to many cells. Knowing the formula is one thing; building a clean, error-resistant sheet is another. The sections below walk through the formulas in order of complexity, show a worked example you can copy, and explain the one trap that even experienced spreadsheet users get wrong: combining two or more discounts that apply one after another.
The Three Formulas You Actually Need
Before reaching for anything fancy, memorize these three formulas. They use the standard layout where column A is the original price, column B is the discount rate (formatted as a percentage, so you type 20% and Excel stores 0.20), and column C is the sale price. Row 1 is the header.
| Goal | Formula | What it returns |
|---|---|---|
| Final sale price | =A2*(1-B2) | What the customer actually pays |
| Discount in dollars | =A2*B2 | Amount saved on the transaction |
| Discount percentage from a known sale price | =(A2-C2)/A2 | Effective % off, useful for receipts |
The (1-B2) trick is the cleanest way to write a discount in Excel because you can read the formula at a glance: "the original, reduced by the discount rate." It also extends naturally to the stacking case described later. A common variant is =A2-A2*B2, which produces the same number but is harder to audit when you are scanning a column of formulas later.
Build a Discount Sheet in Excel Step by Step
This walkthrough assumes a fresh workbook and a small product list. Each step is something you can repeat on your own data.
- Set up the headers. In A1 type "Original Price", in B1 type "Discount %", in C1 type "Sale Price", and in D1 type "You Save". Format B1 as a percentage using Home → Number → Percent.
- Enter sample data. In A2 put 120, in B2 put 0.20 (or type 20 and confirm the percent format displays 20%). The sale price should appear in C2 as soon as you type =A2*(1-B2) and press Enter.
- Add the savings column. In D2 enter =A2*B2. This is the dollar amount the buyer keeps.
- Copy the formulas down. Select C2:D2, grab the small square at the bottom-right corner of the selection, and drag down through your last row of data. Excel adjusts the row numbers automatically.
- Lock the discount with absolute references. If the same 20% applies to every product, put 0.20 in a fixed cell like F1 and write =A2*(1-$F$1) in C2. The dollar signs stop the reference from shifting as you copy down.
- Format the output. Highlight C2:D2 and apply a currency format so the values display as $96.00 and $24.00 rather than raw numbers.
After step 2 your C2 cell should read 96 and D2 should read 24. That result is the sanity check: a $120 item at 20% off costs $96 and saves $24. If your numbers do not match, the most likely culprit is that B2 was typed as 20 instead of 0.20 and the cell was not formatted as a percentage, which would make the sale price 2,400 instead of 96.
Why Stacked Coupons Are the Trap
Stores love to advertise "20% off, then take an extra 10% at the register." Shoppers love to add those numbers and assume they are getting 30% off. They are not. The discounts are applied sequentially, which means the second percentage is taken off the already-reduced price, not the original price. The combined reduction is always less than the sum of the percentages.
Doing this correctly in Excel is a one-line change. Replace the single-coupon formula in C2 with =A2*(1-B2)*(1-C2), where B2 is the first discount and C2 is the second. You can keep chaining factors for a third or fourth coupon by adding more (1-x) terms. The discount in dollars for the same example is =A2 - A2*(1-B2)*(1-C2), which can also be written as =A2*(1-(1-B2)*(1-C2)). The number in parentheses after that final 1- is what the receipt would call the "effective discount," and it is the figure you should compare against marketing language.
This is exactly the case where a quick verification saves you from trusting your own arithmetic. The Discount Calculator lets you type the original price, click "Stack another discount," and see both the final price and the true combined percentage immediately. It is the fastest way to confirm what your Excel formula should produce before you hand the sheet to anyone else.
Edge Cases Worth Knowing
Most discount sheets are straightforward, but a few situations deserve their own formula or workaround.
- Reverse-engineering a percentage. When you have only the original and sale price (for example, on an old receipt), =(Original-Sale)/Original gives you the effective discount as a decimal. Format the cell as a percentage to display it the way shoppers expect to read it.
- Multiple prices for the same item. A SUMPRODUCT formula such as =SUMPRODUCT(A2:A10, (1-B2:B10)) returns the total revenue from a basket where every row has its own discount rate. This is useful for invoice summaries.
- Negative or zero discounts. A 0% discount should return the original price unchanged, and the formula handles that naturally. Negative discounts (markups) work the same way: a -10% entry raises the price by 10% because (1-minus-negative) is greater than one.
- Whole-number percentage input. If your team prefers to type 20 rather than 0.20, divide by 100 inside the formula: =A2*(1-B2/100). Pick one convention per sheet and stick to it; mixing the two is the single most common source of discount errors.
Double-Checking With a Dedicated Tool
Once your formulas are in place, the safest habit is to spot-check a few rows against a separate calculation. The Discount Calculator is built for exactly this: enter the original price and the first discount, click "Stack another discount" for each additional coupon, and read the final price, the total savings, and — when stacking — the true combined effective discount. It applies percentages sequentially the way real registers do, which is the only correct way for stacked coupons. Comparing one or two of your Excel rows against the tool catches transcription errors, rounding issues, and the occasional accidental absolute reference that has drifted the wrong way.
For shoppers, the tool is also useful on its own. If you are trying to decide whether a "30% off" sign beats a "20% off plus extra 10% coupon" deal, type both scenarios and read the final prices side by side. The answer is rarely what the signage suggests, which is why the calculator shows the effective combined percentage explicitly rather than asking you to do the multiplication in your head.
Putting It All Together
You now have a single formula for the common case (=A2*(1-B2)), a chained version for stacked coupons (=A2*(1-B2)*(1-C2)), and the reverse formula for working backward from a sale price. Those three cover almost every situation a personal finance sheet or a small-business price list will throw at you. Add absolute references when a single rate applies to many rows, format the discount column as a percentage so the cell value matches what you typed, and use the Discount Calculator whenever a stack of coupons makes the mental math feel fuzzy. For a deeper look at how stacked coupon math compares in practice, the guide on calculating discounts in Excel and comparing stacked coupons accurately walks through additional scenarios. If you also want to model how the money you save grows over time, the Compound Interest Calculator and the Savings Calculator are good next steps once the discount side of the spreadsheet is settled.
For a deeper look, see How to Calculate Affordable Home Purchase Price.
For a deeper look, see How to Calculate Loan Payment Timeline With a Fixed Budget.