Christmas Gift Budget Spreadsheet: Per-Person Budgets, Gift Status and Black Friday Dates (Excel & Google Sheets)
Short on time? The Christmas & Holiday Budget Planner ($9) is the finished, tested version of what this tutorial builds.
Most holiday overspending isn't one big purchase. It's a dozen "small" gifts nobody added up until January. The fix is a sheet that knows the difference between a gift you're thinking about and a gift you've paid for, and that totals both per person.
This tutorial builds that sheet in three tabs: Settings, People and Gifts. It works the same in Excel, Google Sheets and LibreOffice, using only SUMIFS, IF, AND/OR, N, DATE, WEEKDAY and MOD. 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. We also checked the holiday dates for every year from 2024 to 2040.
1. The Gifts tab
Row 1 holds the headers: Person, Gift, Status, Estimate, Actual, Cost, Spent. You type columns A–E. Status is one of Idea, Bought, Wrapped, Given (add a dropdown with Data → Data validation). Columns F and G are formulas. Fill them down as far as you need.
F2 (Cost): =IF(AND(D2="",E2=""),"",IF(E2<>"",E2,D2))
G2 (Spent): =IF(OR(C2="Bought",C2="Wrapped",C2="Given"),N(F2),0)
Cost uses the actual price once you've typed one and the estimate until then, so a gift you've bought but whose receipt you haven't logged still counts. Spent counts the cost only once the gift is past the Idea stage. N() turns a blank cost into 0, so an empty row adds nothing.
2. The People tab
Row 1 holds the headers: Person, Budget, Planned, Spent, Left, Status. Type names and budgets in A and B, then fill C–F down:
C2 (Planned): =SUMIFS(Gifts!F:F,Gifts!A:A,A2)
D2 (Spent): =SUMIFS(Gifts!G:G,Gifts!A:A,A2)
E2 (Left): =B2-D2
F2 (Status): =IF(B2="","",IF(D2>B2,"Over budget",IF(C2>B2,"Plan is over budget","OK")))
Planned includes ideas, so it warns you before you buy. "Plan is over budget" means your list for that person already costs more than their budget, even though you haven't spent it yet. Person names must match exactly on both tabs (a dropdown on Gifts!A pointing at People!A avoids typos).
3. The Settings tab
B1: 2026 (year)
B2: 600 (total gift budget)
B4 (Thanksgiving, US): =DATE(B1,11,1)+MOD(5-WEEKDAY(DATE(B1,11,1)),7)+21
B5 (Black Friday): =B4+1
B6 (Cyber Monday): =B4+4
B7 (Days to Christmas): =MAX(0,DATE(B1,12,25)-TODAY())
B9 (Total spent): =SUM(Gifts!G:G)
B10 (Remaining): =B2-B9
B11 (Given to people): =SUM(People!B:B)
B12 (Not yet assigned): =B2-B11
Format B4:B6 as dates. The Thanksgiving formula finds the first Thursday of November. WEEKDAY returns 1 for Sunday through 7 for Saturday, and Thursday is 5, so MOD(5-WEEKDAY(...),7) is the number of days from November 1 to that Thursday. Adding 21 jumps three weeks to the fourth Thursday. Change B1 and every date moves.
Worked example
Settings: year 2026, budget $600. People: Mom $120, Dad $80, Emma $150, Liam $140.
| Person | Gift | Status | Estimate | Actual | Cost | Spent |
|---|---|---|---|---|---|---|
| Mom | Scarf | Wrapped | 60.00 | 54.00 | 54.00 | 54.00 |
| Mom | Novel | Idea | 25.00 | 25.00 | 0.00 | |
| Dad | Cordless drill | Bought | 139.00 | 89.00 | 89.00 | 89.00 |
| Emma | Ice skates | Bought | 80.00 | 74.50 | 74.50 | 74.50 |
| Emma | Book set | Idea | 30.00 | 30.00 | 0.00 | |
| Liam | LEGO set | Given | 79.99 | 59.99 | 59.99 | 59.99 |
| Liam | Video game | Bought | 70.00 | 70.00 | 70.00 | |
| Liam | Fun socks | Idea | 15.00 | 15.00 | 0.00 |
| Person | Budget | Planned | Spent | Left | Status |
|---|---|---|---|---|---|
| Mom | 120.00 | 79.00 | 54.00 | 66.00 | OK |
| Dad | 80.00 | 89.00 | 89.00 | -9.00 | Over budget |
| Emma | 150.00 | 104.50 | 74.50 | 75.50 | OK |
| Liam | 140.00 | 144.99 | 129.99 | 10.01 | Plan is over budget |
Settings results: Thanksgiving Thu Nov 26, 2026, Black Friday Nov 27, Cyber Monday Nov 30. Total spent $347.49, remaining $252.51, given to people $490.00, not yet assigned $110.00.
Dad's drill was a Black Friday deal, but $89 is still more than his $80 budget. Liam hasn't overspent yet, but his idea list has. Those are exactly the two things the sheet is meant to catch.
Holiday dates for the next few years
| Year | Thanksgiving | Black Friday | Cyber Monday |
|---|---|---|---|
| 2026 | Nov 26 | Nov 27 | Nov 30 |
| 2027 | Nov 25 | Nov 26 | Nov 29 |
| 2028 | Nov 23 | Nov 24 | Nov 27 |
| 2029 | Nov 22 | Nov 23 | Nov 26 |
| 2030 | Nov 28 | Nov 29 | Dec 2 |
Common mistakes
- Counting ideas as spent. If your total looks too high, check that Spent uses the status test and not just the cost.
- Name typos. "Mom" and "Mom " (with a trailing space) are different people to SUMIFS. Use a dropdown.
- Forgetting the estimate. Leave Actual blank until you know it; Cost falls back to the estimate, so bought gifts are never counted as $0.
- Hard-coding Black Friday. It isn't a fixed date. Use the formula so next year's copy just works.
Going further
This sheet covers gifts. The paid Christmas & Holiday Budget Planner adds stockings, cards, events, the holiday meal, a Black Friday savings tracker, a November–December calendar, a per-group fairness check and dark mode. But the three tabs above are a complete, working gift budget 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.