# 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.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/discount-calculator-excel-google-sheets-guide
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 10 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

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.

**Column Layout (row 2 is the first data row)**
| 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

**Two-step version (matches how most people learn the math)**

```
Discount Amount: =A2*B2
Sale Price: =A2-C2
```
- Copy both formulas down the column once row 2 is correct — every row below inherits the same relative references automatically.

**One-cell shortcut version**

```
Sale Price: =A2*(1-B2)
```
- Skips the separate Discount Amount column entirely. Use whichever version matches how the rest of your sheet is built — both give an identical result.

**Worked Example: $250 Item at 30% Off**
| 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.

**Fixed discount formula**

```
Sale Price: =A2-B2
```
- Here B2 holds a dollar amount, not a percentage — keep fixed-amount coupons in a separate column from percentage discounts so the formulas below don't misread one as the other.

## 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.

**Two stacked discounts**

```
Sale Price: =A2*(1-B2)*(1-C2)
```
- B2 and C2 each hold a discount percentage. Add another *(1-D2) factor for a third stacked discount, and so on.

**Worked Example: $250 Item, 20% Off Then an Extra 10% Off**
| Cell | Formula | Result |
| --- | --- | --- |
| A2 | 250 (typed) | $250.00 |
| B2 | 20% (typed) | 20% |
| C2 | 10% (typed) | 10% |
| D2 | =A2*(1-B2)*(1-C2) | $180.00 |

> **Do Not Add the Percentages Into One Cell** — A formula like =A2*(1-(B2+C2)) looks tidy but is mathematically wrong for stacked discounts — it treats 20%+10% as a flat 30% off the original price ($175), instead of the correct sequential result of $180. Always multiply the separate (1-discount) factors; never add the discounts before subtracting from 1.

## Working Backward: Finding the Original Price

Given a sale price and the discount percentage that produced it, divide instead of multiply.

**Original price from sale price and discount %**

```
Original Price: =A2/(1-B2)
```
- A2 holds the sale price here, B2 the discount percentage — the opposite roles from the earlier examples, so keep column headers clear if both formulas live on the same sheet.

**Worked Example: $180 Sale Price, 25% Discount**
| 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.

**Discount percentage from two known prices**

```
Discount %: =(A2-B2)/A2
```
- Format the result cell as a percentage. A2 is the original price, B2 the sale price. For a $250 item now selling at $180, this returns 28%.

## 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.

**Discount Tier Table (place this in its own range, e.g. F2:G6)**
| Min Quantity | Discount % |
| --- | --- |
| 1 | 0% |
| 10 | 5% |
| 25 | 10% |
| 50 | 15% |
| 100 | 20% |

**Approximate-match VLOOKUP**

```
Discount %: =VLOOKUP(A2,$F$2:$G$6,2,TRUE)
```
- The final argument, TRUE, tells VLOOKUP to find the largest tier threshold that is still less than or equal to A2 — this only works correctly when the tier table is sorted in ascending order by quantity.
- Lock the table range with dollar signs ($F$2:$G$6) so it stays fixed when the formula is copied down a column of different order quantities.

**Equivalent IFS formula (no separate table needed)**

```
Discount %: =IFS(A2>=100,20%,A2>=50,15%,A2>=25,10%,A2>=10,5%,TRUE,0%)
```
- IFS checks conditions in the order they're written and stops at the first TRUE — list the highest quantity threshold first, or a smaller tier will match before a larger one gets checked.

**Worked Example: 37 Units at $12 Each**
| 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 |

_[Figure: Manual Tier Lookup vs. VLOOKUP/IFS — Same pricing table, two very different ways to apply it.]_

## 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](/calculators/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](/blog/business/discount-percentage-complete-guide) — The underlying formulas explained in plain language, plus how to spot a fake "original" price.

[Stacked Discounts & BOGO Deals](/blog/business/stacked-discounts-bogo-deals-guide) — Why compounding discounts save you less than the sign suggests, with more worked examples.

Once your pricing sheet is built, the [Markup Calculator](/calculators/markup-calculator) is a natural next step for checking the profit side of the same numbers.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/discount-calculator-excel-google-sheets-guide). Quote freely with attribution and a link to this page._