Free payment tracker Excel template (and Google Sheets)

A payment tracker is a spreadsheet that records every invoice you send and every payment you receive in one place, so you always know who owes you what and which invoices are overdue. The fastest way to start is with a ready-made template. Chaser's free payment tracker template for Excel and Google Sheets comes with the right columns, formulas, and overdue highlighting already built in, so you can track payments in minutes rather than building a sheet from scratch.

If you invoice customers, you need a simple way to see what is paid, what is outstanding, and what needs chasing today. Most small businesses start in Excel or Google Sheets because they already have them and they cost nothing. The problem is a blank spreadsheet is easy to get wrong, and a messy tracker is worse than none at all. This guide fixes that. Download the ready-made template below, or follow the step-by-step build to create your own, then keep reading for the formulas, the habits that make it work, and the point at which it pays to automate.

What is a payment tracker template?

A payment tracker template is a pre-built spreadsheet that records the invoices you have issued, the payments you have received, and the balance still outstanding on each one. Instead of designing the columns and writing the formulas yourself, you open a ready-made file in Excel or Google Sheets, add a row for each invoice, and let the built-in formulas keep your totals and overdue figures up to date. It is the simplest way to keep track of payments without buying any software.

A payment tracker template is most useful for freelancers juggling invoices across several clients, small business owners managing their own accounts receivable, and finance teams that want a quick view of outstanding payments without specialist tools. Suppliers and vendors also use one to track what business customers owe them. Excel and Google Sheets are the popular choices because they are familiar, easy to customise, and free to use. When the volume grows, this is the same job that Chaser's accounts receivable software takes over automatically.

Download the free payment tracker Excel template

Chaser's free payment tracker template works in both Microsoft Excel and Google Sheets, and it is ready to use with no setup. The formulas, the running balance, and the colour-coded overdue highlighting are already in place, so you can add your first invoice the moment you open it. It is built for finance teams, small businesses, freelancers, and vendors who want a customer payment tracker that just works.

Here is what comes built in to the free template:

• Ready-made columns for invoice number, customer, amount, invoice date, due date, date paid, and balance.
• Automatic days overdue, calculated from the due date so it updates itself every day.
• Colour-coded overdue highlighting, so invoices that need chasing stand out at a glance.
• A running balance and totals, so you always see your real outstanding position.
• Both formats in one download, an .xlsx file for Excel and a Google Sheets version to copy.

What columns should a payment tracker include?

A payment tracker needs nine core columns to give you a complete picture of every invoice: invoice number, customer, amount, invoice date, due date, date paid, status, days overdue, and balance. These columns let you identify each invoice, see when it was due, record when it was paid, and know exactly how much is still owed. The table below shows what each one does and a worked example.

Optional extras worth adding: a payment method column for reconciliation, a late fee column, and a free-text notes column for partial payments or disputes.

If you only ever add one extra column, make it customer. Because the customer sits in its own field, you can filter or sort the whole sheet by client to see every invoice for one customer, what they have paid, and what is still outstanding. That gives you a full billing history per customer inside the same file, which is exactly what people mean when they search for a customer, client, or vendor payment tracker.

How to keep track of customer payments in Excel, step by step

To keep track of customer payments in Excel, set up one row per invoice with the nine core columns, add a couple of simple formulas to work out days overdue and the outstanding balance, then turn on conditional formatting so overdue rows go red. You can do this from a blank sheet in about fifteen minutes, or skip straight to recording invoices by downloading the free template above. Here is the full build.

Step 1: Set up your column headers

Open a new Excel or Google Sheets file and, in row 1, type your headers across the columns in this order: invoice number, customer, amount, invoice date, due date, date paid, status, days overdue, and balance. Put your business name and the reporting period in a couple of cells above the table so each saved copy is clearly labelled. Format the amount and balance columns as currency, and the three date columns as dates, so your figures stay consistent.

Step 2: Add each invoice as a new row

For every invoice you send, add a new row and fill in the invoice number, customer, amount, invoice date, and due date. Do this the moment you raise the invoice, not when you start chasing it, because a tracker only works if it reflects reality. Leave the date paid column blank for now. Use the same format for dates and customer names on every row, or filtering and sorting later will be unreliable.

Step 3: Add a formula to calculate days overdue

The days overdue column should work itself out so you never count days by hand. The simplest approach uses the TODAY function, which returns today's date and updates every time the file is opened, as set out in Microsoft's TODAY function guidance. Subtract the due date from today, and only show a number when the invoice is unpaid and actually late. Assuming the due date is in column E and the date paid is in column F, the formula for row 2 is:

In plain English: if the invoice is paid, show 0; otherwise, if today is past the due date, show the number of days late; if not, show 0.

Step 4: Add a formula for the outstanding balance and status

The balance column shows what is still owed on each invoice. If the amount is in column C and the amount paid is recorded in the date paid workflow, the simplest version subtracts any payment from the amount. A clear way to set the status is with the IF function, which returns one value when a condition is true and another when it is false, as described in Microsoft's IF function guidance. With the due date in E and the date paid in F, this status formula for row 2 reads:

This reads: if there is a payment date, mark it "Paid"; if not and the due date has passed, mark it "Overdue"; otherwise mark it "Due".

To total your outstanding receivables at the bottom of the balance column, use a simple sum such as =SUM(I2:I100), adjusting the range to fit your rows. If you ever need the gap between two specific dates rather than today, Microsoft's guide on calculating the difference between two dates walks through the options.

Step 5: Turn on conditional formatting for overdue rows

