DEV Community

Black
Black

Posted on

Building a printable invoice from a table in Excel and Google Sheets (no FILTER, no macros)

#excel #spreadsheet #freelance #productivity

Most invoice templates are one pretty page you copy for every client. After a few months you have forty files and no idea who still owes you. I built an invoice tracker in a single spreadsheet (Excel + Google Sheets, no macros): one table of invoices, one table of line items, and a printable invoice page that fills itself in from whichever invoice number you pick. Here's how the formulas work.

Due date from each client's terms

Clients have an optional "Terms (days)" column. Blank means the default from Setup. The due date on the Invoices tab:

=D6 + IFERROR(
    IF(INDEX(ClientTerms, MATCH(C6, ClientNames, 0)) = "",
       DefaultTerms,
       INDEX(ClientTerms, MATCH(C6, ClientNames, 0))),
    DefaultTerms)
Enter fullscreen mode Exit fullscreen mode

The IFERROR covers a client name that isn't on the Clients tab yet, so the row never shows an error.

Status that updates itself 

=IF(J6=0, "Draft",
 IF(M6<=0.005, "Paid",
 IF(E6<TODAY(), "Overdue "&(TODAY()-E6)&" days",
 IF(N(K6)>0, "Partial", "Sent"))))
Enter fullscreen mode Exit fullscreen mode

J is the total, K is the amount paid, M is the balance. <=0.005 instead of =0 stops a rounding leftover from keeping an invoice open. Conditional formatting with "begins with Overdue" turns those red.

The printable invoice

This is the part I wanted most: pick an invoice number in one cell and get a finished invoice. The header is easy: find the invoice's row once with MATCH and reuse it.

Row:     =MATCH($D$2, InvNum, 0)
Client:  =INDEX(InvClient, $J$2)
Due:     =INDEX(InvDue, $J$2)
Enter fullscreen mode Exit fullscreen mode

The line items are harder, because one invoice can have any number of lines spread anywhere in the Items table. Without FILTER (older Excel doesn't have it), a helper column on Items marks the lines that belong to the selected invoice with their row number:

=IF(AND($B6<>"", $B6=Invoice!$D$2), ROW(), "")
Enter fullscreen mode Exit fullscreen mode

Then line k on the invoice is the k-th smallest of those row numbers:

Helper:       =IFERROR(SMALL(ItemKey, 1), "")
Description:  =IF($J20="", "", INDEX(Items!$C:$C, $J20))
Amount:       =IF($J20="", "", INDEX(Items!$H:$H, $J20))
Enter fullscreen mode Exit fullscreen mode

SMALL skips the empty text cells, so lines 1, 2, 3 pull the first, second and third matching rows in order, and the rest stay blank. It works the same in Google Sheets.

Aging buckets

A hidden column holds days overdue (0 if paid or not yet due):

=IF(OR($B6="", N(M6)<=0.005), 0, MAX(0, TODAY()-E6))
Enter fullscreen mode Exit fullscreen mode

Then each bucket is one SUMIFS with two conditions on that column:

1-30:   =SUMIFS(InvBal, InvBal, ">0.005", InvOver, ">=1",  InvOver, "<=30")
31-60:  =SUMIFS(InvBal, InvBal, ">0.005", InvOver, ">=31", InvOver, "<=60")
Enter fullscreen mode Exit fullscreen mode

Average days to get paid 

Helper:  =IF(AND(N(M6)<=0.005, L6<>"", N(J6)>0), L6-D6, "")
Average: =IFERROR(ROUND(AVERAGE(InvDays), 0), 0)
Enter fullscreen mode Exit fullscreen mode

Only fully paid invoices with a paid date count. If that number creeps up, it's a sign to tighten your terms or send reminders sooner.

The finished sheet

It also has a dashboard (invoiced vs collected by month, outstanding, oldest overdue invoices, top clients) and per-client totals. It's on Payhip and Gumroad for $9 if you'd rather not build it yourself.

Top comments (0)