How to Keep Track of Invoices and Payments in Excel: 3 Examples

For most small businesses, the most reliable Excel setup is two linked tables: one row per invoice in an Invoices table and one row per payment in a separate Payments table. Excel can then calculate payments received, outstanding balances, overdue amounts, and collection status automatically.

If your invoices are few and payments are simple, a one-sheet tracker may be enough. If you need partial payments, payment history, or management reporting, use the second or third example below.

As an Amazon Associate I earn from qualifying purchases.

Choose the right Excel setup

Your situation	Best setup
Few invoices and generally one payment per invoice	Simple one-sheet tracker
Partial payments or multiple payments against one invoice	Separate Invoices and Payments tables
Regular collection reviews or customer-level reporting	Two tables plus a dashboard and aging report
Bank matching, automated reminders, tax reporting, or formal accounting records	Dedicated invoicing or accounting software

Excel supports the formulas, tables, lookups, date calculations, and conditional formatting needed for these systems. See Microsoft’s formula examples and Excel help resources for interface-specific guidance.

Set up the workbook before entering data

Use Excel Tables

1. Enter your column headings.
2. Select the range.
3. Choose Insert > Table.
4. Confirm My table has headers.
5. Rename the table under Table Design > Table Name.

Name the two main tables Invoices and Payments. Tables automatically extend formulas and formatting when new rows are added in normal use, and structured references are easier to maintain than arbitrary ranges.

Use consistent identifiers and formats

Every invoice needs a unique InvoiceID, such as INV-1001. Do not reuse an invoice number after deleting or correcting a row. Format invoice totals and payment amounts as currency, format dates consistently, and use an explicit Currency column if you work in more than one currency.

Keep input columns separate from formula columns. Users should enter invoice and payment information, while Excel calculates paid-to-date, balance, status, and aging fields.

Add an “As of date” control

For a live workbook, TODAY() can update status every day. For historical reports, place a fixed reporting date in a cell such as B1 and use $B$1 in formulas. Otherwise, a report saved in August can show different aging results when opened in September.

Example 1: A simple one-sheet invoice tracker

This approach works for a freelancer or very small business with a low invoice volume and uncomplicated payments. It is quick to build, but it should not be your default if invoices commonly receive installments or multiple payments.

Column layout

Column	Purpose
Invoice #	Unique invoice identifier
Customer	Customer or account name
Invoice Date	Date issued
Due Date	Payment deadline
Amount	Original amount due
Amount Paid	Total entered manually
Balance	Amount still due
Status	Calculated payment state
Payment Date	Date paid, if applicable
Notes	Follow-up, dispute, or exception details

Sample data

Invoice #	Customer	Invoice date	Due date	Amount	Amount paid	Balance	Status
INV-1001	Acme Design	2026-08-01	2026-08-31	1,200	1,200	0	Paid
INV-1002	Northstar LLC	2026-08-05	2026-09-04	2,500	1,000	1,500	Partial
INV-1003	Greenline Co.	2026-07-10	2026-08-09	850	0	850	Overdue

Balance formula

If the first data row is row 2 and the invoice amount is in column E while amount paid is in column F, enter this in G2:

=E2-F2

You can show only the amount still owed with:

=MAX(0,E2-F2)

However, capping the result at zero can hide an overpayment. A better workbook keeps the raw difference visible or adds a separate credit/overpayment flag.

Status formula

This formula distinguishes paid, partial, overdue, and open invoices:

=IF(G2<=0,"Paid",IF(F2>0,IF(TODAY()>D2,"Partial—Overdue","Partial"),IF(TODAY()>D2,"Overdue","Open")))

A simpler version is:

=IF(G2<=0,"Paid",IF(TODAY()>D2,"Overdue","Open"))

Do not manually overwrite a formula-driven status to record an exception such as a dispute. Add separate columns such as Collection Status or Exception Status.

Days overdue

=IF(G2<=0,0,MAX(0,TODAY()-D2))

For a historical report whose as-of date is in B1:

=IF(G2<=0,0,MAX(0,$B$1-D2))

Identify invoices due soon

To flag unpaid invoices due within seven days:

=IF(AND(G2>0,D2>=TODAY(),D2<=TODAY()+7),"Due soon","")

Conditional formatting

Apply colors to make collection priorities visible:

• Red: unpaid and overdue.
• Yellow: unpaid and due within seven days.
• Green: balance is zero.
• Orange: partially paid.
• Purple or blue: disputed or on hold.

For a sheet beginning in row 2, an overdue rule is:

=AND($G2>0,$D2<TODAY())

A due-soon rule is:

=AND($G2>0,$D2>=TODAY(),$D2<=TODAY()+7)

A paid rule is:

=$G2<=0

