)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
Finance and collections teams can build a columnar AR aging report with customer names in rows and each aging bucket in its own column by importing QuickBooks AR data into Google Sheets or Excel using Coefficient’s QuickBooks connector and applying bucket formulas on top. QuickBooks AR aging reports use fixed vertical layouts. There is no way to restructure the native report so that Current, 1-30, 31-60, 61-90 and over-90 balances appear as separate columns per customer row, which is the format most collections teams and CFOs actually want to work from.
A common challenge for finance teams presenting AR to leadership or the board: the QuickBooks format requires significant manual restructuring before it can be used in a management report or shared with a collections team for prioritisation.
How to build a columnar AR aging report from QuickBooks data
Step 1. Import AR Aging Detail data using From QuickBooks Report
Open Coefficient in Google Sheets or Excel and select Import from QuickBooks. Choose From QuickBooks Report and select A/R Aging Detail. This pulls individual invoice-level data including customer name, due date and outstanding balance for every open invoice. You can also use From Objects and Fields on the Invoice object if you need additional fields not included in the aging report, such as the sales rep or payment terms.
Step 2. Calculate days overdue and assign each invoice to a bucket
Add a formula column calculating days overdue as today’s date minus the due date. Add a second column assigning each invoice to a bucket using nested IF logic: zero to 30 days maps to Current, 31 to 60 maps to bucket one, 61 to 90 to bucket two, 91 to 120 to bucket three and anything over 120 to the final bucket. This gives you the bucket assignment on every row that your summary formulas will reference.
Step 3. Build the columnar summary table with one row per customer
Create a summary table with Customer Name in column A and one column per aging bucket across columns B through F. Use SUMIFS in each bucket column to sum the balance for that customer and that bucket assignment. Add a Total Outstanding column summing across all buckets. This produces the exact horizontal layout that QuickBooks cannot generate natively.
Step 4. Apply conditional formatting and schedule daily refresh
Apply red conditional formatting to the over-90 column and amber to 61-90 for any customer with a non-zero balance in those buckets. Set a daily refresh in Coefficient so aging calculations update automatically as invoices age and payments are received. Enable Formula Auto Fill Down so the bucket formulas extend automatically when new invoices appear in the import.
What you get
Collections teams start every day with a current columnar aging view that shows each customer’s exposure across every bucket without any manual restructuring. Overdue accounts stand out through conditional formatting. Leadership and board presentations use the same live data rather than a manually reformatted snapshot from last week.
Start building your columnar AR aging report today at coefficient.io/get-started.
