An invoice tracker shows which invoices you have sent, which have been paid and which are late. For freelancers and small businesses, it is one of the most valuable spreadsheets, because late payments hurt cash flow. You can build one in Excel in under an hour with a table, a status drop-down, a few date formulas, conditional formatting for overdue invoices and a summary block with totals. This guide walks through each step with a small example that you can check by hand, shows the formulas for due date, days overdue and balance, explains how to total by client and by status, and gives tips for making the file reliable enough to hand to a client.
Updated November 2026
In this guide
- What an invoice tracker should show
- Step 1: build the table
- Step 2: the due date formula
- Step 3: balance and status
- Step 4: days overdue
- Step 5: highlight overdue invoices
- Step 6: totals by status and by client
- Add an aging report
- Make it easy to use
- Reminders and next steps
- Worked example: the sample tracker on 18 November
- Quick recap
- Practice problems
- Common mistakes
What an invoice tracker should show
At a minimum, the tracker should answer five questions: who owes you, how much, when it was due, whether it is paid, and how late it is. Add columns for the invoice number, the client, the invoice date, the payment terms in days, the due date, the amount, the amount paid, the balance and the status. Keep one row per invoice. Do not mix invoices and payments in the same list unless you use a separate payments table, because partial payments are easier to follow with a separate amount paid column.
Step 1: build the table
Enter these headings in row 1: Invoice no, Client, Invoice date, Terms days, Due date, Amount, Paid, Balance, Status. Then add sample rows, such as the ones below.
| Invoice no | Client | Invoice date | Terms | Amount | Paid |
|---|---|---|---|---|---|
| INV-001 | Acme | 1 Oct 2026 | 30 | 1,200 | 1,200 |
| INV-002 | Beta Co | 10 Oct 2026 | 15 | 800 | 0 |
| INV-003 | Acme | 20 Oct 2026 | 30 | 1,500 | 500 |
| INV-004 | Gamma | 5 Nov 2026 | 30 | 950 | 0 |
Convert the range to an Excel table with Insert, Table, so that formulas fill down automatically when you add a new row.
Step 2: the due date formula
The due date is the invoice date plus the terms in days. In the Due date column, use =C2+D2. For INV-001, 1 October plus 30 days is 31 October 2026. For INV-002, 10 October plus 15 days is 25 October 2026. For INV-003, 20 October plus 30 days is 19 November 2026. For INV-004, 5 November plus 30 days is 5 December 2026. Format the column as a date. Excel stores dates as numbers, so adding days works directly. For month-based terms, use =EDATE(C2,1) to add a month. If you need to count the days between two dates, our DATEDIF guide shows more options.
Step 3: balance and status
The balance is the amount minus the amount paid. In the Balance column, use =F2-G2. INV-001 has a balance of 0, INV-002 has 800, INV-003 has 1,000, and INV-004 has 950. You can calculate the status automatically with =IF(H2=0,"Paid",IF(TODAY()>E2,"Overdue","Open")). If the balance is zero, the status is Paid. If the due date has passed and money is still owed, it is Overdue. Otherwise it is Open. See our IF statements guide for nested IF logic. Some people prefer a manual drop-down for the status, as covered in our drop-down list guide, for values such as Draft, Sent, Paid and Disputed. Use a formula when you want it to be automatic, and a drop-down when you need more choices.
Step 4: days overdue
Add a column named Days overdue with =IF(H2>0,MAX(0,TODAY()-E2),0). If today is 18 November 2026, INV-002 due on 25 October is 24 days overdue, since 25 October to 18 November is 24 days: 6 days left in October plus 18 days in November. INV-003 is due on 19 November, so it is not yet overdue, and shows 0. INV-004 is also not due until 5 December. INV-001 is paid, so it shows 0. The MAX function prevents negative numbers for invoices that are not yet due. Use TODAY for a live tracker, or a fixed report date in a cell if you want a snapshot for a specific day.
Step 5: highlight overdue invoices
Select the whole table, go to Home, Conditional Formatting, New Rule, and use a formula: =$I2="Overdue", where column I holds the status. Choose a light red fill. You can add a second rule for Paid with a light green fill, and a third for invoices due within 7 days with a yellow fill, using =AND($H2>0,$E2-TODAY()<=7,$E2>=TODAY()). The dollar sign before the column letter makes the whole row change color. Colors make the tracker useful at a glance, and you can filter by status to see only what needs action.
Step 6: totals by status and by client
Add a Summary area above or beside the table. For the total outstanding, use =SUM(Table1[Balance]) or =SUM(H2:H100), which gives 2,750 for the sample: 800 plus 1,000 plus 950. For the total overdue, use =SUMIFS(H2:H100,I2:I100,"Overdue"), which returns 800 for INV-002 on 18 November. For a client total, use =SUMIFS(H2:H100,B2:B100,"Acme"), which returns 1,000. For the number of unpaid invoices, use =COUNTIFS(H2:H100,">0"), which returns 3. Our SUMIFS guide and COUNTIFS guide explain these functions. Put the summary in a block at the top so you see the key numbers as soon as you open the file.
Add an aging report
An aging report groups unpaid invoices by how late they are. Create buckets for Current, 1 to 30 days, 31 to 60 days and over 60 days. In a helper column, assign the bucket with =IF(H2=0,"Paid",IF(J2=0,"Current",IF(J2<=30,"1-30",IF(J2<=60,"31-60","60+")))), where J holds the days overdue. Then use SUMIFS on the bucket column to total the balance of each bucket. For the sample on 18 November, INV-002 is in the 1-30 bucket with 800, and INV-003 and INV-004 are current with 1,000 and 950. Aging reports help you decide whom to remind first and how serious the situation is.
Make it easy to use
Freeze the header row, as in our freeze panes guide, and add filter buttons so you can show only overdue or only one client. Use data validation for the client names, to avoid spelling variants that break totals. Protect the formula columns, and leave the input columns unlocked. Format money columns with a thousands separator and two decimals, and dates with a clear format such as 01-Nov-2026. Add a notes column for reminders, such as the date of the last email. Keep a backup copy, and save the file under a clear name.
Reminders and next steps
The tracker tells you what to chase, but you still have to act. A good routine is to open the file every Monday, filter for Overdue, and send reminders. Many freelancers send a friendly reminder on the due date, a firmer message after 7 days and a final notice after 30 days. Record the date you sent each reminder in the notes column. If you build the tracker for a client, add a short instruction sheet that explains how to enter a new invoice, how to record a payment, and what each color means.
Worked example: the sample tracker on 18 November
Put the four sample invoices in the table and set the report date to 18 November 2026. INV-001 is paid, with a balance of 0. INV-002 is overdue by 24 days with a balance of 800. INV-003 is open with a balance of 1,000 and falls due tomorrow, so the yellow rule highlights it. INV-004 is open with a balance of 950 and is due on 5 December. The summary shows 2,750 outstanding, 800 overdue and 3 unpaid invoices. The Acme total is 1,000, the Beta Co total is 800 and the Gamma total is 950. If you now enter a payment of 800 against INV-002, the balance becomes 0, the status changes to Paid, the days overdue drop to 0, the red color disappears and the overdue total becomes 0. Seeing the whole chain update from one entered number is the best test that the tracker works, and it is a good demonstration to show a client who is deciding whether to buy a template.
Quick recap
Use one row per invoice, calculate the due date from the invoice date and terms, subtract payments to get the balance, derive the status and days overdue with IF and TODAY, and colour the overdue rows. Summarize outstanding, overdue and client totals with SUMIFS, add an aging view, freeze the headings and protect the formulas.
Practice problems
Try these. Problem 1: an invoice dated 1 March with 30-day terms is due 31 March. Problem 2: an invoice of 2,000 with 750 paid has a balance of 1,250. Problem 3: if today is 10 November and the due date was 1 November, the days overdue are 9. Problem 4: build the summary for the sample data and check that the outstanding total is 2,750. Problem 5: add a new invoice of 600 for Beta Co and confirm that the Beta Co total becomes 1,400. Check that the table, the formulas and the colors update.
Common mistakes
The most common mistake is to type dates as text, so the due date formula fails. Another is to overwrite a formula by accident, which breaks the status. A third is to use different spellings of a client name, so the totals are wrong. A fourth is to forget to record partial payments. A fifth is to rely on color alone, without a status column that can be filtered. Test your tracker with a paid invoice, a partly paid invoice, an overdue invoice and a future invoice before you start using it.
Frequently asked questions
How do I make an invoice tracker in Excel?
Create a table with invoice number, client, dates, amount, paid and status, then add formulas for due date, balance and days overdue.
How do I calculate the due date?
Add the payment terms in days to the invoice date.
How do I flag overdue invoices?
Use an IF formula with TODAY and conditional formatting.
How do I total unpaid invoices?
Use SUM on the balance column, or SUMIFS for a client or status.
Can the status update automatically?
Yes. Use an IF formula based on the balance and the due date.