Conditional formatting means overdue invoices colour themselves, so you never scan the sheet by eye. Select your data rows, open conditional formatting, and add a rule based on a formula. A rule such as =$G2="Overdue" set to a red fill turns any overdue row red, and a second rule of =$G2="Due" set to amber flags invoices coming up. Now a glance tells you exactly which customers to chase first. The free template above has these rules already built in.

Step 6: Record payments as they arrive

When a customer pays, enter the date in the date paid column on that invoice's row. The status flips to "Paid", the days overdue resets to 0, and the balance updates on its own. You never have to touch the formulas. Then work top down through anything still red, and reach for a ready-made payment reminder email template so you can chase politely without writing each message from scratch.

Tips to get the most from your payment tracker spreadsheet

A payment tracker only helps if you keep it current and tidy. The habits below take seconds each but make the difference between a sheet you trust and one you quietly stop using. Build them into your week and your tracker stays accurate enough to base real cash flow decisions on.

• Log invoices the day you send them. Adding the row straight away keeps the tracker honest and your balance accurate.
• Keep formats consistent. Use the same date, currency, and customer-name format on every row so sorting and filtering stay reliable.
• Sort by days overdue. A quick sort surfaces your most urgent chasers in seconds.
• Review overdue rows weekly. A five-minute weekly check catches problems before they become cash flow issues.
• Back it up to the cloud. Save to Google Drive or OneDrive so your records are safe and reachable from anywhere.
• Send a statement of account. For customers with several open invoices, a statement of account summarises everything they owe in one place.

Excel vs Google Sheets vs dedicated software: which should you use?

Excel, Google Sheets, and dedicated accounts receivable software all track payments, but they suit different stages. A spreadsheet is the right starting point when you are managing a small, steady number of invoices. Dedicated software earns its place once chasing, reconciling, and forecasting by hand starts eating your week. The table below compares the three so you can pick the right tool for where your business is now.

Excel and Google Sheets are close cousins. Choose Excel if your business already runs on Microsoft Office and you prefer working offline, and Google Sheets if you want several people updating the same tracker in real time. The free template comes in both formats, so you do not have to decide up front. The bigger decision is when to move off a spreadsheet altogether, which the next section covers.

When does a payment tracker spreadsheet stop working?

A payment tracker spreadsheet stops working well once you are managing more than roughly 30 to 40 active invoices a month. Beyond that, manual data entry takes real time, the risk of typos and broken formulas climbs, and the one thing a spreadsheet can never do becomes a daily drag: it cannot chase a customer, reconcile a payment, or forecast your cash. A sheet can show you an invoice is overdue, but you still have to write and send every reminder yourself.

The hidden cost is your time. Finance teams spend around 14 hours a week on manual receivables admin, according to Intuit, and roughly 8% of revenue is written off to bad debt each year on average, according to Sage. A spreadsheet does nothing to reduce either figure. It records the problem, but it does not act on it, which is why the longer an invoice sits in the overdue column, the harder it gets to collect.

Sources: Intuit, Sage, CFO.com.

This is the point where the spreadsheet has done its job and it is time for a tool that acts. Chaser's accounts receivable software does everything the template does, then keeps going: it sends personalised payment reminders on a schedule you set, syncs two ways with accounting tools such as Xero, QuickBooks, and Sage so a paid invoice is never chased, and shows you days sales outstanding and a real-time cash flow forecast. The result is fewer hours on admin, fewer write-offs, and more confidence in your numbers, the gap that leaves 64% of finance leaders uncertain about their cash flow data, according to CFO.com.

Frequently asked questions

How do I keep track of customer payments in Excel?

Set up one row per invoice with columns for invoice number, customer, amount, invoice date, due date, date paid, status, days overdue, and balance. Add a TODAY-based formula for days overdue and conditional formatting so overdue rows go red. The quickest start is to download Chaser's free payment tracker template, which has all of this built in.

How do I keep track of payments received?

Record each payment against its invoice by entering the date it arrived in a date paid column. In a well-built tracker, this automatically updates the status to paid, resets the days overdue to zero, and clears the outstanding balance, so you always have a dated record of exactly what has been received and what is still owed.

What columns should a payment tracker include?

A payment tracker should include nine core columns: invoice number, customer, amount, invoice date, due date, date paid, status, days overdue, and balance. Useful optional columns are payment method for reconciliation, late fees, and a free-text notes field for partial payments or disputes. Chaser's free template includes all of these.

Is the payment tracker template free to download?

Yes. Chaser's payment tracker template is free to download and works in both Microsoft Excel and Google Sheets. It comes with the columns, formulas, running balance, and overdue highlighting already set up, so there is nothing to build or configure. You add a row for each invoice and start tracking straight away.

Should I use Excel or Google Sheets for a payment tracker?

Use Excel if your business already runs on Microsoft Office and you prefer working offline. Use Google Sheets if you want several people updating the same tracker in real time from a browser. Both handle the same formulas and formatting, and Chaser's free template comes in both formats, so you can pick whichever suits your team.

When should I move from a spreadsheet to accounts receivable software?

A spreadsheet works well up to around 30 to 40 active invoices a month. Beyond that, manual entry takes too long and errors creep in. A spreadsheet also cannot chase, reconcile, or forecast. At that point, tools like Chaser automate invoice chasing, payment reminders, and debtor reporting, so your team spends less time on admin and gets paid faster.

What is the difference between a payment tracker and an invoice tracker?

An invoice tracker records the invoices you have issued and whether they have been paid. A payment tracker is slightly broader and can cover all incoming and outgoing payments, not just invoices. Chaser's free template covers both: it tracks the invoices you send and records the payments received against each one in the same file.

Sources: Microsoft, TODAY function, Microsoft, IF function, Microsoft, calculate the difference between two dates, Intuit, Sage, CFO.com.