How to Create a Bill Tracker in Google Sheets

Building a personal bill tracker is one of the best ways to get a handle on your finances, and you don’t need fancy software to do it. You can create a powerful and completely customized system using a tool you already have: Google Sheets. This guide will walk you through setting up a simple but effective bill tracker from scratch, complete with automated reminders and visual dashboards.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Setting Up Your Google Sheet Foundation

First things first, let's create the canvas for your new financial command center. This part is quick and easy.

Open your browser and navigate to sheets.new. This handy shortcut instantly creates a brand new, untitled Google Sheet, saving you a few clicks. Give your tracker a memorable name by clicking on "Untitled spreadsheet" in the top-left corner. Something like "2024 Bill Tracker" or "My Financial Hub" works perfectly.

It's also a good practice to name a tab at the bottom. Right-click on "Sheet1" and select "Rename." You might name it "Monthly Bills" or by the current month, like "October 2024."

Designing Your Bill Tracker: The Essential Columns

The foundation of any good tracker is its structure. We'll set up columns that capture all the essential information you need to stay on top of your payments. In the first row of your sheet, enter the following headers, one in each cell starting from A1:

• Bill Name (Column A): The name of the service or bill (e.g., "Netflix," "Rent," "Electricity Bill").
• Category (Column B): A category to help you see where your money is going (e.g., "Entertainment," "Housing," "Utilities," "Debt").
• Amount Due (Column C): The total amount you need to pay for this bill.
• Due Date (Column D): The date the payment is due.
• Status (Column E): The current status of the bill. We’ll make this a dropdown later with options like "Paid," "Unpaid," and "Pending."
• Payment Date (Column F): The date you actually paid the bill.
• Days Remaining (Column G): An automated column that shows how many days you have left until a bill is due.
• Confirmation # (Column H): A place to drop the payment confirmation number for your records.
• Notes (Column I): An optional column for any extra details, like account changes or payment plan information.

After you've added these headers, make them stand out. Select the entire first row by clicking on the number "1" on the far left. Use the toolbar to make the text bold, change the background color of the cells, or even increase the font size. This makes your tracker much easier to read.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Populating Your Tracker with Bills

Now, start listing all your bills for the month. Don't worry about getting everything perfect on the first try, you can always add more later. For recurring bills with a fixed amount, you can enter them for several months in advance. For variable bills like electricity or credit cards, you can add them as the statements arrive.

Adding Automation with Formulas

This is where your static spreadsheet becomes a dynamic and intelligent tool. Formulas do the heavy lifting for you, providing at-a-glance insights and minimizing manual work.

Automating 'Days Remaining' with a Formula

Knowing when a bill is approaching is the most valuable part of a tracker. We can create a simple formula that compares the due date to today's date and tells you how many days are left. This updates automatically every single day.

In cell G2 (the first cell under the 'Days Remaining' header), type the following formula:

=IF(E2="Paid", "Paid", D2 - TODAY())

Let's break that down:

• IF(E2="Paid", "Paid", ...): This is a conditional statement. It checks if the cell in the 'Status' column (E2) says "Paid." If it does, it will display the word "Paid" in the 'Days Remaining' cell.
• D2 - TODAY(): If the status is not "Paid," this part of the formula runs. It takes the date in the 'Due Date' column (D2) and subtracts today's date. The result is the number of days until the due date. A negative number here means the bill is overdue!

Once you've entered the formula in G2, copy it down for all your other bills. Click on the cell G2, grab the tiny blue square (the "fill handle") in the bottom-right corner, and drag it down to the last row of your bill list. Google Sheets will automatically adjust the formula for each row.

Building a Summary Section

It's helpful to see a high-level summary of your financial obligations. Let's create a small dashboard at the top or side of your tracker.

Find an empty space and create these labels in separate cells: "Total Bill Amount," "Total Paid," and "Amount Remaining."

1. To calculate the Total Bill Amount:

This formula adds up all the values in your "Amount Due" column (Column C). We use C2:C to make sure it includes all rows, even new ones you add later.

=SUM(C2:C)

2. To calculate the Total Paid:

