DEV Community

Ashish Srivastava
Ashish Srivastava

Posted on Originally published at ashishexcelnotes.hashnode.dev AI-assisted

Freelancer invoice tracker in Excel: track clients, projects and who still owes you

#excel #freelancing #productivity #tutorial

Most freelancers start with invoices scattered across email, a notes app and a bank statement. That works until you have five clients and can't say who has paid. A simple freelancer invoice tracker in Excel fixes that: one list of clients, one list of projects, one log of invoices, and a summary that shows what's outstanding. This guide builds one step by step.

Note: this covers spreadsheet layout and formulas only. How you charge tax, what an invoice must show and how you report income depend on where you live. It is not tax or legal advice.

Step 1: A Clients sheet

Make a sheet called Clients with these columns:

Col Field
A Client name
B Contact person
C Email
D Payment terms (days), e.g. 15 or 30
E Currency
F Notes

Select the range and press Ctrl+T to make it an Excel Table, then name it tblClients under Table Design > Table Name. Tables grow on their own, so new clients appear in dropdowns without editing ranges.

Keep one row per client. If the same client appears as "Acme" and "Acme Pvt Ltd", your totals will split, so pick one spelling and stick to it.

Step 2: A Projects sheet

On a Projects sheet:

Col Field
A Project ID, e.g. P-001
B Client (dropdown)
C Project name
D Billing type: Fixed or Hourly
E Rate or fixed fee
F Start date
G Status: Active, Paused, Done

For the Client dropdown, select column B and use Data > Data Validation > List with the source =INDIRECT("tblClients[Client name]"). Do the same for Status with a typed list: Active,Paused,Done.

Make this a Table too (tblProjects).

Step 3: An optional Time Log

If you bill by the hour, add a TimeLog sheet: A = date, B = project ID, C = hours, D = description. Then on the Projects sheet add column H, Hours logged:

=SUMIFS(tblTime[Hours],tblTime[Project ID],[@[Project ID]])
Enter fullscreen mode Exit fullscreen mode

And column I, Billable so far:

=IF([@[Billing type]]="Hourly",[@Hours logged]*[@Rate],[@Rate])
Enter fullscreen mode Exit fullscreen mode

Now each hourly project shows what you could invoice today.

Step 4: The Invoices log

This is the core of the tracker. Make an Invoices sheet:

Col Field
A Invoice no.
B Invoice date
C Client (dropdown)
D Project ID
E Amount
F Due date
G Amount paid
H Paid date
I Balance
J Status
K Days overdue

Fill the due date from the client's payment terms:

F2: =IF(B2="","",B2+IFERROR(INDEX(tblClients[Payment terms (days)],MATCH(C2,tblClients[Client name],0)),30))
Enter fullscreen mode Exit fullscreen mode

If the client isn't found, it falls back to 30 days.

Balance:

I2: =IF(E2="","",E2-N(G2))
Enter fullscreen mode Exit fullscreen mode

Status:

J2: =IF(E2="","",IF(I2<=0,"Paid",IF(TODAY()>F2,"Overdue",IF(N(G2)>0,"Part-paid","Open"))))
Enter fullscreen mode Exit fullscreen mode

Days overdue:

K2: =IF(J2="Overdue",TODAY()-F2,"")
Enter fullscreen mode Exit fullscreen mode

Turn it into a Table (tblInvoices) so these formulas fill down automatically for every new row.

Step 5: Highlight what needs chasing

Select the Status column and add Home > Conditional Formatting > Highlight Cells Rules > Text that Contains:

  • Overdue → light red fill
  • Part-paid → light yellow fill
  • Paid → light green fill

For a stronger signal, select the whole table and add a formula rule =$K2>30 with bold red text. Anything more than a month late jumps out.

Step 6: A one-glance Summary sheet

Make a Summary sheet with a client list in column A (you can type it, or in Excel 365/2021 use =SORT(UNIQUE(tblInvoices[Client]))). Then:

B2 (Invoiced):     =SUMIFS(tblInvoices[Amount],tblInvoices[Client],A2)
C2 (Received):     =SUMIFS(tblInvoices[Amount paid],tblInvoices[Client],A2)
D2 (Outstanding):  =B2-C2
E2 (Overdue):      =SUMIFS(tblInvoices[Balance],tblInvoices[Client],A2,tblInvoices[Status],"Overdue")
Enter fullscreen mode Exit fullscreen mode

Above the table, add three headline numbers:

Total outstanding:  =SUM(tblInvoices[Balance])
Overdue invoices:   =COUNTIF(tblInvoices[Status],"Overdue")
Paid this month:    =SUMIFS(tblInvoices[Amount paid],tblInvoices[Paid date],">="&EOMONTH(TODAY(),-1)+1,tblInvoices[Paid date],"<="&EOMONTH(TODAY(),0))
Enter fullscreen mode Exit fullscreen mode

UNIQUE and SORT need Excel 365 or Excel 2021. The other formulas work in older desktop versions too.

Step 7: A weekly 10-minute routine

The tracker only helps if it stays current. A simple habit:

  1. Every time you send an invoice, add a row to Invoices straight away.
  2. Every time money lands, fill in Amount paid and Paid date.
  3. Once a week, filter Status to Overdue and send a polite reminder to each client on the list.
  4. Once a month, check the Summary sheet for clients who are slow to pay, and consider shorter terms or an advance for their next project.

Common mistakes

  • Typing client names by hand. Use the dropdown so the SUMIFS totals match.
  • Overwriting formulas. Lock the Balance, Status and Days overdue columns with Review > Protect Sheet, leaving the input columns unlocked.
  • Mixing currencies in one total. If you bill in more than one currency, add a converted-amount column or keep a separate summary per currency.
  • Deleting paid invoices. Keep them. Your paid history is what tells you which clients are reliable.

Wrapping up

With four sheets (Clients, Projects, Invoices and Summary) you get a freelancer invoice tracker that shows who owes you, how late they are, and what came in this month, without any paid software. Start with the Invoices log alone if that's all you need, and add the rest as your client list grows.

This article was drafted with AI tools. It's general spreadsheet guidance for Excel, not tax or legal advice.

Top comments (0)