Black Friday Deal Tracker Spreadsheet: Check the Real Discount Against the Lowest Price You've Seen (Excel & Google Sheets)
Short on time? The Christmas & Holiday Budget Planner ($9) is the finished, tested version of what this tutorial builds.
A Black Friday tag that says "was $139, now $89" tells you how far the price fell from the number the store chose to print. It doesn't tell you whether $89 is a good price. If the same drill sold for $99 in September, the real saving is $10, not $50.
This tutorial builds a deal tracker that keeps both numbers side by side. You note the lowest price you've seen for each item in the weeks before (a price-history tool, a screenshot or your own notes), and the sheet measures every deal against the lower of that and the list price. It works the same in Excel, Google Sheets and LibreOffice, using only IF, OR, AND, MIN, N, ISNUMBER, SUM, SUMIFS and COUNTIF. To test it, we built a workbook with these exact formulas, recalculated it in LibreOffice, and compared every cell to the worked example below and to an independent Python model on 40 random deal lists.
Why bother? The US Federal Trade Commission's Guides Against Deceptive Pricing say a "was" price is only a legitimate comparison if it's the price the item "was offered to the public on a regular basis for a reasonably substantial period of time" (16 CFR 233.1). If it was inflated so the sale could look bigger, the guide says the "reduced" price "is, in reality, probably just the seller's regular price." You can't audit a store's pricing history, but you can write down what you saw.
1. The Deals tab
Row 1 holds the headers: Item, List price, Lowest seen, Deal price, Bought?, Claimed saving, Benchmark, Real saving, Real %, Verdict, Saved. You type columns A–E. Leave Lowest seen blank if you haven't tracked the item; the sheet then falls back to the list price. Bought? is Yes or No (a dropdown via Data → Data validation helps). Columns F–K are formulas. Fill them down as far as you need.
F2 (Claimed saving): =IF(OR(B2="",D2=""),"",B2-D2)
G2 (Benchmark): =IF(B2="","",IF(C2="",B2,MIN(B2,C2)))
H2 (Real saving): =IF(OR(G2="",D2=""),"",G2-D2)
I2 (Real %): =IF(OR(H2="",N(G2)=0),"",H2/G2)
J2 (Verdict): =IF(I2="","",IF(I2>=Settings!$B$1,"Good deal",IF(I2>0,"Small discount",IF(I2=0,"Same as usual","Worse than before"))))
K2 (Saved): =IF(AND(E2="Yes",ISNUMBER(H2)),H2,0)
Claimed saving is what the tag says. Benchmark is the price you'd have paid anyway: the list price, or the lowest price you've seen if that's lower. Real saving and Real % measure the deal against the benchmark, so a "50% off" tag on an item that was cheaper last month shows up as Worse than before. Saved counts only what you actually bought, and it can be negative: paying more than you could have paid earlier is money lost, not saved. Format I as a percentage.
2. The Settings tab
B1: 20% (a Good deal is at least this far below the benchmark)
B3 (Claimed savings, bought): =SUMIFS(Deals!F:F,Deals!E:E,"Yes")
B4 (Real savings, bought): =SUM(Deals!K:K)
B5 (Spent on deals): =SUMIFS(Deals!D:D,Deals!E:E,"Yes")
B6 (Good deals found): =COUNTIF(Deals!J:J,"Good deal")
Type B1 as 20% (or 0.2). The gap between B3 and B4 is how much of the "savings" came from the tags rather than from the prices.
Worked example
Settings: Good-deal threshold 20%. Seven deals, five bought:
| Item | List price | Lowest seen | Deal price | Bought? | Claimed saving | Benchmark | Real saving | Real % | Verdict | Saved |
|---|---|---|---|---|---|---|---|---|---|---|
| Cordless drill | 139.00 | 99.00 | 89.00 | Yes | 50.00 | 99.00 | 10.00 | 10.1% | Small discount | 10.00 |
| Cashmere scarf | 78.00 | 54.00 | Yes | 24.00 | 78.00 | 24.00 | 30.8% | Good deal | 24.00 | |
| LEGO set | 79.99 | 59.99 | 59.99 | Yes | 20.00 | 59.99 | 0.00 | 0.0% | Same as usual | 0.00 |
| Ice skates | 89.99 | 69.99 | 74.50 | Yes | 15.49 | 69.99 | -4.51 | -6.4% | Worse than before | -4.51 |
| Headphones | 99.99 | 89.99 | 79.99 | Yes | 20.00 | 89.99 | 10.00 | 11.1% | Small discount | 10.00 |
| Robot vacuum | 249.00 | 199.00 | 229.00 | No | 20.00 | 199.00 | -30.00 | -15.1% | Worse than before | 0.00 |
| Air fryer | 99.00 | 79.00 | 59.00 | No | 40.00 | 79.00 | 20.00 | 25.3% | Good deal | 0.00 |
Settings results: claimed savings on what was bought $129.49, real savings $39.49, spent on deals $357.48, good deals found 2.
The tags promised $129.49 off. Measured against prices that were already available, the five purchases saved $39.49, and the ice skates cost $4.51 more than they had a few weeks earlier. The two genuinely good deals were the scarf (never seen cheaper) and the air fryer, which wasn't bought. That's the decision the sheet is for: the air fryer at $59 beats its own history by 25%, the robot vacuum at $229 is $30 worse than its history even though the tag says $20 off.
Black Friday and Cyber Monday 2026
Black Friday 2026 is Friday, November 27 and Cyber Monday is November 30 (US Thanksgiving is the fourth Thursday of November). Start filling in Lowest seen now: the weeks before Black Friday are when you can still see what an item normally costs. The Christmas gift budget tutorial has a formula that works out these dates for any year.
Common mistakes
- Trusting the tag. Claimed saving uses the store's list price. If you only track that column, every deal looks good.
- Typing percentages as whole numbers. If B1 is 20 instead of 20%, nothing is ever a Good deal. Format B1 as a percentage.
- Counting what you didn't buy. Saved only counts rows marked Yes, so a deal you passed on doesn't inflate your total. Spelling matters: "yes " with a space doesn't count.
- Mixing pack sizes. Compare the same item and quantity. A "deal" on a smaller bundle at a lower price isn't a saving.
- Hiding losses. Leave negative Saved values in. If you overpaid, the total should say so.
Going further
This tracker covers deals. The paid Christmas & Holiday Budget Planner has its own Black Friday tab (it compares the deal price with the regular price against a threshold you set and totals what you saved), plus gifts per person, stockings, cards, events, the holiday meal, a November–December calendar with every key date, a fairness check and dark mode. Adding a Lowest seen column like the one above to its Black Friday tab works the same way. The two tabs above are a complete, working deal tracker on their own.
Written with AI assistance. Every formula, command and number on this page was tested before publishing (see each section). Product links go to our store.