If you send invoices and track payments in a spreadsheet, you know how quickly things get messy. An invoice tracker in Google Sheets keeps everything in one place: what's been sent, what's been paid, what's overdue, and what's still outstanding. This guide shows you how to build one that actually works.
Your Invoice Log
One row per invoice. Columns: invoice number, client name, invoice date, due date, amount, amount paid, status (Draft, Sent, Partial, Paid, Overdue), and notes.
Use data validation on the Status column to enforce consistent labels. Add a helper column for Outstanding Balance:
=E2-F2
Where E2 is the invoice amount and F2 is the amount paid. This auto-calculates what's still owed.
Flagging Overdue Invoices
Add an Overdue column that checks whether an unpaid invoice is past its due date:
=IF(AND(D2<TODAY(), G2<>"Paid"), "OVERDUE", "")
Where D2 is the due date and G2 is the status. Apply conditional formatting to highlight OVERDUE rows in red. Now your overdue invoices stand out immediately.
Days Outstanding
Add a Days Outstanding column:
=IF(G2="Paid", "", TODAY()-C2)
Where C2 is the invoice date. This tells you how long each open invoice has been sitting. Sort by this column to see your oldest unpaid invoices at the top.
Summary Dashboard
Add a summary section at the top of the sheet:
- Total invoiced this month: use SUMIFS filtered by invoice date and current month
- Total collected: SUMIF where status is Paid
- Outstanding: total invoiced minus total collected
- Overdue: SUMIFS where due date is before today and status is not Paid
These four numbers give you your AR position at a glance.
Aging Buckets
Break outstanding invoices into aging buckets — 0-30 days, 31-60 days, 60-90 days, 90+ days. Use SUMIFS to calculate the outstanding balance in each bucket based on days outstanding. This is what your accountant wants to see at month end.
The Easy Way: Using SheetXAI in Google Sheets
Example 1: You have invoice data already in the sheet.
"I have an invoice log on Sheet 1 with columns for client, invoice date, due date, amount, and amount paid. Build a tracker with outstanding balance, overdue flags, days outstanding, and a summary showing total invoiced, collected, and overdue this month."
SheetXAI reads your data, adds the calculated columns, applies the conditional formatting, and builds the summary section.
Example 2: Your invoices live in your billing or accounting system.
"Pull outstanding invoices from QuickBooks and build an AR tracker showing what's paid, what's outstanding, and what's overdue, with aging buckets."
SheetXAI connects to your system and builds the full accounts receivable tracker.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Profit and Loss Report in Google Sheets, How to Create a Monthly Expense Report in Google Sheets, and Google Sheets AI Guide.
