How to track unpaid invoices and partial payments in Excel

Start with two tables, not one giant spreadsheet

The easiest way to lose track of receivables is to keep overwriting the same row every time a customer pays. A cleaner setup is to separate invoices from payments. The invoice table tells you what was billed. The payment table records every payment event. A shared Invoice ID connects them.

Table 1: Invoice log

Create an Excel sheet called Invoices with these starter columns:

• Invoice ID — a unique value such as INV-1001.
• Client — customer or business name.
• Invoice Date — when the invoice was issued.
• Due Date — the agreed payment due date.
• Invoice Amount — the original amount billed.
• Payments Received — total payments linked to this invoice.
• Remaining Balance — invoice amount minus payments received.
• Days Overdue — how many days past the due date, if still unpaid.
• Next Follow-Up Date — when you plan to review or contact the client again.
• Notes — short context such as “client said payment Friday.”

Table 2: Payment log

Create another sheet called Payments. Use one row per payment, even when several payments belong to the same invoice:

• Payment ID
• Payment Date
• Invoice ID
• Payment Amount
• Method or Reference — optional, if useful for your workflow.
• Notes — optional.

This structure makes partial payments much easier. You never need to erase an earlier payment just because a second payment arrives.

Calculate the amount still unpaid

If your payment log uses Invoice ID in one column and Payment Amount in another, Excel can total every payment for an invoice with SUMIFS. The idea is simple:

For example, imagine invoice INV-1004 is $1,200. The client pays $400, then later pays $300. Your payment log contains two rows. Total payments are $700, so the remaining balance is $500. You keep the full history without changing the original $1,200 invoice amount.

Do not use “Paid / Unpaid” as your only status

A binary status hides the work that actually needs attention. A useful invoice review should distinguish at least these situations:

You can add a “Due Soon” state if your business benefits from reviewing invoices a few days before the due date. The goal is not to create a fancy dashboard. The goal is to know which invoices need attention today.

Use aging to prioritize, not to scare customers

Invoice aging simply groups unpaid balances by how long they have been outstanding. A common operational view is Current, 1–30 days overdue, 31–60, 61–90 and 90+ days. Those buckets help you review older balances first and see whether late payments are becoming a pattern.

If you calculate days overdue in Excel, keep the logic tied to the remaining balance. An invoice with a zero balance should not continue looking overdue just because its due date is in the past.

Aging is an organizational signal, not legal or collections advice. Your communication and collection practices should follow the agreements and laws that apply to your business.

Track the next action, not just the last message

Notes like “emailed client” are useful history, but they do not answer the next question. Add fields that make the future action visible:

• Last Follow-Up Date
• Next Follow-Up Date
• Promise-to-Pay Date, if a client gives one
• Short Follow-Up Note

When you open the spreadsheet, you should be able to filter for “next follow-up date is today or earlier” and immediately see your work list.

A simple weekly invoice follow-up routine

For a small service business, a short recurring review is often more useful than constantly checking the sheet. A practical weekly rhythm can be:

1. Check new payments. Add each payment to the payment log.
2. Review remaining balances. Confirm that paid invoices reached zero.
3. Review due and overdue invoices. Sort by overdue days or aging bucket.
4. Check promised payment dates. See which commitments are due.
5. Set the next follow-up date. Give every open item a clear next review point.
6. Look at the total pattern. Are overdue balances growing? Are the same clients repeatedly late?

This routine turns the spreadsheet into a work queue instead of a storage file.

Five mistakes that make invoice spreadsheets harder to trust

1. Reusing invoice IDs

If two invoices share the same ID, payments can roll up to the wrong record. Keep Invoice ID unique.

2. Typing the total paid directly into the invoice row

This destroys payment history. Record payment events separately, then calculate the total.

3. Overwriting the original invoice amount

Keep the amount billed separate from the amount received and the remaining balance.

4. Tracking “overdue” without a due date

You cannot calculate meaningful overdue days unless the underlying due date is reliable.

5. Recording the last contact but no next action

A history log is useful, but a next follow-up date is what keeps the workflow moving.

Build a usable version in 15 minutes

If you are starting from nothing, do not build charts first. Create the two tables, enter five real invoices, enter any payments already received, calculate remaining balances, then add a next follow-up date to every invoice that is still open. Once that works, add aging buckets or a dashboard only if they help you make a decision faster.

Next: understand invoice aging

Once your invoice and payment logs are working, aging helps you sort the remaining unpaid balances by how late they are. Read Invoice Aging Explained for the Current, 1–30, 31–60, 61–90 and 90+ buckets, or use the free Invoice Aging Calculator with one invoice.

When a ready-made workbook may save time

If this becomes a recurring job and you would rather not build the formulas, aging view, monthly summary and dashboard yourself, PoonthaiDigital has a paid Excel version built around the same workflow. It includes separate invoice and payment logs, aging and follow-up fields, a monthly summary and dashboard.

See exactly what the Invoice & Payment Follow-Up Tracker includes before deciding whether it is useful for you. The product page also explains what the workbook does not do.

This guide is for general business organization and workflow planning. It is not accounting, tax, legal or collections advice.