In desktop Excel, create these under Home > Conditional Formatting > Manage Rules > New Rule > Use a formula to determine which cells to format. The formula must return TRUE or FALSE, and rule order matters when multiple rules apply. Labels can vary between desktop, Mac, web, and older editions. Microsoft’s conditional-formatting guide provides current interface guidance.

Control manually entered values

For a manually maintained status field, select the cells and choose Data > Data Validation. Set Allow to List and enter:

Open,Partial,Paid,Disputed,Written off

For the recommended formula-driven model, leave payment status calculated and use a separate list for collection exceptions.

Where this approach breaks down

A single row cannot preserve a dependable history when an invoice receives multiple installments, is paid by two methods, is refunded, or is partly reversed. It also encourages users to replace an earlier payment with a new total, destroying the audit trail. Move to separate invoice and payment tables as soon as those situations occur.

Example 2: Separate invoice and payment tables

This is the recommended default for most small businesses because it preserves payment history and handles partial and multiple payments.

1. Create the Invoices table

Create an Excel Table named Invoices with these columns:

InvoiceID
Customer
IssueDate
DueDate
InvoiceTotal
PaidToDate
Balance
AmountOutstanding
CreditOrOverpayment
Status
DaysOverdue
LastFollowUp
DocumentLink
Notes

At minimum, enter the first five fields for each invoice:

InvoiceID	Customer	IssueDate	DueDate	InvoiceTotal
INV-1001	Acme Design	2026-08-01	2026-08-31	1,200
INV-1002	Northstar LLC	2026-08-05	2026-09-04	2,500
INV-1003	Greenline Co.	2026-07-10	2026-08-09	850

2. Create the Payments table

Create a second Excel Table named Payments:

PaymentID
InvoiceID
PaymentDate
Amount
Method
Reference
Notes

PaymentID	InvoiceID	PaymentDate	Amount	Method	Reference
PAY-2001	INV-1001	2026-08-20	1,200	ACH	ACH-7781
PAY-2002	INV-1002	2026-08-18	1,000	Check	4821
PAY-2003	INV-1002	2026-08-25	500	Card	CARD-3350

INV-1002 has two payment rows. Its payment history remains intact, while Excel can still calculate the total received.

3. Calculate paid to date

In the PaidToDate column of the Invoices table, use:

=SUMIFS(Payments[Amount],Payments[InvoiceID],[@InvoiceID])

This adds every payment whose invoice ID matches the current invoice.

4. Calculate the balance transparently

Use the raw difference in Balance:

=[@InvoiceTotal]-[@PaidToDate]

Then add separate reporting fields:

AmountOutstanding:

=MAX(0,[@InvoiceTotal]-[@PaidToDate])

CreditOrOverpayment:

=MAX(0,[@PaidToDate]-[@InvoiceTotal])

This prevents an overpayment from disappearing from the report. A negative balance might indicate a customer credit, duplicate payment, incorrect allocation, refund due, or payment intended for another invoice.

5. Calculate payment status

Using the current date:

=IF([@PaidToDate]>=[@InvoiceTotal],"Paid",IF(TODAY()>[@DueDate],IF([@PaidToDate]>0,"Partial—Overdue","Overdue"),IF([@PaidToDate]>0,"Partial","Open")))

For reproducible reports, put the reporting date in B1 and use:

=IF([@PaidToDate]>=[@InvoiceTotal],"Paid",IF($B$1>[@DueDate],IF([@PaidToDate]>0,"Partial—Overdue","Overdue"),IF([@PaidToDate]>0,"Partial","Open")))

Payment status should describe the money state. Keep business exceptions such as Disputed, On hold, or Written off in a separate collection-status field.

6. Calculate days overdue

=IF([@PaidToDate]>=[@InvoiceTotal],0,MAX(0,TODAY()-[@DueDate]))

With a fixed reporting date:

=IF([@PaidToDate]>=[@InvoiceTotal],0,MAX(0,$B$1-[@DueDate]))

This ages the unpaid balance from the due date. It does not age the original invoice value after payments have been received.

7. Add input controls and error checks

For payment methods, use Data Validation with a list such as:

ACH,Check,Credit card,Cash,Wire transfer,Other

Maintain a separate customer list or Customers table rather than typing names inconsistently. Variations such as Acme Design, Acme Design LLC, and ACME DESIGN can split customer totals.

Useful checks include:

Duplicate invoice ID:

=COUNTIF(Invoices[InvoiceID],[@InvoiceID])>1

If structured references are not accepted in a conditional-formatting rule, use a range such as:

=COUNTIF($A$2:$A$500,A2)>1

Microsoft documents COUNTIF-based duplicate highlighting in its conditional-formatting guidance.

Payment assigned to an unknown invoice:

