Christmas Budget Tracker in Excel or Google Sheets: Formulas to Stay on Budget

Updated 2026-11-03

The average holiday overspend does not come from one big present. It comes from twenty small ones nobody added up: an extra stocking filler here, a second Black Friday "deal" there, a forgotten teacher's gift on 18 December. A simple spreadsheet stops that, and you can build one in about ten minutes. Below is a layout and the exact formulas, all working in both Excel and Google Sheets.

Step 1: Set up the gift list

Create one row per gift (not per person), so a person with three gifts gets three rows. Use these columns:

• Budget (C) is what you plan to spend on that gift.
• Spent (D) stays at 0 until you actually buy it.
• Regular price (E) is the full price before any sale, so you can see what deals really saved.
• Status (F) is a drop-down: Idea, Ordered, Bought, Wrapped.
• Arrives (G) is the expected delivery date for online orders, entered as a real date.

To add the status drop-down: select F2:F100, then in Excel go to Data > Data Validation > List and type Idea,Ordered,Bought,Wrapped. In Google Sheets use Insert > Drop-down (or Data > Data validation) and add the four options. Our drop-down list guide covers colours, growing lists and fixes. Running an office or family gift swap as well? Draw the names with our Secret Santa generator.

Step 2: The summary formulas

Put a small summary to the right of the list, for example in column I (labels) and column J (formulas):

Format J5 as a percentage and J2:J4 and J6 as currency. Using ranges down to row 100 means new gifts are counted automatically as you add rows. If you prefer, turn the list into an Excel table (Ctrl+T) and use structured references instead.

The "saved on deals" formula adds up the regular price of every gift you have bought and subtracts what you actually paid. It is a nice reality check after Black Friday (Friday 27 November 2026) and Cyber Monday (30 November): a "saving" only counts if you were going to buy the item anyway.

Step 3: Spending per person

To see how much each person's gifts cost, list the names in column L (L2, L3 and so on) and use SUMIFS next to them:

=SUMIFS($D$2:$D$100, $A$2:$A$100, L2)

And the budget for the same person:

=SUMIFS($C$2:$C$100, $A$2:$A$100, L2)

In Excel 365 and Google Sheets you can build the name list automatically with:

=SORT(UNIQUE(FILTER(A2:A100, A2:A100<>"")))

The list updates as soon as you add a new person. For more examples of conditional sums, see SUMIFS and COUNTIFS: 10 practical examples.

Step 4: Flag gifts that went over budget

Add a helper column H with:

=IF(D2>C2, "Over by "&TEXT(D2-C2, "0.00"), "")

In the example, Dad's coffee grinder shows "Over by 5.00". To colour the whole row red instead, select A2:H100 and add a conditional formatting rule with the custom formula =$D2>$C2. Our guide to highlighting a whole row based on a cell value explains why the dollar sign goes before the D but not before the 2.

Step 5: Check delivery dates

Online orders that arrive after Christmas Eve are a classic December problem. In column K:

=IF(G2="", "", IF(G2>DATE(2026,12,23), "Late!", "OK"))

Ana's headphones, due on 27 December, show "Late!", so you know to cancel or buy locally. Using 23 December rather than 24 leaves a day of slack for delays. To estimate when an order placed today will arrive if the shop quotes "5 working days", use WORKDAY, which skips weekends:

=WORKDAY(TODAY(), 5)

Add a list of public holidays as a third argument to skip those as well. Our guide to working days left in 2026 with NETWORKDAYS and WORKDAY shows how.

Step 6: A daily spending allowance

If you want to spread the remaining money over the days left, divide what is left by the days until Christmas Eve:

=MAX(0, J4) / MAX(1, DATE(2026,12,24)-TODAY())

The MAX parts stop the formula showing a negative allowance once you are over budget, and avoid dividing by zero on Christmas Eve itself.

Step 7: Add the other holiday costs

Gifts are only part of the bill. Add a second small table with a Category column (Food, Decorations, Travel, Cards and postage, Going out) and amounts, then total each category with SUMIF:

=SUMIF(Costs!A:A, "Food", Costs!C:C)

Add those totals to your gift spending for the full picture: =J3+SUM(Costs!C:C).

Useful extras

• Progress check: =COUNTIF(F2:F100,"Wrapped")&" of "&COUNTA(B2:B100)&" wrapped" shows something like "1 of 5 wrapped".
• Biggest single gift: =XLOOKUP(MAX(D2:D100), D2:D100, B2:B100) returns the name of the most expensive gift. See XLOOKUP vs VLOOKUP if your Excel version lacks XLOOKUP.
• Discount on a deal: =1-D2/E2 formatted as a percentage shows the real percent off. More in our discount formulas guide.
• Share it: a Google Sheet shared with a partner means you both see the same totals and nobody buys the same gift twice.

Common mistakes

• Typing prices with currency symbols as text. Let the cell format add the symbol; type numbers only.
• Entering delivery dates as text such as "3rd Dec". Use a real date (3/12/2026 or 12/3/2026 depending on your region), or comparisons will not work.
• Forgetting to lock ranges with $ before copying SUMIFS down the person list.
• Counting a gift as "spent" when it is only ordered and might be cancelled. Decide on one rule and stick to it.

Start the sheet now, before the Black Friday emails arrive, and it will tell you exactly how much room is left in the budget when you see a deal.

Sending cards too? Print address labels from your card list, and summarise spending per person with a pivot table.

← More guides