This formula only adds up the amounts for bills you've marked as "Paid" in the 'Status' column. We use SUMIF for this, which adds numbers based on a condition.

=SUMIF(E2:E, "Paid", C2:C)

This tells Google Sheets to look in the range E2:E for the word "Paid," and if it finds it, to add the corresponding number from the range C2:C.

3. To calculate the Amount Remaining:

This one is simple subtraction. Just subtract the "Total Paid" from the "Total Bill Amount." If you put the formula above in cell K2 and the first formula in cell K1, your formula would be:

=K1 - K2

Now you have a dynamic summary that updates in real-time as you pay your bills!

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Enhancing Your Tracker for Maximum Impact

Let's add a few professional features that make your tracker more functional and visually appealing. These touches are simple to implement but make a huge difference.

Use Conditional Formatting for At-a-Glance Status

Conditional formatting changes a cell's appearance based on its value. We’ll use it to color-code your bills so you can instantly see what's paid, what's upcoming, and what's dangerously overdue.

1. Select your entire data range, starting from "Bill Name" in A2 to the last cell in your 'Notes' column. Don’t include the headers.
2. Go to the menu and click Format > Conditional formatting.
3. A sidebar will appear. Under "Format rules," change the dropdown for "Format cells if..." to "Custom formula is."

Rule 1: Highlight Paid Bills (Gray Out)

Enter this formula to gray out and strike through any rows marked as "Paid."

=$E2="Paid"

Then, under "Formatting style," click the "Strikethrough" button and choose a light gray text or fill color. Click "Done."

Rule 2: Highlight Overdue Bills (Red)

Click "Add another rule." Use this formula to highlight bills that are overdue and haven't been paid yet.

=AND($E2<>"Paid", $D2<TODAY())

This checks two things: is the status not "Paid," AND is the due date before today? If both are true, it applies the formatting. Choose a red background color to make these stand out. Click "Done."

Rule 3: Highlight Bills Due Soon (Yellow)

Add one last rule to highlight bills coming up. This formula flags bills due within the next 7 days.

=AND($E2<>"Paid", $D2-TODAY()<=7)

Choose a yellow or orange background color as a warning. Click "Done." The order of these rules matters, so make sure the "Paid" rule is at the top of the list in the sidebar.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Create Drop-Down Menus for Consistency

Drop-down menus prevent typos and keep your data clean, which is essential for formulas to work correctly. We'll add one to the "Status" and "Category" columns.

1. Select the entire "Status" column (excluding the header), from E2 down.
2. Go to Data > Data validation.
3. Click "+ Add rule." For "Criteria," choose "Dropdown."
4. Enter your options: "Paid," "Unpaid," "Pending." You can even assign colors to each option right here.
5. Click "Done." Now a clickable dropdown arrow will appear in each cell in that column.

Repeat the same process for your "Category" column, adding options relevant to your budget like "Housing," "Utilities," "Groceries," "Subscriptions," etc.

Visualize Your Spending with Charts

A pie chart is a great way to see where your money is going each month. Google Sheets makes creating one incredibly simple.

1. Highlight your 'Category' and 'Amount Due' columns (B and C). You can select one column, then hold Ctrl (or Cmd on Mac) and click the other column header to select both.
2. Go to the menu and click Insert > Chart.
3. Google Sheets will often suggest a pie chart automatically. If not, you can select it from the Chart Editor sidebar under "Chart type."

You can customize the chart's title, colors, and labels from the editor. Drag this chart to your summary area so you have a complete visual dashboard of your monthly finances.

Final Thoughts

In just a short time, you’ve built more than just a list of bills, you’ve created a dynamic, automated financial dashboard in Google Sheets. By tracking your due dates and analyzing your spending categories, you're taking a significant step toward gaining full control over your financial health.

Just like organizing your personal finances in a spreadsheet can bring clarity, the right tools can demystify your company's performance data. Businesses often face the challenge of data scattered across different platforms - marketing analytics in one place, sales numbers in another. At Graphed, we solve this by connecting all your data sources and allowing you to simply ask questions in plain English to build real-time dashboards and reports. It’s about spending less time chasing down numbers and more time making well-informed decisions.