Introduction
Sending an invoice is only one part of getting paid. You also need to know when payment is due, whether the client has paid, how much remains outstanding, and which invoices need a follow-up.
When you're managing several clients, keeping track of this information through emails, separate spreadsheets, and individual documents can quickly become confusing.
An invoice tracker gives you one place to monitor your billing activity. Instead of searching through emails to remember which client owes what, you can review your invoices in a structured table.
In this guide, you'll learn how to create an invoice tracker in Excel, which columns to include, how to calculate outstanding balances, and how to identify overdue invoices automatically.
1. What Is an Invoice Tracker?
An invoice tracker is a spreadsheet or tool used to record invoices and monitor their payment status.
A typical tracker includes:
Invoice number
Client name
Invoice date
Payment due date
Invoice amount
Amount paid
Outstanding balance
Payment status
Days overdue
The purpose is simple: make it easier to understand what you've billed, what you've collected, and what still needs attention.
For freelancers, consultants, agencies, and businesses that bill clients regularly, an invoice tracker can help reduce missed follow-ups and improve visibility into outstanding payments.
2. Create Your Invoice Tracker in Excel
Open a new Excel workbook and create a worksheet named Invoice Tracker.
Add the following column headings in the first row:
Column | Heading | Purpose |
|---|---|---|
A | Invoice Number | Unique invoice reference |
B | Client Name | Customer or business name |
C | Invoice Date | Date the invoice was issued |
D | Due Date | Date payment is expected |
E | Invoice Amount | Total amount billed |
F | Amount Paid | Total payment received |
G | Balance Due | Remaining amount to collect |
H | Payment Status | Current payment state |
I | Days Overdue | Number of days past the due date |
You can also add columns for currency, payment method, invoice link, and follow-up notes if you work with multiple currencies or need additional context.
Example invoice tracker
Here's an example using fictional client records and amounts in USD.
Invoice No. | Client | Invoice Date | Due Date | Invoice Amount | Amount Paid | Balance Due |
|---|---|---|---|---|---|---|
INV-101 | Northstar Studio | 01-Oct-2026 | 15-Oct-2026 | $1,200 | $1,200 | $0 |
INV-102 | Greenfield Media | 05-Oct-2026 | 20-Oct-2026 | $850 | $300 | $550 |
INV-103 | Apex Consulting | 10-Oct-2026 | 24-Oct-2026 | $1,500 | $0 | $1,500 |
This example illustrates three different situations: an invoice paid in full, one partially paid, and one awaiting payment.
The outstanding balance is the first number to calculate because it shows how much money is still expected from each client.
3. Calculate the Outstanding Balance Automatically
Manually updating the remaining balance every time a client makes a payment can introduce errors.
Instead, use a formula.
In cell G2, enter:
=MAX(0,E2-F2)
This subtracts the amount paid from the invoice amount and prevents the displayed balance from going below zero.
For example, if an invoice is for $850 and the client has paid $300, the remaining balance will be $550.
Copy the formula down the column for the rest of your invoices.
Important: This formula assumes that the amount paid is recorded as a cumulative total and that payments don't exceed the invoice amount. If you need to track overpayments, refunds, credits, or disputed balances, use additional columns rather than hiding those differences.
4. Automatically Identify the Payment Status
A payment-status column helps you identify which invoices are paid, outstanding, partially paid, or overdue.
In cell H2, enter:
=IF(G2=0,"Paid",IF(F2>0,"Partially Paid",IF(TODAY()>D2,"Overdue","Unpaid")))
The formula checks the remaining balance, amount paid, and due date.
It returns:
Paid: No balance remains.
Partially Paid: A payment has been received, but a balance remains.
Overdue: The invoice still has an outstanding balance and the due date has passed.
Unpaid: The invoice has an outstanding balance and its due date has not passed.
Copy the formula down the column.
This formula assumes that every invoice has a valid due date and that payments are recorded accurately. If you use payment terms, disputes, or credit notes, adjust the status logic to reflect your process.
5. Calculate How Many Days an Invoice Is Overdue
Knowing that an invoice is overdue is useful. Knowing how late it is can help you prioritize follow-ups.
In cell I2, enter:
=IF(AND(G2>0,TODAY()>D2),TODAY()-D2,0)
The formula returns the number of days past the due date when a balance remains. Otherwise, it returns zero.
For example, if an invoice was due on 15 October and remains unpaid on 25 October, it will show 10 days overdue.
This can help you sort invoices from the most overdue to the least overdue.
Keep in mind that Excel's TODAY() function updates based on the current date when the workbook recalculates. Historical reporting may require recording the status as of a particular date.
6. Highlight Overdue Invoices With Conditional Formatting
A spreadsheet becomes easier to review when overdue invoices stand out visually.
You can use Excel's conditional formatting to highlight overdue rows.
Select your invoice data range, such as
A2:I100.Open Home → Conditional Formatting → New Rule.
Choose the option to use a formula to determine which cells to format.
Enter this formula:
=AND($G2>0,$D2<TODAY())Choose a noticeable fill colour and save the rule.
Excel will highlight rows where the balance is greater than zero and the due date has passed.
You can also create a separate rule to highlight invoices due within the next seven days:
=AND($G2>0,$D2>=TODAY(),$D2<=TODAY()+7)
These visual indicators make it easier to see which invoices need attention without reviewing every row individually.
7. Summarize Your Outstanding Invoices
Once your tracker contains several invoices, you may want to see totals instead of reviewing each record individually.
Create a small summary section above or beside your table.
Total invoiced
=SUM(E2:E100)
This calculates the total amount billed across the listed invoices.
Total collected
=SUM(F2:F100)
This calculates the total amount recorded as paid.
Total outstanding
=SUM(G2:G100)
This shows the remaining balance across your invoices.
Count of overdue invoices
=COUNTIF(H2:H100,"Overdue")
This counts invoices currently marked as overdue.
These summaries give you a quick view of your billing activity. If you work with multiple currencies, calculate totals separately by currency unless you have converted them using a consistent exchange-rate method.
Also, remember that outstanding invoices are not the same as available cash. An invoice can remain unpaid for weeks, so use a separate cash flow forecast to understand expected receipts and upcoming payments.
8. Common Invoice Tracking Mistakes to Avoid
Even a well-designed spreadsheet can become unreliable if the underlying information isn't maintained consistently.
Not updating partial payments
If a client pays only part of an invoice, update the cumulative amount paid. Otherwise, the outstanding balance will be incorrect.
Forgetting to review due dates
A tracker is most useful when you review it regularly. Consider setting a recurring time each week to check upcoming and overdue invoices.
Using inconsistent invoice numbers
Assign each invoice a unique reference so you can identify it quickly when communicating with clients.
Mixing different currencies in one total
If you bill clients in USD, EUR, INR, or other currencies, keep amounts separated by currency or clearly document the conversion method.
Treating an invoice as cash received
Sending an invoice doesn't mean the money has reached your account. Track payments received separately from amounts billed.
Keeping multiple versions of the spreadsheet
If several copies circulate through emails and folders, you may not know which one contains the latest information. Store the current version in a clearly defined location and establish who is responsible for updating it.
9. When Should You Move Beyond Excel?
Excel can be a good starting point if you have a manageable number of invoices and need a straightforward way to monitor payments.
However, as your client base grows, manual tracking can become more demanding.
You may benefit from a dedicated invoicing workflow when you need to:
Create invoices consistently.
Keep client details organized.
Track invoice and payment statuses in one place.
Reduce manual updates across separate files.
Access billing information without searching through multiple spreadsheets.
The right choice depends on how you work. A spreadsheet may be sufficient for your current needs, while a dedicated tool may make sense when manual administration starts consuming too much time.
10. Simplify Your Invoice Workflow With PayinGlobal
Creating an invoice tracker in Excel gives you a structured way to monitor billing and outstanding payments. But if you're also managing client information, creating invoices, and following up on payments, you may prefer to keep those activities within a dedicated workflow.
PayinGlobal's Invoicing & Clients tool is designed to help organize client billing and invoice-related work.
Explore the tool here:
PayinGlobal Invoicing & Clients
You can also explore the PayinGlobal template gallery for reusable templates to support recurring business tasks.
The goal isn't to add another tool for the sake of it. It's to choose a workflow that makes your billing easier to maintain as your work grows.
Frequently Asked Questions
Can I track unpaid invoices in Excel?
Yes. Create columns for invoice numbers, client names, invoice dates, due dates, invoice amounts, amounts paid, and outstanding balances. Formulas can calculate balances and identify overdue invoices.
How do I automatically mark an invoice as overdue in Excel?
Use a formula that checks whether the due date has passed and whether an outstanding balance remains. For example:
=IF(G2=0,"Paid",IF(F2>0,"Partially Paid",IF(TODAY()>D2,"Overdue","Unpaid")))
This formula assumes the balance is in column G, the amount paid is in F, and the due date is in D.
How often should I update my invoice tracker?
Update it whenever you issue an invoice, receive a payment, or change an invoice's status. Review outstanding invoices at least weekly if your billing volume warrants it.
Is an invoice tracker the same as an accounting system?
No. An invoice tracker focuses on billing and payment status. An accounting system typically supports a broader range of activities, such as recording transactions, reconciling accounts, and preparing financial reports.
What should I do if a client pays an invoice in multiple installments?
Record the cumulative amount paid and update it each time another payment arrives. The outstanding balance formula will calculate the remaining amount.
Final takeaway
A reliable invoice tracker helps you answer three important questions: What have I billed? What have I collected? What still needs follow-up?
Start with a clear table, use formulas to reduce manual calculations, highlight overdue invoices, and review the information consistently.
As your invoicing needs grow, consider whether a dedicated tool can reduce the effort required to maintain your workflow.
Explore PayinGlobal: https://www.payinglobal.com/
This article is for general educational purposes. The spreadsheet examples are illustrative and may need adjustment for your invoicing process, tax requirements, and accounting practices.




