Expense Sorted

Bill Tracker Spreadsheet: Free Google Sheets and Excel Layout With Due-Date Alerts

By Anonymous|Guides|bill tracker spreadsheet, monthly bill tracker, bill tracker template, Google Sheets, Excel

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

TabWhat it holdsWhen you touch it
BillsOne row per bill: name, category, amount, due day, frequency, autopayWhen a bill starts, ends or changes price
MonthThis month's checklist: due date, paid, paid date, statusEach time you pay something
YearTwelve columns of totals per categoryOnce 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

ColumnExampleNotes
A: BillElectricityThe name as it appears on your bank statement helps later
B: CategoryUtilityKeep categories short and consistent
C: Amount120Typical amount if it varies
D: Due day18Day of the month, 1 to 31
E: FrequencyMonthlyMonthly, Quarterly or Annual
F: AutopayYesAutopay 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.

BillCategoryAmountDue dayFrequencyAutopayMonthly set-aside
RentLiving$1,5001stMonthlyNo$1,500
InternetUtility$8012thMonthlyYes$80
ElectricityUtility$12018thMonthlyYes$120
Car insuranceLiving$4519thMonthlyYes$45
PhoneUtility$5020thMonthlyNo$50
StreamingLiving$1825thMonthlyYes$18
WaterUtility$2105thQuarterlyNo$70
Car registrationTransport$31214thAnnualNo$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:

TypeDetailsParticularsAmountDate
PaymentTest Person ADinner68.5015/09/2026
Direct CreditExample Employer LtdSalary2000.0016/09/2026
Visa PurchaseCOUNTDOWN ALBANY-35.2017/09/2026
Automatic PaymentExample Power CoPower-120.0018/09/2026
Direct DebitExample InsurancePremium-45.0019/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.

Frequently Asked Questions

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.

Local product

Go Local — data stays on your computer

Download free. Unlock smarter local AI categorization with Local Pro ($49 one-time) or start a 14-day trial.