Get the template: click the button to save your own copy of the ready-made sheet to your Google Drive. You can also build the same sheet in about 10 minutes with the steps below, and every formula is on this page.
What the template looks like
The template has two tabs. Invoices holds the raw invoice list, one row per invoice. AR Aging holds the report: one row per customer with an open balance, and one column per aging bucket.
| Customer | Current | 1-30 | 31-60 | 61-90 | 90+ | Total |
|---|---|---|---|---|---|---|
| Amy's Bird Sanctuary | $239.00 | $0.00 | $0.00 | $0.00 | $0.00 | $239.00 |
| Freeman Sporting Goods | $0.00 | $562.50 | $0.00 | $0.00 | $0.00 | $562.50 |
| Jeff's Jalopies | $0.00 | $0.00 | $0.00 | $81.00 | $0.00 | $81.00 |
The sample rows use names from the QuickBooks sample company. "Current" means the invoice is not yet past its due date; the other buckets count days past the due date.
Step 1: the Invoices tab
Create a tab named Invoices with these headers in row 1, in this order:
| Column | Header | What goes in it |
|---|---|---|
| A | Invoice Number | The QuickBooks invoice number |
| B | Date | Invoice date |
| C | Customer | Customer display name |
| D | Total Amount | Invoice total |
| E | Balance | Amount still unpaid (0 when paid) |
| F | Due Date | Due date, as a real date |
These are the default invoice columns SheetsSync writes, so a synced tab drops straight in. If you paste an export instead, make sure the Date and Due Date columns are real dates (right-aligned in the cell), not text.
Step 2: the AR Aging tab and formulas
Create a second tab named AR Aging. Put these headers in row 1: Customer, Current, '1-30, '31-60, '61-90, 90+, Total.
Type the three middle bucket headers with a leading apostrophe, exactly as shown ('1-30, '31-60, '61-90). Without it, Google Sheets turns 1-30 into a date. The apostrophe keeps the header as plain text and does not show in the cell.
A2, the customer list. One formula lists every customer with an open balance, sorted A to Z, and grows as new customers appear:
=SORT(UNIQUE(FILTER(Invoices!C2:C, Invoices!E2:E>0)))B2 to F2, the buckets. Enter these in row 2 and fill them down as far as the customer list goes:
Current B2: =SUMIFS(Invoices!$E:$E, Invoices!$C:$C, $A2, Invoices!$F:$F, ">="&TODAY())
1-30 C2: =SUMIFS(Invoices!$E:$E, Invoices!$C:$C, $A2, Invoices!$F:$F, "<"&TODAY(), Invoices!$F:$F, ">="&TODAY()-30)
31-60 D2: =SUMIFS(Invoices!$E:$E, Invoices!$C:$C, $A2, Invoices!$F:$F, "<"&TODAY()-30, Invoices!$F:$F, ">="&TODAY()-60)
61-90 E2: =SUMIFS(Invoices!$E:$E, Invoices!$C:$C, $A2, Invoices!$F:$F, "<"&TODAY()-60, Invoices!$F:$F, ">="&TODAY()-90)
90+ F2: =SUMIFS(Invoices!$E:$E, Invoices!$C:$C, $A2, Invoices!$F:$F, "<"&TODAY()-90)
Total G2: =SUM(B2:F2)Grand totals. Because the customer list grows, don't put grand totals under the table. Don't put them in row 1 of columns A to G either: row 1 holds the headers, so a total there would land on a header cell. Put them in a separate area beside the table instead, for example columns I:J, with a label in I and the total in J:
I1: Current J1: =SUM(B2:B)
I2: '1-30 J2: =SUM(C2:C)
I3: '31-60 J3: =SUM(D2:D)
I4: '61-90 J4: =SUM(E2:E)
I5: 90+ J5: =SUM(F2:F)
I6: Total J6: =SUM(G2:G)Format columns B to G as currency. The report recalculates every day on its own, because TODAY() moves the bucket edges.
Check your numbers: the Total column summed over all customers should equal the sum of the Balance column on the Invoices tab. If it doesn't, some due dates are probably stored as text.
Step 3: fill it from QuickBooks
By hand (any tool)
In QuickBooks Online, run an invoice list, export it to Excel, and paste the six columns into the Invoices tab. Repeat whenever you want a fresh aging report.
Automatically with SheetsSync
- Install SheetsSync and connect QuickBooks Online from Extensions > SheetsSync > Open SheetsSync.
- Go to the Invoices tab and open Configure This Sheet. Choose Invoices and keep the default fields (Invoice Number, Date, Customer, Total Amount, Balance, Due Date).
- Set the date range to All Time. The default is Last 30 Days, which would leave out older unpaid invoices, and those are the ones an aging report is for.
- Click Sync. The AR Aging tab fills in.
Invoices is one data type, so this works on the Free plan. On Pro you can set the Invoices tab to sync on a schedule (hourly, every 6 hours, daily or weekly), and the aging report stays current with no clicks. Keep the AR Aging tab separate from the synced tab, because each sync rewrites the synced tab.
Useful extras
- Highlight late customers: select F2:F and add a conditional formatting rule "Greater than 0" with a red fill.
- Days overdue per invoice: on the Invoices tab, add
=IF(E2>0, MAX(0, TODAY()-F2), "")in a new column to see how late each open invoice is. Put it to the right of the synced columns. - Share without a QuickBooks seat: share the sheet (or just the AR Aging tab as a published range) with whoever chases payments.
- More invoice reporting: the QuickBooks invoices to Google Sheets guide covers filters and balance-by-customer formulas, and the dashboard guide turns the same data into charts.