=IF(COUNTIF(Invoices[InvoiceID],[@InvoiceID])=0,"Check invoice ID","")

Missing due date:

=IF([@DueDate]="","Missing due date","")

Overpayment:

=IF([@PaidToDate]>[@InvoiceTotal],"Overpaid","")

Invalid payment amount:

=IF(OR([@Amount]="",[@Amount]<=0),"Check payment amount","")

Keep data-quality checks separate from business statuses. “Unknown invoice ID” is a data problem; “Disputed” is a collection or customer issue.

Example 3: Build an invoice dashboard and aging report

A dashboard is useful when reviewing collections weekly, prioritizing follow-up, or reporting receivables by customer and age.

Set the reporting date

Put an as-of date in B1, such as 2026-08-18. Use that cell in all dashboard formulas so the report can be reproduced later.

Core summary metrics

Total invoiced:

=SUM(Invoices[InvoiceTotal])

Total collected:

=SUM(Invoices[PaidToDate])

Total outstanding:

=SUM(Invoices[AmountOutstanding])

Report credits or overpayments separately rather than mixing them into the amount customers still owe.

Number of unpaid invoices:

=COUNTIFS(Invoices[Balance],">0")

Number of overdue invoices:

=COUNTIFS(Invoices[Balance],">0",Invoices[DueDate],"<"&$B$1)

Overdue amount:

=SUMIFS(Invoices[AmountOutstanding],Invoices[Balance],">0",Invoices[DueDate],"<"&$B$1)

Amount due in the next seven days:

=SUMIFS(Invoices[AmountOutstanding],Invoices[Balance],">0",Invoices[DueDate],">="&$B$1,Invoices[DueDate],"<="&$B$1+7)

Create aging buckets

Aging should normally be based on the unpaid balance and the number of days after the due date.

Current or not yet due:

=SUMIFS(Invoices[AmountOutstanding],Invoices[Balance],">0",Invoices[DueDate],">="&$B$1)

1–30 days overdue:

=SUMIFS(Invoices[AmountOutstanding],Invoices[DaysOverdue],">=1",Invoices[DaysOverdue],"<=30")

31–60 days overdue:

=SUMIFS(Invoices[AmountOutstanding],Invoices[DaysOverdue],">=31",Invoices[DaysOverdue],"<=60")

61–90 days overdue:

=SUMIFS(Invoices[AmountOutstanding],Invoices[DaysOverdue],">=61",Invoices[DaysOverdue],"<=90")

More than 90 days overdue:

=SUMIFS(Invoices[AmountOutstanding],Invoices[DaysOverdue],">90")

Check that the buckets use the same reporting date and that paid invoices contribute zero to AmountOutstanding.

Summarize balances by customer

Create a summary with columns for Customer, Invoice Count, Outstanding, and Overdue. If the customer name is in A2:

Invoice count:

=COUNTIF(Invoices[Customer],A2)

Outstanding balance:

=SUMIFS(Invoices[AmountOutstanding],Invoices[Customer],A2)

Overdue balance:

=SUMIFS(Invoices[AmountOutstanding],Invoices[Customer],A2,Invoices[DaysOverdue],">0")

In newer Microsoft 365 versions, generate a sorted customer list with:

=SORT(UNIQUE(Invoices[Customer]))

Older Excel versions can use a manually maintained list, Remove Duplicates, or a PivotTable.

Add an invoice search box

If a user enters an invoice number in B3, use XLOOKUP to return details:

=XLOOKUP($B$3,Invoices[InvoiceID],Invoices[Customer],"Not found")

To return the outstanding balance:

=XLOOKUP($B$3,Invoices[InvoiceID],Invoices[AmountOutstanding],"Not found")

XLOOKUP and dynamic-array functions are not available in every older Excel edition. If they are unavailable, filter the Invoices table manually or use compatible INDEX/MATCH or VLOOKUP formulas. Check Microsoft’s Excel function documentation for your version.

Use a PivotTable for flexible reporting

A PivotTable can show:

• Outstanding balance by customer.
• Invoices by payment status.
• Collections by month.
• Overdue balances by aging bucket.
• Payments by method.

A useful layout is:

• Rows: Customer.
• Columns: Status or aging bucket.
• Values: Sum of AmountOutstanding.
• Filters: Invoice date, due date, and currency.

Refresh the PivotTable after adding data. It is a reporting layer, not a replacement for the underlying tables.

Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Support on Ko-Fi

How to maintain the tracker

When issuing an invoice

1. Assign a unique invoice number.
2. Add a row to Invoices.
3. Enter the customer, issue date, due date, total, and currency.
4. Confirm the applicable tax and payment terms.
5. Add a file path or hyperlink to the invoice PDF, contract, purchase order, or related document.
6. Confirm that the calculated status is Open.

