How to Create a Bill Tracker in Excel

Tired of trying to remember which bills are due when and whether you've actually paid them? A simple bill tracker can save you from late fees and financial stress by putting all your upcoming expenses in one organized place. This guide will walk you through creating a powerful and personalized bill tracker from scratch using Microsoft Excel, complete with automated status updates, visual cues, and a summary dashboard.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Use Excel for Tracking Bills?

While there are plenty of budgeting apps out there, building your own bill tracker in Excel offers some unique advantages. You have complete control over the layout and features, you don't have to pay a subscription fee, and you get to practice some valuable spreadsheet skills along the way. It’s a simple, effective, and free way to take command of your finances.

Step 1: Setting Up Your Spreadsheet Columns

First things first, let's build the foundation of your tracker. Open a new Excel workbook and create a new sheet. You can rename it by double-clicking the "Sheet1" tab at the bottom and typing something like "Bill Tracker".

Now, create the headers for your columns in the first row. Here are the essential ones to start with:

• Bill: The name of the expense (e.g., "Rent," "Netflix," "Car Insurance").
• Category: The type of expense (e.g., "Housing," "Subscriptions," "Utilities," "Auto"). This will be useful for analysis later.
• Amount Due: The dollar amount you owe.
• Due Date: The date the payment is due.
• Date Paid: The date you actually made the payment. Leave this blank until you pay the bill.
• Status: An automatically updated field that will tell you if a bill is "Paid," "Upcoming," or "Overdue." We'll set this up in the next step.
• Payment Method: How you paid (e.g., "Chase Credit Card," "Bank Transfer," "PayPal").
• Notes: An optional column for any extra details, like a confirmation number.

Once you've added your headers, format them to be bold so they stand out. Now you can start populating your tracker with your recurring bills. Don't worry about filling in the "Status" column yet, we'll automate that next.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 2: Automating the 'Status' with Formulas

Manually updating the status of every bill is tedious. Let's use an Excel formula to do the heavy lifting. We'll write a rule that checks the "Due Date" and "Date Paid" columns to determine the bill's current status automatically.

We want the "Status" column (Column F in our example) to follow this logic:

• If there's a date in the "Date Paid" column, the status should be "Paid."
• If "Date Paid" is empty and the "Due Date" is in the past, the status should be "Overdue."
• If "Date Paid" is empty and the "Due Date" is today or in the future, the status should be "Upcoming."

To do this, we'll use a nested IF formula. Click on cell F2 (the first cell in your Status column) and enter the following formula:

=IF(E2<>"", "Paid", IF(TODAY()>D2, "Overdue", "Upcoming"))

Let's break that down:

• IF(E2<>"", "Paid", ...): Checks if cell E2 (Date Paid) is not blank. If it contains a date, displays "Paid."
• IF(TODAY()>D2, "Overdue", "Upcoming"): If E2 is blank, this part checks if today's date is greater than the Due Date (cell D2). If yes, "Overdue", if no, "Upcoming".

After entering the formula in F2, click the small green square at the bottom-right corner of the cell and drag it down to apply the formula to other rows. Now, the status will update automatically based on your due dates and payment status!

Step 3: Using Conditional Formatting for Visual Cues

To make your tracker even easier to read at a glance, we can use Conditional Formatting to color-code the "Status" column. This will make overdue bills jump off the page.

Color-Coding the Status Column

First, highlight all the cells in your "Status" column (excluding the header). Then, follow these steps:

1. Go to the Home tab on the Excel ribbon.
2. Click on Conditional Formatting > Highlight Cells Rules > Text that Contains...
3. In the dialog box, type "Overdue" and select a format, like "Light Red Fill with Dark Red Text." Click OK.
4. Repeat the process for "Paid." Type "Paid" and select a green format.
5. Repeat once more for "Upcoming," choosing a yellow or orange format.

Now, your status column will automatically change color based on its value, providing an instant visual summary.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Color-Coding the Entire Row

To make things even clearer, you can format the entire row to change color based on the status. This is slightly more advanced but highly effective.

1. Select all the data in your tracker, from the first bill in A2 to the last note in H[last row].
2. Go to Home > Conditional Formatting > New Rule...
3. In the "New Formatting Rule" window, select Use a formula to determine which cells to format.
4. For the "Overdue" rule, enter this formula:

=$F2="Overdue"

The dollar sign locks the column F, so Excel checks the status in column F for each row.

1. Click the Format... button, choose a red fill color, and click OK.
2. Repeat these steps to create new rules for "Paid" (using $F2="Paid" with a green fill) and "Upcoming" (using $F2="Upcoming" with a yellow fill).

Your tracker is now visually optimized, making it impossible to miss an overdue bill.

Step 4: Building a Summary Dashboard

To get a high-level overview of your finances, create a simple dashboard on a separate sheet. Create a new sheet named "Dashboard." Here, you can calculate some key metrics using formulas that pull data from your "Bill Tracker" sheet.

Key Financial Metrics

Set up labels like:

• Total Monthly Bills: The sum of all bills due this month.
• Total Paid: The sum of all bills marked as "Paid."
• Amount Remaining: The total amount of unpaid bills.
• Overdue Bills: A count of how many bills are overdue.

Formulas for Your Dashboard

Assuming your main data is on the sheet named "Bill Tracker," use these formulas:

• Total Monthly Bills:

=SUM('Bill Tracker'!C:C)

(this sums all "Amount Due" values, for more precise tracking by month, consider using SUMIFS)

• Total Paid:

=SUMIF('Bill Tracker'!F:F,"Paid",'Bill Tracker'!C:C)

(sums amounts where status is "Paid")

• Amount Remaining:

=SUMIF('Bill Tracker'!F:F,"<>Paid",'Bill Tracker'!C:C)

(sums amounts where status is not "Paid")

• Overdue Bills:

=COUNTIF('Bill Tracker'!F:F,"Overdue")

(counts how many bills are overdue)

This simple dashboard turns your list of bills into actionable financial information.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 5: Adding a Chart to Visualize Spending

The "Category" column we set up earlier is perfect for creating a chart that shows where your money is going. A pie chart is a great way to visualize this breakdown.

First, summarize your spending by category on your Dashboard sheet:

1. List your unique categories in a column (e.g., Housing, Utilities, Subscriptions).
2. Next to each category, use a SUMIF formula to calculate total spent:

=SUMIF('Bill Tracker'!B:B, A2, 'Bill Tracker'!C:C)

where A2 contains the category name (e.g., "Housing"). Drag down for all categories.

1. Highlight your summary table (category names and totals).
2. Go to the Insert tab and click the Pie Chart icon. Choose a 2-D or 3-D Pie.

Excel will instantly generate a chart that visually represents your spending habits, helping you see which categories take up the biggest portion of your budget.

Final Thoughts

By following these steps, you’ve created a feature-rich bill tracker in Excel that goes beyond a simple list. You've set up columns, automated status updates with formulas, added visual alerts with conditional formatting, and built a dashboard to summarize it all. This tool can genuinely help you stay on top of your finances and avoid pesky late fees for good.

Building reports like this in Excel is a great skill, but manually updating and maintaining trackers can be time-consuming. We built Graphed to remove this friction. Instead of building from scratch, you can connect your data sources (like your bank or accounting software) and use simple, natural language to create dashboards instantly. Ask a question like, "Show me my spending by category for the last 30 days as a pie chart," and get a live, automated report in seconds, not hours.