How to Track Overdue Invoices and Payments in Excel

To track overdue invoices in Excel, keep payments in a table of their own, and let formulas work out each invoice’s paid amount, balance, status and days late. Add up each invoice’s payments with =SUMIF($K$2:$K$50,A2,$L$2:$L$50), and count the days late only while a balance is left: =IF(AND(F2>0,$R$1>D2),$R$1-D2,""), with today’s date in R1.

A payment is typed once, in the Payments table, and everything else follows. The six steps below build it, then add the total you’re owed, the overdue total, and red for the late rows. The example has six invoices, and today, in the example, is September 29, 2026.

The tables

Invoices, in columns A to H. You type A to D; E to H are the formulas in Steps 1 to 4, shown here with their results.

Payments, in columns J to N, one row per payment:

Step 1: Add up each invoice’s payments

Step 2: Work out the balance

Step 3: Let the status follow the money

Step 4: Count the days late

Put today’s date in R1 with =TODAY(), so every row counts from the same day.

An invoice counts as late only while money is still owed and its due date has passed. On September 29, 2026, Riverside Brewing is 55 days late, Gulfstream Logistics 4 and Coastal Print Works 16. Harborlight’s invoice is due that day, so it isn’t late yet.

Step 5: Add up what you’re owed

For red on the late rows, select A2:H7, choose Home, Conditional Formatting, New Rule, Use a formula to determine which cells to format, type the rule below, and pick a red fill.

More on rules like this: how to highlight overdue items automatically.

Step 6: Record the rest, and watch it drop off

When Sunrise Dental pays the other $416.10, add one row to Payments: 09/29/2026, INV-200002, 416.10, Check. Nothing else is typed. INV-200002 now shows Paid $716.10, a balance of $0.00 and the status Paid; its Days late cell goes blank, and the row is no longer red. Outstanding drops to $10,144.47, the late invoices to 3, and the overdue total to $8,455.57.

Common questions

Should I record payments on the invoice, or in a table of their own?

In a table of their own, one row per payment. An invoice can be paid in parts, and a Paid column on the invoice holds only one number. A Payments table keeps every check with its date and reference, and SUMIF adds them up.

What’s the difference between outstanding and overdue?

Outstanding is everything still owed. Overdue is the part whose due date has passed. In the example, $10,560.57 is outstanding and $8,871.67 of it is overdue. Harborlight’s $1,688.90 is owed but due today, so it isn’t late yet.

How do I count only working days late?

Swap the subtraction for NETWORKDAYS: =IF(AND(F2>0,$R$1>D2),NETWORKDAYS(D2+1,$R$1),""). It counts Monday to Friday after the due date, up to today: 23 working days for Sunrise Dental, where Step 4 counts 33 calendar days. Add your holidays as a range in a third argument. More in business days between two dates.