When receiving a payment

1. Add a new row to Payments.
2. Enter the date and exact amount received.
3. Assign the correct invoice ID.
4. Record the method and bank, check, or processor reference.
5. Confirm that PaidToDate and Balance updated.
6. Investigate an overpayment, short payment, or unmatched payment instead of hiding it.

Never change the original invoice amount to reflect a payment, and do not replace an earlier payment with a new cumulative total. Add a new payment row.

Reconcile regularly

• Compare Excel payment totals with bank or processor records.
• Check for duplicate payment IDs.
• Find payments not linked to an invoice.
• Review negative balances and possible credits.
• Review overdue invoices marked disputed or on hold.
• Save a dated copy or export of important reports.

Handle exceptions correctly

One payment covering multiple invoices

Use one allocation row per invoice in the Payments table and repeat the same bank transaction reference on each row. This is intentional: the table records how one lump-sum transaction was allocated.

For more complex reconciliation, use a separate bank-transactions table and an allocation table. The simpler repeated-reference method is usually sufficient for a small Excel register.

Short payments

Leave the unpaid balance open and record the reason in a notes or collection-status field. A short payment may result from a dispute, retainage, withholding, processing fee, credit memo, or customer error. Do not mark the invoice Paid merely because money was received.

Credit notes, adjustments, and refunds

Do not enter a credit note as a fake payment. Use an adjustment column or a separate Adjustments table:

AdjustmentID
InvoiceID
AdjustmentDate
Type
Amount
Reason

Document the sign convention clearly. For example, if credits reduce the amount due:

InvoiceTotal - Adjustments - Payments

Refunds, reversals, and credits may require accounting treatment beyond a simple tracker.

Taxes and currencies

Where relevant, record subtotal, tax, total, currency, and exchange-rate treatment separately. Do not add invoices in different currencies into one receivables total without stating the conversion method and reporting currency.

Common Excel failures and fixes

Problem	Why it matters	Fix
Duplicate invoice numbers	Payments may be allocated to the wrong record.	Use unique IDs and a COUNTIF duplicate check.
Hard-coded Paid/Open statuses	Status becomes stale after a payment or due date passes.	Calculate payment status from balance and due date.
Overwriting payment history	Installments and reversals disappear.	Add one row per payment.
Fixed ranges	New rows may be excluded from formulas.	Use Excel Tables and structured references.
Hidden overpayments	Customer credits or duplicate payments are concealed.	Show raw balance and a separate credit/overpayment field.
Mixing currencies	Totals become misleading.	Track currency and conversions explicitly.
Dates stored as text	Due-date and aging formulas may fail.	Convert entries to real Excel dates and use a consistent display format.
TODAY() in historical reports	Old reports change over time.	Use a fixed as-of date.
Blanking every error with IFERROR	Data problems become invisible.	Show clear messages such as “Check invoice ID.”
Deleting formula cells	New or edited records stop calculating.	Protect formula columns and check formulas after edits.

Sharing and security

Shared workbooks can create duplicate entries, double-recorded payments, inconsistent notes, and accidental formula deletion. Store the file in a secure cloud location with version history when collaboration is appropriate, restrict editing of formula columns, and assign responsibility for maintaining the register.

Invoice trackers may contain customer names, addresses, bank references, tax identifiers, and commercially sensitive balances. Use appropriate access controls and secure storage. Password protection alone is not a substitute for sound data governance.

When Excel is no longer enough

Excel is reasonable when invoice volume is low to moderate, one person or a small team owns the process, currencies and tax treatments are limited, and the main need is visibility rather than automation.

Consider dedicated software when you need:

• Automated invoice creation and delivery.
• Online payment links.
• Automatic reminders.
• Bank-feed matching.
• Recurring billing.
• Sales-tax or VAT reporting.
• A formal accounts-receivable ledger.
• Granular user permissions and a defensible audit history.
• Inventory, payroll, expense, or general-ledger integration.
• Large transaction volumes, multiple legal entities, or complex currencies.

Excel formulas can compare and summarize data, but they do not by themselves prove that a bank transaction is valid or correctly allocated. Version history is useful, but it is not automatically equivalent to an accounting-system audit trail. Local bookkeeping, tax, invoice-retention, and legal requirements should be assessed separately.

As a general decision guide, stay with Excel for a controlled register; consider an invoicing platform such as FreshBooks when the main problem is client billing and payment collection; consider broader accounting software such as QuickBooks Online when you need bookkeeping, bank feeds, tax reporting, and accountant collaboration. Wave may also be relevant for some microbusinesses, subject to current regional feature availability. Check each provider’s official pricing and feature pages before choosing, since plans and availability change.

Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.