How to Build a Discount Calculator in Excel and Google Sheets (Formulas Explained)
Every discount calculation — percentage off, stacked discounts, reverse original-price lookups, and tiered bulk pricing — comes down to a handful of one-line formulas. Here is exactly how to set them up.
A spreadsheet discount calculator is one formula, copied down a column — the same way a running balance or a loan schedule is one formula repeated. The part that actually trips people up is not the arithmetic, it is deciding which of four closely related formulas a given situation calls for: a straight percentage off, a fixed amount off, a stack of several discounts, or working backward from a sale price to find the original.
This guide covers all four, plus a tiered/bulk-discount lookup for anyone pricing by quantity. Every formula below works identically in Excel and Google Sheets unless a note says otherwise.
The Core Layout
Before any formula, set up four columns: Original Price, Discount %, Discount Amount, and Sale Price. Enter the discount percentage as a real percentage (format the cell as %, and type 25 to get 25%) so it behaves correctly in every formula that follows.
Swipe sideways to compare columns.
| Column | Header | Contains |
|---|---|---|
| A | Original Price | A typed number, e.g. 120 |
| B | Discount % | A typed percentage, e.g. 25% |
| C | Discount Amount | Formula: =A2*B2 |
| D | Sale Price | Formula: =A2-C2 |
The Basic Percentage-Off Formula
Swipe sideways to compare columns.
| Cell | Formula | Result |
|---|---|---|
| A2 | 250 (typed) | $250.00 |
| B2 | 30% (typed) | 30% |
| C2 | =A2*B2 | $75.00 |
| D2 | =A2-C2 | $175.00 |
A Fixed-Amount Discount (a Flat "$X Off" Coupon)
A flat coupon skips the percentage entirely — subtract the coupon amount directly from the original price.
Stacked Discounts in a Single Cell
When two discounts apply in sequence — a storewide sale plus a checkout coupon, for example — chain the "remaining price" multipliers in one formula instead of building a discount-amount column for each one.
Swipe sideways to compare columns.
| Cell | Formula | Result |
|---|---|---|
| A2 | 250 (typed) | $250.00 |
| B2 | 20% (typed) | 20% |
| C2 | 10% (typed) | 10% |
| D2 | =A2*(1-B2)*(1-C2) | $180.00 |
Working Backward: Finding the Original Price
Given a sale price and the discount percentage that produced it, divide instead of multiply.
Swipe sideways to compare columns.
| Cell | Formula | Result |
|---|---|---|
| A2 | 180 (typed) | $180.00 |
| B2 | 25% (typed) | 25% |
| C2 | =A2/(1-B2) | $240.00 |
Finding the Discount Percentage From Two Prices
If you have both the original and sale price already — from a receipt, a competitor listing, or a price-history tool — calculate the percentage they represent instead of guessing.
Tiered or Bulk Discounts With VLOOKUP
Quantity-based pricing needs a small lookup table rather than one flat percentage. Build a two-column table with the minimum quantity for each tier in ascending order, then look up the matching rate for any order size.
Swipe sideways to compare columns.
| Min Quantity | Discount % |
|---|---|
| 1 | 0% |
| 10 | 5% |
| 25 | 10% |
| 50 | 15% |
| 100 | 20% |
Swipe sideways to compare columns.
| Cell | Formula | Result |
|---|---|---|
| A2 | 37 (typed quantity) | 37 |
| B2 | 12 (typed unit price) | $12.00 |
| C2 | =VLOOKUP(A2,$F$2:$G$6,2,TRUE) | 10% |
| D2 | =A2*B2*(1-C2) | $399.60 |
Manual Tier Lookup vs. VLOOKUP/IFS
Same pricing table, two very different ways to apply it.
Manual Lookup
You check the quantity against the table by eye and manually type the matching discount percentage into each order.
- Breaks silently if you misread a row
- Needs re-checking every time tiers change
- Slow for large order lists
VLOOKUP or IFS Formula
The correct tier is selected automatically from the quantity in the same row — change the table once and every order recalculates.
- Update tier thresholds in one place
- Copies down instantly for any order list length
- No manual re-checking required
The formula version scales to any order quantity without a new IF statement for every possible size.
Common Spreadsheet Discount Mistakes
- Typing a discount as a whole number (25) instead of a percentage (25%) — =A2*25 charges 25 times the price, not a 25% cut.
- Adding stacked discount percentages together in one formula instead of chaining separate (1-discount) factors.
- Leaving the VLOOKUP table unsorted — an approximate-match VLOOKUP (the TRUE argument) gives silently wrong results on an unsorted range.
- Forgetting to lock the lookup table range with $ signs before copying the formula down, which shifts the range on every row.
- Mixing a fixed dollar coupon and a percentage discount in the same column, so a formula built for one misreads the other.
Excel vs. Google Sheets: What Actually Differs
Every formula in this guide — the percentage formulas, the stacked-discount multiplication, VLOOKUP, and IFS — works identically in both programs, using the same syntax. The differences that do exist are minor: Google Sheets also supports XLOOKUP-style flexibility through its own newer functions, and applying one formula across an entire column without dragging uses ARRAYFORMULA in Sheets versus filling down or a Table in Excel. For a standard discount calculator, none of that is necessary — build it once, copy the formula down, and it behaves the same in either program.
Frequently Asked Questions
Should the discount percentage cell use a % format or a plain decimal?
Either works as long as the formulas match the format. Formatting the cell as a percentage and typing 25 (which displays as 25%) is the most common approach and avoids confusion when reading the sheet later. If you use a plain decimal instead, type 0.25 rather than 25.
Why did my stacked discount formula give a bigger discount than expected?
This almost always means the discounts were added inside the formula, e.g. =A2*(1-(B2+C2)), instead of multiplied as separate factors, e.g. =A2*(1-B2)*(1-C2). Adding first always produces a larger discount than the correct compounding method.
Can I use VLOOKUP for tiered discounts if the table is sorted in descending order?
No — an approximate-match VLOOKUP (the TRUE argument) requires the lookup column sorted in ascending order to return correct results. If your table is descending, either re-sort it or switch to an IFS formula, which does not require sorted data.
How do I find the original price if I only know the sale price and the dollar amount saved?
Add them back together: Original Price = Sale Price + Discount Amount, or simply =A2+B2 if A2 holds the sale price and B2 the amount saved.
Does this discount calculator handle sales tax?
Not directly — these formulas return the discounted price before tax. Add a separate column that multiplies the sale price by (1 + tax rate) after the discount is applied, since tax is normally calculated on the post-discount price.
Related Reading
Discount Calculator — Run any of the scenarios above instantly online, with full step-by-step work shown.
Discount Calculator: A Complete Guide to Calculating Discount Percentage — The underlying formulas explained in plain language, plus how to spot a fake "original" price.
Stacked Discounts & BOGO Deals — Why compounding discounts save you less than the sign suggests, with more worked examples.
Once your pricing sheet is built, the Markup Calculator is a natural next step for checking the profit side of the same numbers.
Written by
Do The Calculation Team
Do The Calculation Editorial Board
The Do The Calculation Editorial Board is comprised of software engineers, finance analysts, and technical contributors focused on building clean, accurate, and easy-to-use calculator tools.