Home / Writing

Excel Invoice Tracker Template: The 5 Columns That Matter

Five columns decide whether the tracker works

It takes about twenty minutes to build an Excel invoice tracker template. Deciding what one row represents is the slow part. A sheet with tidy colors and a missing due date cannot tell you who is late, so the design question comes first: which fields carry the reporting, and which are decoration. Five columns do the work, and everything else can be derived or added later.

The test is simple. If you can answer "who owes me money, and how long has it been owed" from the columns alone, the template is sound. If you cannot, no amount of formatting fixes it.

The five key columns in the Excel invoice tracker template

ColumnWhat it holdsWhat breaks without it
Invoice numberunique, sequential, never reusedfollow-ups get vague; duplicate payments hide
Clientone consistent spelling per clientper-client totals and statements are wrong
Issue datethe day the invoice was sentno aging clock, no DSO
Due datethe actual date from the termsoverdue cannot be calculated
Amounta plain number, one currency per columntotals and sorting break

Column 1: invoice number

Use a format you can scan: 2026-014, not "Acme March". The number needs to be unique across your whole history, because clients reference it in emails and payment systems match on it. Never reuse a number for a corrected invoice; issue 2026-014R instead. Avoid spaces and long client names in the number itself.

Column 2: client

Pick one spelling per client and stick to it: "Acme Studio" forever, not "Acme", "Acme Studio Inc", and "acme studio" in three rows. This single column is what makes per-client totals possible, and those totals are the input to client profitability calculations later. If you need the billing contact, put it in the next column rather than inside this one, or filters stop working cleanly.

Column 3: issue date

This is the date the invoice left your desk, which starts the aging clock. It is not the last day of the month in which you did the work. Enter a real date value, not text like "early March". Text dates are the most common reason a tracker stops calculating.

Column 4: due date

Store the actual due date rather than computing it with a formula from the issue date. Terms vary: net 30 for one client, net 15 for another, a payment plan for a third. A fixed date also survives a change in terms without rewriting formulas. If you do want a default, format the issue date column and add 30 days in a separate helper cell, then paste the result as a value.

Column 5: amount

Enter 1200, not $1,200. Cell formatting displays the currency; text typing breaks sums, sorting, and every formula that follows. If you invoice in more than one currency, add a currency column and keep one amount column rather than mixing symbols inside it.

The two columns you add next

The five columns hold the facts. Two more turn the sheet into a system:

  • Paid date. Blank until the invoice is paid. It is the only field that closes a row, and its presence makes every status label derivable.
  • Payment notes. "Partial, $400 on 3/12" or "promised Friday" is enough. This is where you record the conversations that later become a collections decision.

A status column is tempting, but it lies as soon as you stop updating it. Backlog, overdue, and paid can all be computed from the due date, the paid date, and today's date. Store facts, derive labels.

Formulas that do the reporting

Assume invoice number in A, client in B, issue date in C, due date in D, amount in E, paid date in F, with data starting in row 2.

  • Days overdue: =IF(F2="",TODAY()-D2,""). A positive number is the number of days late; a blank row is either paid or not yet due.
  • Aging bucket: =IF(F2<>"","paid",IF(TODAY()-D2<=0,"current",IF(TODAY()-D2<=30,"1-30",IF(TODAY()-D2<=60,"31-60","60+")))).
  • Client total: =SUMIFS(E:E,B:B,"Acme Studio",F:F,"") for everything still open.

None of this is advanced. What matters is that the formulas read from dates and amounts rather than from text you typed by hand.

What the sheet should tell you monthly

At the end of each month, filter to open rows and sort by due date. The output is a short list of decisions:

  • Current and 1–30 days late: send the reminder or the follow-up call. This is routine admin, not a crisis.
  • 31–60 days late: escalate to a firm email with the invoice attached and a specific payment date requested.
  • 60+ days late: stop delivering new work until there is a payment plan, and consider whether the client stays.

That same filter feeds the invoice aging report, which groups the balance by bucket and shows whether the problem is one client or your whole book. Track the average over a few months and you also have your DSO for freelancers, the number of days it takes, on average, to get paid.

Keep the file small

One sheet, one header row, filters on, first row frozen, and no merged cells. Color coding is fine for emphasis as long as the data does not depend on it: a red cell is not a status, a paid date is. Save a dated copy at each quarter end if you want a trail, but work in one live file. Multiple versions of a tracker is how two invoices end up counted twice.

Do this today

  • Build the header row with the five columns, and format the amount column as a number.
  • Enter every open invoice from the last three months, with issue date, due date, and amount.
  • Add the days-overdue formula, sort descending, and call the two oldest clients today.

Free tool: runway calculator or invoice aging calculator — no signup.