Monthly Bills Spreadsheet Built From Your Bank Statements

Quick Answer: The quickest accurate monthly bills spreadsheet starts from your real transactions, not a blank template. Put two or three months of bank and card statement rows in one sheet, count how often each payee appears with COUNTIF, and copy the ones that repeat into a bills tab with columns for amount, due day, autopay and a paid check for each month.

A monthly bills spreadsheet is a list of every payment that comes round on a schedule (rent or mortgage, utilities, phone, insurance, loan payments, subscriptions) with its usual amount, due date and how it gets paid. Building it from your bank statements rather than from memory means nothing gets left out, and the amounts are the ones you actually pay.

The method below works in Excel or Google Sheets. It takes about an hour the first time and a few minutes each month after that.

Step 1: Get three months of transactions into one sheet

Three months is the sweet spot. One month misses quarterly bills and anything that skipped a cycle; a year is more rows than you need to start.

Gather the transactions from every account bills come out of: the main checking account, any credit card that carries subscriptions, and a second account if a partner pays some bills. You have three ways to get them:

• The bank's own CSV or Excel download. Most online banking sites offer a transaction export. If yours does, use it.
• Copying from the PDF. Fine for a dozen lines, painful for three months.
• Converting the statement PDFs. When the bank only gives you PDFs, QuickBankConvert turns each one into rows of date, description, category and amount. A free account downloads CSV or Excel for a few statements a month, which covers a household's three-month pull; the limits are on the pricing page.

Paste everything into one tab called Transactions with the same four columns: Date, Description, Amount, Account. Keep money out as negative numbers so bills are easy to filter.

Step 2: Which payments are bills? Find them with COUNTIF

Bank descriptions are messy ("ACH DEBIT ELECTRIC CO 0423 WEB PMT"), so first add a Payee column with a short clean name you type yourself: "Electric", "Phone", "Car insurance". Sorting the sheet by Description puts the variations of the same payee next to each other and makes this quick.

Then add a column that counts how many times each payee appears. Microsoft's COUNTIF documentation gives the syntax as COUNTIF(range, criteria). If Payee is column E and your data runs from row 2 to row 400:

=COUNTIF($E$2:$E$400, E2)

Fill it down. Over a three-month window:

Groceries and fuel will also count high. They're spending, not bills, so leave them off unless you want a budget, which is a different sheet. Our monthly expenses spreadsheet guide covers that side.

Step 3: Build the bills tab

Create a second tab called Bills with one row per bill. These columns cover what you need:

1. Bill: your clean name.
2. Statement text: a few characters of how it appears on the statement, so you can spot it next month.
3. Usual amount: the figure from the last statement.
4. Due day: the day of the month it's taken or due. Use the transaction dates from Step 1.
5. Paid from: which account or card.
6. Autopay: Yes or No.
7. Last month and Change: filled in during Step 4.
8. Jan, Feb, Mar…: one column per month, starting in column I, for a checkmark or the amount actually paid.

Sort the tab by Due day. A list ordered by date reads like a calendar of the month, which is what makes it useful the week before payday.

How do you track autopay and due dates?

Autopay bills need checking, not chasing. Manual bills need a reminder. Split them visually: in Google Sheets, conditional formatting can shade rows by a rule; Google's help page on conditional formatting gives the route: select the cells, click Format, then Conditional formatting, and create a rule. Rules based on your own formula are covered in that page's advanced section. Two rules worth setting:

• Shade rows where Autopay is "No", so the bills you must pay yourself stand out.
• Highlight the current month's column cell when it's still empty after the due day.

For the due-day check, a helper column with =IF(AND(DAY(TODAY())>D2, M2=""), "Overdue?", "") (Due day in column D, this month's paid column in M, which is May if January starts in I) flags anything that should have gone out by now. Adjust the letters to your layout.

Step 4: Spot amounts that change

The real value of building from statements is catching drift: the insurance renewal that went up, the streaming service that raised its price, the utility bill that doubled in winter. Fill in the two columns you left for this:

• Last month (column G): the amount from the most recent statement.
• Change (column H): =G2-C2, last month's amount minus the usual amount, so a price rise shows as a positive number.

Then set a conditional format to colour any change bigger than a few percent. Utilities will move with the seasons, and that's expected; a phone or subscription bill that moves is worth a call.

If you find a payment you didn't expect or no longer want, the CFPB's guidance on stopping automatic payments explains the steps and your rights with the bank. For a deeper hunt through subscriptions specifically, see our subscription audit guide.

Keeping it current each month

Once the tabs exist, the monthly update is short:

1. Download or convert the new month's statements and paste the rows at the bottom of Transactions.
2. Fill in the Payee column for new lines (copy down from the last occurrence for repeats).
3. Tick each bill's month column, entering the amount if it differs from usual.
4. Look for anything new with a count of 1 that looks like it could become a bill, such as a new subscription or a first loan payment, and add it.
5. Glance at the Change column.

Excel or Google Sheets for a household bill tracker?

Both handle this well. Pick by how you'll use it:

If you later want the tracker to feed a full budget, our guide to creating a budget spreadsheet from bank statements builds on the same Transactions tab.

Related guides

• Monthly expenses spreadsheet
• Subscription audit from bank statements
• Create a budget spreadsheet from bank statements
• Bank statement PDF to Google Sheets