A bill tracker spreadsheet is a list of every recurring bill, written once, that feeds a monthly checklist: each bill gets a real due date, a paid checkbox and a status that turns red when it is overdue. The version on this page uses three tabs (Bills, Month, Year), works in Google Sheets and Excel, and handles the bills that break most budgets: quarterly and annual ones. In the worked example, $1,813 of monthly bills plus one annual and one quarterly bill need $1,909 set aside each month.
The layout: three tabs
| Tab | What it holds | When you touch it |
|---|
| Bills | One row per bill: name, category, amount, due day, frequency, autopay | When a bill starts, ends or changes price |
| Month | This month's checklist: due date, paid, paid date, status | Each time you pay something |
| Year | Twelve columns of totals per category | Once a month, to review |
Keeping the bill list separate from the checklist is the whole trick. You type a bill's details once, and every new month is a copy of the Month tab that reads from the same list.
Bills tab columns
| Column | Example | Notes |
|---|
| A: Bill | Electricity | The name as it appears on your bank statement helps later |
| B: Category | Utility | Keep categories short and consistent |
| C: Amount | 120 | Typical amount if it varies |
| D: Due day | 18 | Day of the month, 1 to 31 |
| E: Frequency | Monthly | Monthly, Quarterly or Annual |
| F: Autopay | Yes | Autopay bills still need checking, not paying |
| G: Monthly set-aside | =C2/IF(E2="Annual",12,IF(E2="Quarterly",3,1)) | What to put aside each month |
Build the monthly checklist
On the Month tab, put the first day of the month in B1 (for example 1 December 2026). Pull each bill across with =Bills!A2, then build its due date:
=DATE(YEAR($B$1), MONTH($B$1), MIN(Bills!D2, DAY(EOMONTH($B$1,0))))
The MIN(..., DAY(EOMONTH(...))) part matters: a bill due on the 31st lands on the 30th in November and on the 28th in February instead of spilling into next month.
Then add three more columns: a Paid checkbox (Insert, Checkbox in Google Sheets; a TRUE/FALSE column in Excel), the Paid date, and a Status:
=IF(E2, "Paid", IF(D2<TODAY(), "Overdue", IF(D2-TODAY()<=7, "Due soon", "Upcoming")))
Add conditional formatting on the Status column: red fill when the text is Overdue, amber for Due soon, grey for Paid. Sort the tab by due date and the next bill is always at the top.
For quarterly and annual bills, show them only in the months they are due. Add a Due month column to the Bills tab (for example 3 for March) and wrap the row in a check: =IF(OR(Bills!E2="Monthly", MONTH($B$1)=Bills!H2), Bills!A2, ""). Quarterly bills need the due month plus 3, 6 and 9, which MOD(MONTH($B$1)-Bills!H2, 3)=0 handles.
Worked example: one month of bills
The figures below are an illustration, not anyone's real budget.
| Bill | Category | Amount | Due day | Frequency | Autopay | Monthly set-aside |
|---|
| Rent | Living | $1,500 | 1st | Monthly | No | $1,500 |
| Internet | Utility | $80 | 12th | Monthly | Yes | $80 |
| Electricity | Utility | $120 | 18th | Monthly | Yes | $120 |
| Car insurance | Living | $45 | 19th | Monthly | Yes | $45 |
| Phone | Utility | $50 | 20th | Monthly | No | $50 |
| Streaming | Living | $18 | 25th | Monthly | Yes | $18 |
| Water | Utility | $210 | 5th | Quarterly | No | $70 |
| Car registration | Transport | $312 | 14th | Annual | No | $26 |
| Total | | | | | | $1,909 |
The six monthly bills add up to $1,813. Water ($210 every three months) and car registration ($312 once a year) only show up in the months they are due, but setting aside $70 and $26 each month means they never arrive as a surprise. The tracker's real number is therefore $1,909 a month, not $1,813.
On 22 December the Month tab would show rent, internet, electricity, car insurance and phone as Paid, and streaming as Due soon. If the phone bill had not been ticked by the 21st, it would show Overdue in red.
Find your bills in your bank CSV
Most people forget at least one bill when they write the list from memory. Your bank export already knows them. In an ANZ (New Zealand) CSV export, the Type column labels recurring payments, as in these sample rows:
| Type | Details | Particulars | Amount | Date |
|---|
| Payment | Test Person A | Dinner | 68.50 | 15/09/2026 |
| Direct Credit | Example Employer Ltd | Salary | 2000.00 | 16/09/2026 |
| Visa Purchase | COUNTDOWN ALBANY | | -35.20 | 17/09/2026 |
| Automatic Payment | Example Power Co | Power | -120.00 | 18/09/2026 |
| Direct Debit | Example Insurance | Premium | -45.00 | 19/09/2026 |
The Automatic Payment and Direct Debit rows are bills: power on the 18th for $120 and insurance on the 19th for $45. A card purchase or a salary credit is not. Import three months of CSV into a spare tab and filter for those two types:
=FILTER(A2:G, (A2:A="Automatic Payment") + (A2:A="Direct Debit"))
Any payee that appears every month goes on the Bills tab, with the day it left your account as the due day. ANZ writes dates as dd/mm/yyyy; if your Sheets locale is set to the US, convert them with =DATE(RIGHT(G2,4), MID(G2,4,2), LEFT(G2,2)). Other banks name these types differently, and card-billed subscriptions show up as ordinary purchases, so scan the merchant names too.
Once your transactions carry categories, the Year tab can compare what you planned with what actually left your account. Auto-categorizing bank transactions in Excel covers formula and rule approaches, and the bank transaction categorization guide compares them. If you want a full spending tracker next to the bills list, the expense tracking spreadsheet template uses the same Date, Description, Amount and Category columns.
The Year tab
Put the months across row 1 and your categories down column A, then total each cell from the monthly tabs or from your imported transactions:
=SUMIFS(Actuals!C:C, Actuals!D:D, $A2, Actuals!A:A, ">="&B$1, Actuals!A:A, "<="&EOMONTH(B$1,0))
A year view is where creeping bills show: a streaming price rise or an insurance renewal that went up is easy to miss in one month and obvious across twelve.
Print it as a monthly bill tracker PDF
If you prefer ticking bills off on paper, the Month tab prints well. In Google Sheets, select the checklist columns, then File, Download, PDF, choose "Selected cells" and "Fit to width". In Excel, set the print area and use File, Export, PDF. Print a fresh copy at the start of each month, and copy what you paid back into the sheet when you review.
When a spreadsheet is not enough
A bill tracker only knows what you tell it. It will not notice a new subscription unless you add it, and it cannot see that a bill was paid unless you tick the box or import your transactions. If you share bills with flatmates, the roommate expense-splitting template tracks who owes what. And if you would rather keep your bank data off cloud spreadsheets entirely, a private expense tracker that runs on your own machine does the import and categorizing locally.
FAQ
What should a bill tracker spreadsheet include?
One row per bill with its name, category, amount, due day, how often it repeats and whether it is on autopay. A monthly tab then turns each bill into a real due date with a paid checkbox, the date you paid and a status column that flags overdue and due-soon bills.
How do I make a bill tracker flag overdue bills?
Add a status column: =IF(E2,"Paid",IF(D2<TODAY(),"Overdue",IF(D2-TODAY()<=7,"Due soon","Upcoming"))), where E2 is the paid checkbox and D2 the due date. Then add conditional formatting that colours Overdue red and Due soon amber.
How do I track bills that are not monthly?
Give each bill a frequency (monthly, quarterly, annual) and work out a monthly set-aside: the amount divided by 3 for quarterly bills and by 12 for annual ones. A $312 annual car registration becomes $26 a month, so the month it is due does not break your budget.
Can I print the bill tracker as a PDF?
Yes. In Google Sheets, open the month tab and use File, Download, PDF; in Excel use File, Export, PDF. Set the print range to the checklist columns so it fits on one page.
How do I find all my bills from a bank statement?
Export a few months of transactions as CSV and filter for the transaction types banks use for recurring payments, such as Automatic Payment and Direct Debit in an ANZ export. Each payee that repeats every month is a bill to add to the list.