Build a Bill Tracker in Excel

A bill tracker in Excel is a simple list of bills with their due dates, amounts and whether each one is paid, plus a few formulas that do the checking for you. In about fifteen minutes you can build one that counts the days left, totals what you still owe and turns overdue bills red. This guide builds it step by step, and every formula is one you will reuse at work.

Step 1: Set up the bill list#

Start with one row per bill and these columns:

A few habits keep the tracker reliable:

• Type real dates, such as 8/11/2026 or 2026-11-08, so Excel stores them as dates and can do maths with them. A date that sits on the left of the cell is probably text.
• Type amounts as plain numbers. Format them as currency afterwards, rather than typing a symbol.
• Turn the list into a table with Ctrl+T (Mac: Cmd+T). New bills typed in the row below join the table, and formulas fill down by themselves.

Step 2: Add a Yes/No drop-down for Paid#

A drop-down list stops typos like "yes " or "Y", which formulas would not match. Select D2:D7, go to Data > Data Validation, choose List and type Yes,No in the Source box. Each cell now has a small arrow with the two choices.

This matters because the totals in Step 4 look for the exact word "No". A stray space would quietly leave a bill out.

Step 3: Count the days left with TODAY#

TODAY returns the current date, and subtracting two dates gives the number of days between them. In E2:

=B2-TODAY()

Fill it down (Windows: Ctrl+D; Mac: Cmd+D). If today is 2 Nov 2026, Electricity shows 6 and Rent shows -1, because it was due yesterday. A negative number means the date has passed. The result recalculates every time you open the file, so you never need to update it by hand. The TODAY function guide explains how it updates.

If the result looks like a date, change the cell's number format to General. For other ways to count days, see the days between dates lesson.

A clearer label is often more useful than a number. This formula returns a status for each bill:

=IF(D2="Yes","Paid",IF(B2<TODAY(),"Overdue",IF(B2-TODAY()<=7,"Due soon","Upcoming")))

It checks the Paid column first, so a paid bill never shows as overdue. Then it checks whether the date has passed, then whether it is within a week.

Step 4: Total paid and unpaid bills with SUMIF#

SUMIF adds the numbers that match one condition. To see what you still owe:

=SUMIF(D2:D7,"No",C2:C7)

It checks D2:D7 for "No" and adds the matching amounts in C2:C7: 85 + 45 + 110 + 40 = 280. Change "No" to "Yes" for the amount already paid, 1,230. Together they make the total of 1,510. The SUMIF function guide lists the full syntax.

Two more summary formulas are worth adding at the top of the sheet:

Note how the date conditions join the operator in quotes to TODAY with &. Writing "<TODAY()" would not work, because inside quotes it is just text.

Step 5: Highlight overdue bills with conditional formatting#

Conditional formatting changes a cell's colour when a rule is true. To turn unpaid, overdue bills red:

1. Select A2:E7.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter =AND($D2="No",$B2<TODAY()).
5. Click Format, pick a light red fill and click OK twice.

The $ before D and B locks the columns, so every cell in a row checks that row's Paid and Due date. The row number has no $, so it moves down as the rule is applied to rows 3 to 7. Add a second rule with =AND($D2="No",$B2-TODAY()<=7,$B2>=TODAY()) and an amber fill for bills due within a week. The conditional formatting lesson practises formula rules like these.

Step 6: Roll monthly bills forward with EDATE#

Most bills repeat every month. When you pay one, you need next month's due date. EDATE adds whole months and keeps the same day:

=EDATE(B2,1)

For Electricity, this returns 8 Dec 2026. When a bill is paid, add a new row for next month with that date, and set Paid to No. Keeping the old rows gives you a payment history you can total by month later.

Ideas to extend your bill tracker#

Once the basics work, you can grow the tracker with the same skills:

• Add a Category column (housing, utilities, transport) and total it with SUMIF, as in the expense tracker guide.
• Add a Paid on date to check whether bills were paid on time.
• Use the same days-left and red-highlight pattern for renewals and warranties, as in the expiry date tracker.
• Track job applications or stock levels with the same layout: see the job application tracker and the inventory tracker.

Practise it#

Practise days between dates in a live spreadsheet: you type each date formula and it checks your answer instantly. Then try the unpaid total exercise on this page, and practise conditional formatting rules to make your tracker highlight what matters.