← All guides

BUSINESS GUIDES

Invoice Tracker in Excel: Columns, Formulas and Example

September 18, 2026

An invoice tracker in Excel helps you see what you billed, what customers paid, and what remains outstanding. For a small business with a manageable number of invoices, a clear tracker can support follow-ups without relying on memory or searching through email.

Use one sheet for invoices and another for confirmed payments. This keeps several payments against one invoice from turning into several duplicate invoice rows.

Create the Invoices sheet

Put these headers in row 1, using exactly the column order below if you want to copy the formulas. Store invoice references as text, dates as real Excel dates, and amounts as numbers.

ColumnHeaderPurpose
AInvoice numberUnique invoice reference
BCustomerBilling customer
CInvoice dateDate issued
DDue dateAgreed payment deadline
ECurrencySuch as USD or GBP
FInvoice totalFull amount billed, including applicable tax
GCredit notesPositive total of valid credits applied
HPayments receivedCalculated from payment records
IBalanceAmount still outstanding
JDays overdueCalculated only for a positive open balance
KStatusOpen, overdue, settled, or overpaid
LFollow-up noteAction, dispute, or promised payment date

Create the Payments sheet

Use A for invoice number, B for payment date, C for currency, D for amount allocated, and E for the bank or payment-provider reference. Enter one row for each confirmed payment allocation.

If a customer sends $900 covering three invoices, split it into the correct three allocations and repeat the bank reference. The allocated amounts must still total $900. If you do not yet know the allocation, investigate before marking individual invoices paid.

Add the formulas

In Invoices!H2, total payments matching both the invoice reference and currency:

=IF(A2="","",SUMIFS(Payments!$D$2:$D$10000,Payments!$A$2:$A$10000,A2,Payments!$C$2:$C$10000,E2))

In I2, calculate the remaining balance:

=IF(A2="","",ROUND(F2-G2-H2,2))

In J2, calculate days overdue only where the due date is populated and an amount remains payable:

=IF(OR(A2="",D2="",I2<=0),"",MAX(0,TODAY()-D2))

In K2, assign a simple status:

=IF(A2="","",IF(I2<0,"Overpaid",IF(I2=0,"Settled",IF(D2="","Missing due date",IF(TODAY()>D2,"Overdue","Open")))))

Copy the formulas down. The formulas use English Excel function names and commas; some regional settings use semicolons. The two-decimal rounding assumes a currency with two minor-unit decimals. Adapt it for currencies with different conventions. Extend the payment ranges if you exceed row 10000.

Check a worked example

Invoice INV-041 totals $1,000. A valid $100 credit note is applied, and two payments of $250 and $150 have been received in the invoice currency. The tracker calculates $400 received and a balance of $1,000 − $100 − $400 = $500.

If the due date is 10 October and today's date is 20 October, that positive balance is 10 days overdue. A future-due invoice has zero days overdue. A zero balance is marked settled, whether cleared by payments, credits, or a combination.

Avoid common tracking errors

Do not enter a quotation as an issued invoice. Do not record a bank payment twice because it appears in both an email and a statement. Record a credit note as an adjustment, not as money received.

Keep currencies separate in summaries. Adding USD 500 to GBP 400 does not produce a meaningful single-currency balance. For foreign-currency settlements, retain the original bank amount and the supported allocation in invoice currency.

The formulas do not replace a full accounting system. Refunds, cancellations, disputed invoices, and write-offs need controlled records and may require additional columns or software. Preserve notes and supporting documents rather than forcing every situation into “paid” or “unpaid.”

Review the tracker regularly

Reconcile new payments, check missing due dates, filter positive overdue balances, and record the next action. Protect formula cells from accidental changes and keep a backup. Add a link to each saved invoice if it helps you retrieve supporting documents.

Frequently asked questions

Can I use one row for every payment?

Use payment rows on the Payments sheet. Keep one invoice row on the Invoices sheet so the billed amount is counted only once.

Why does an invoice show as overpaid?

Payments and credits exceed its total. Check for duplicate entries, wrong allocations, or a genuine customer overpayment before taking action.

Can the same invoice number appear for two customers?

This layout assumes invoice numbers are unique across your business. If they are not, the matching logic needs another unique key.

Formula reference: Microsoft's SUMIFS documentation explains how multiple criteria restrict the amounts included in a sum.

Put this guide into practice

Ready to prepare your invoice? Start with Invoice Quote PDF, enter your agreed details, and review the document before sending it.

Comments

Post a Comment

Popular posts from this blog

What Information Should an Invoice Include?

Image
A professional invoice does not need to be complicated, but it does need the right information. If an invoice is missing a due date, customer details, tax information, or payment instructions, the customer may need to contact you before they can make payment. Whether you are a freelancer, contractor, consultant, retailer, supplier, or small business owner, your invoice should clearly answer a few basic questions: Who is sending the invoice? Who is being billed? What is being charged? How much is due? When should it be paid? And how can the customer pay? Once those questions are answered clearly, creating an invoice becomes much easier. You do not need unnecessary sections or a complicated design. You simply need accurate information presented in a way the customer can understand. Quick answer: A typical invoice should include your business details, customer details, invoice number,...
Read more

Social Media Management Invoice Template and Monthly Example

A social media management invoice should show the service period, the accounts covered and the deliverables included. “Monthly social media fee” may be enough for a familiar client, but a more specific description helps when a new approver or finance team checks the bill. The most important distinction is between your management fee and money spent on advertising. They are different charges and should not be blended into a single unexplained total. Use the agreement to decide which costs you invoice and which the client pays directly. Start with the monthly scope Billing period, such as 1–31 October, and the client project reference. Platforms covered and the number or type of deliverables. Whether scheduling, community management and reporting are included. Any separately approved content production or urgent work. Payment due date, invoice number, currency and billing contact. For example, “October management: 12 feed posts, four short videos, scheduling and one performance report ac...
Read more

Invoice vs Quotation: What Is the Difference?

Image
Invoices and quotations are two of the most common documents used by businesses, freelancers, contractors, consultants, and service providers. They often contain similar information, so it is easy to confuse one with the other. However, an invoice and a quotation have very different purposes. The simplest way to understand the difference is this: a quotation normally tells a customer what something is expected to cost before the sale is completed, while an invoice asks the customer to pay for a confirmed sale, product, or service. Knowing when to use each document makes communication with customers clearer. It can also help you organize your records, reduce disagreements about prices, and make your business look more professional. In this guide, we will explain what an invoice is, what a quotation is, how they are different, what information they normally contain, and when you should use each one. ...
Read more