Excel guide · 4 min read
How to calculate markdown percentage in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SKU | Department | Type | Original price | Markdown price | Units sold | Unit cost | Markdown $ | Sales $ |
| 2 | Wool scarf | Accessories | Promo | 40 | 28 | 35 | 16 | 420 | 980 |
| 3 | Leather belt | Accessories | Permanent | 55 | 38.5 | 22 | 20 | 363 | 847 |
| 4 | Rain shell | Outerwear | Permanent | 120 | 84 | 18 | 52 | 648 | 1512 |
| 5 | Puffer vest | Outerwear | Promo | 95 | 76 | 26 | 41 | 494 | 1976 |
| 6 | Denim jacket | Outerwear | Permanent | 110 | 66 | 14 | 48 | 616 | 924 |
| 7 | Cotton tee | Tops | Promo | 22 | 17.6 | 60 | 7 | 264 | 1056 |
| 8 | Linen shirt | Tops | Permanent | 48 | 33.6 | 25 | 19 | 360 | 840 |
| 9 | Hoodie | Tops | Promo | 60 | 45 | 40 | 24 | 600 | 1800 |
Download this sheet as an Excel practice file (.xlsx)
Markdown % for one SKU
The sheet above is a clothing store's end-of-season markdown list: original ticket price, the reduced price, units sold at that price and unit cost. Columns H and I are helper formulas, copied down: markdown dollars =(D2-E2)*F2 and sales dollars =E2*F2.
The percentage off the original price is the number that goes on the ticket and the one buyers argue about. Subtract, divide by the ORIGINAL price, and format as a percentage (Home > %).
Result: 0.3 shows as 30% with Percent format
The scarf went from $40 to $28: 30% off. The same formula on row 6 says the denim jacket is 40% off.
Total markdown dollars with SUMPRODUCT
The percentage on a ticket tells you nothing about cost until you multiply by how many sold at that price. Markdown dollars are the price cut times the units sold, added up across every SKU. SUMPRODUCT does it in one cell without the helper column.
Count only units that actually sold at the reduced price. Units still on the shelf haven't cost you anything yet; units sold at full price before the markdown don't belong here at all.
Result: 3765
$3,765 of markdowns this season. The same total as =SUM(H2:H9).
Markdown % of sales: the number retail reports use
Merchandise plans and retail reports usually express markdowns as a percentage of net sales: markdown dollars divided by the sales dollars you actually took. It's what lets you compare a $2 million department with a $200,000 one.
Expect it to look bigger than the ticket percentage. Every item in Accessories is exactly 30% off, yet Accessories markdowns are 42.9% of its sales, because the base is the reduced price, not the original. That's not an error. Just label which one you mean.
Result: 0.3789632612 shows as 37.9% with Percent format
37.9% for the whole list: $3,765 of markdowns on $9,935 of sales.
By department with SUMIFS
Divide the department's markdown dollars by the department's sales. Put the department name in a cell instead of typing it, so one formula fills a summary table.
Result: 0.39845874887 shows as 39.8% with Percent format
39.8% for Outerwear. Accessories comes out at 42.9% and Tops at 33.1%.
Markdowns against the original value instead
Some teams divide by sales at original price. It gives the average depth of the cut across what you sold. Use whichever your plan uses, and never compare one with the other.
Result: 0.27481751825 shows as 27.5% with Percent format
27.5%: $3,765 off $13,700 of goods at original price.
Permanent vs promotional markdowns
A permanent markdown is a price cut that stays until the item is gone, usually to clear slow stock. A promotional markdown is temporary (a weekend sale) and the price goes back up. Track them in a Type column, because they mean different things: a high permanent share says the buy was too deep or the product missed, a high promo share says you are buying sales.
Result: 1987
$1,987 of permanent markdowns, which is 52.8% of the $3,765 total. Promo markdowns are the other $1,778.
Gross margin after the markdown
This is where a markdown actually bites. The denim jacket costs $48. At $110 it earned a 56.4% margin; at $66 the margin is 27.3%. A 40% price cut more than halved the margin, because cost doesn't move.
For the whole list, weight by units: gross profit at the prices paid divided by sales at the prices paid. The blended margin at original prices, =SUMPRODUCT((D2:D9-G2:G9)*F2:F9)/SUMPRODUCT(D2:D9*F2:F9), was 59.6%.
Result: 0.44348263714 shows as 44.3% with Percent format
44.3%: the markdowns took the season's margin down by about 15 points. Compare this with your plan before approving the next round.
Why stacked discounts don't add up
"An extra 20% off already-reduced items" on a 30% markdown is not 50% off. The 20% comes off the $28 price, so the scarf sells for $22.40. Multiply the remaining shares and subtract from 1 to get the true total.
This is also why the markdown % column should always be calculated from the original price and the final selling price, not by adding the percentages from each round.
Result: 0.44 shows as 44% with Percent format
44% off in total, not 50%.
Common mistakes
Dividing by the markdown price for the ticket percentage
Percent off is always (original - new) / original. Dividing by the new price gives 42.9% instead of 30% for the scarf.
Averaging the percentage column
A 40% cut on 14 jackets and a 20% cut on 60 tees don't average to 30%. Use markdown dollars with SUMPRODUCT, then divide.
Mixing the two definitions
Markdown % of sales and percent off original are both valid. Write which one in the header and stick to it across months.
Counting units still in stock
Markdown dollars are earned only when the unit sells. Multiply by units sold at the reduced price, not units marked down.
FAQ
What is the formula for markdown percentage?
=(original price - markdown price) / original price, formatted as a percentage. A $110 jacket reduced to $66 is (110-66)/110 = 40% off.
How is markdown % of sales calculated?
Total markdown dollars (price cut times units sold) divided by net sales at the price customers paid. It's higher than the ticket percentage because the base is the reduced price.
What's the difference between markdown and markup?
Markup is how much you add on top of cost to set the price: (price - cost) / cost. Markdown is how much you take off the price later: (original - new) / original. See the markup guide for the first one.
How do I calculate the original price from a markdown?
Divide the reduced price by 1 minus the markdown: =28/(1-30%) gives $40. Don't add 30% back to $28; that gives $36.40.
Functions used
Related guides
- How to calculate open-to-buy in Excel
- How to calculate a discount price in Excel
- How to calculate markup in Excel
- How to calculate sell-through rate in Excel
- How to calculate the stock-to-sales ratio in Excel
- Budget formulas in Excel: the 10 every budget needs
- How to calculate labor cost percentage in Excel
Updated