Why a bill tracker spreadsheet helps

Bills are predictable — you know what's coming and roughly when. Yet they cause stress because most people don't have a system for tracking them. A bill tracker spreadsheet solves this by putting every bill in one place, with due dates, amounts, and payment status visible at a glance.

This guide walks through building a bill tracker that works alongside your household budget and keeps you on top of every payment.

Step 1: List every bill

Start by listing every recurring bill. Go through your bank statements for the last 12 months and note every regular payment. See our guide to managing household bills for the full process.

For each bill, you need:

• Bill name (e.g., Rent, Electricity, Phone)
• Amount (the typical or exact amount)
• Frequency (weekly, fortnightly, monthly, quarterly, annually)
• Due date (the day of the month or the specific date for the next payment)
• Payment method (direct debit, BPAY, manual transfer)
• Account (which account it's paid from)

Step 2: Set up the spreadsheet

Create a new sheet with the following columns:

| Bill | Amount | Frequency | Next due | Days until due | Account | Status | |---|---|---|---|---|---|---| | Rent | $800 | Monthly | 2026-10-15 | 3 | Bills | Due | | Electricity | $180 | Quarterly | 2026-10-28 | 16 | Bills | Scheduled | | Phone | $60 | Monthly | 2026-10-20 | 8 | Bills | Direct debit | | Car insurance | $66 | Monthly | 2026-10-25 | 13 | Bills | Direct debit | | Internet | $80 | Monthly | 2026-10-22 | 10 | Bills | Direct debit |

Step 3: Add the key formulas

Days until due

This is the most useful column — it tells you which bills are urgent:

=DATEDIF(TODAY(), D2, "d")

Where D2 is the Next due date. This shows the number of days from today until the bill is due.

If the date has passed (the bill is overdue), DATEDIF returns an error. Handle this with IFERROR:

=IFERROR(DATEDIF(TODAY(), D2, "d"), "Overdue")

Fortnightly equivalent

To see how much your bills cost per pay, add a column that converts each bill to a fortnightly amount:

=B2 * (26 / VLOOKUP(C2, FrequencyTable, 2, FALSE))

Where FrequencyTable maps frequencies to annual counts:

| Frequency | Pays/year | |---|---| | Weekly | 52 | | Fortnightly | 26 | | Monthly | 12 | | Quarterly | 4 | | Annually | 1 |

The formula multiplies the bill amount by (26 / pays_per_year) to get the fortnightly equivalent. For a $180 quarterly bill: $180 × (26/4) = $1,170/year ÷ 26 = $45/fortnight.

Total fortnightly bill cost

At the bottom of the fortnightly column:

=SUM(F2:F50)

This is the amount you need to transfer to your bills account each payday. See our guide to the bills account system.

Step 4: Add conditional formatting

Conditional formatting makes the tracker visual and highlights what needs attention:

Highlight urgent bills

1. Select the Days until due column
2. Format → Conditional formatting → Custom formula
3. Add three rules:
   • Less than 0: red background (overdue)
   • 0 to 7: orange background (due this week)
   • 8 to 14: yellow background (due in the next two weeks)

Now you can see at a glance which bills need attention this week.

Highlight payment status

If you track payment status (Paid, Due, Scheduled):

1. Select the Status column
2. Conditional formatting → Text contains
3. "Paid" → green background
4. "Due" → orange background
5. "Overdue" → red background

Step 5: Add a bill calendar view

A list is useful, but a calendar view shows when bills cluster — the danger periods when several are due at once.

Simple calendar with conditional formatting

1. Create a 12-column layout (one per month)
2. In each cell, list the bills due that month (use TEXTJOIN or just enter them manually)
3. Use conditional formatting to highlight months with high total bill costs

Or: sort by due date

Sort the main sheet by Next due date. The bills appear in order of when they're due, so the most urgent are always at the top.

Step 6: Track variable bills

Some bills (electricity, gas, water) vary each cycle. For these:

• Use the average amount in the Amount column, and adjust when the actual bill arrives
• Add a column for the actual amount so you can compare to the average
• Review quarterly to update the average if usage has changed

Example with variable bills

| Bill | Budgeted | Actual last bill | Difference | |---|---|---|---| | Electricity | $180 | $215 | -$35 | | Gas | $90 | $75 | +$15 | | Water | $70 | $70 | $0 |

If the actual is consistently higher than budgeted, increase the budgeted amount (and the fortnightly transfer).

Step 7: Handle annual and irregular bills

Annual and quarterly bills are easy to forget because they don't appear every month. Include them in the tracker with their actual due date, and use the sinking fund approach to save for them gradually.

Example

| Bill | Amount | Frequency | Next due | Fortnightly equivalent | |---|---|---|---|---| | Car rego | $600 | Annually | 2027-03-15 | $23 | | Home insurance | $1,200 | Annually | 2027-04-01 | $46 | | Council rates | $900 | Quarterly | 2026-12-01 | $35 |

The fortnightly equivalent tells you how much to set aside each pay so the money is there when the bill arrives.

Step 8: Review and update

A bill tracker needs regular maintenance:

• Monthly: update the Next due dates for bills that have been paid, and mark them as Paid
• Quarterly: review variable bills and adjust the budgeted amounts if usage has changed
• Annually: review all bills for increases (insurance, rego, subscriptions) and check whether you're on the best plan. See our guide to auditing subscriptions

Common bill tracker mistakes

• Forgetting annual bills. Car rego, insurance, and memberships come once a year and are easy to miss. Include them with their actual due date.
• Not converting to one frequency. If you don't calculate the fortnightly equivalent, you can't see the total bill cost per pay.
• No urgency highlighting. Without conditional formatting, you have to scan every row to find what's due. Colour coding makes it instant.
• Not updating after payment. If you don't mark bills as paid and update the next due date, the tracker becomes stale and unreliable.
• Ignoring variable bills. If your electricity estimate is $180 and the actual is $250, you'll be short. Review and adjust regularly.

When a spreadsheet isn't enough

A bill tracker spreadsheet is excellent for the detail, but it doesn't:

• Project your balance forward — to see whether your balance will cover upcoming bills, use the free payday calculator.
• Alert you when a bill will breach your buffer — a spreadsheet shows the data but doesn't warn you.
• Automatically update due dates — you have to manually update the Next due date after each payment.

The Payday Compass app handles all of this automatically — enter each bill with its amount, frequency, and due date, and the app projects when it will hit, whether your balance will cover it, and alerts you before a breach.

Summary

A bill tracker spreadsheet lists every bill with its amount, frequency, due date, and payment status. Use DATEDIF to calculate days until due, convert all bills to a fortnightly equivalent to see the total per-pay cost, and use conditional formatting to highlight urgent and overdue bills. Include annual and irregular bills, track variable bills against a budgeted average, and review monthly. The key is having every bill in one place with the due dates visible — so nothing surprises you.

For automatic bill projection and buffer alerts, try the Payday Compass Money Runway.