How to build an accounts receivable aging report in Excel

Your accounting software exports open invoices. Your owner wants to know who owes what and how late. Here's how to turn the export into an aging report with buckets, totals by customer and the worst accounts in red, step by step.

Updated October 21, 2026 · 8-minute read

Start with the right columns

Export your open invoices (unpaid or partly paid) to Excel or CSV. You need one row per invoice with at least:

Customer
Invoice number
Invoice date and/or Due date
Open balance (what's still owed, not the original amount)

Get rid of anything that isn't an invoice row: report titles, blank rows, and customer subtotal lines. If your system only gives you a PDF, see how to convert a PDF report to Excel first. Then select the data and press Ctrl+T to make it a table, which keeps formulas and the PivotTable up to date as rows change.

Decide the "as of" date and what you age by

Put the date you're aging to in a cell above the table, for example B1, and label it "As of". Use a fixed date (the month end) rather than TODAY() if the report has to match the books for a period.

Then decide what lateness means for you:

Days past due (from the due date) shows who has broken their terms. Most collection work uses this.
Days since invoice (from the invoice date) shows the age of the sale, regardless of terms. Some lenders and owners ask for this.

Step 1: days past due

Add a column called Days late. With the due date in column D and the as-of date in B1:

=MAX(0, $B$1 - [@[Due date]])

In a plain range instead of a table, the same thing is =MAX(0, $B$1 - D4). MAX(0, …) turns not-yet-due invoices into 0 instead of a negative number. If your dates came through as text (they won't subtract), select the column and use Data > Text to Columns > Finish to convert them, or check the date format in the export.

Step 2: the aging bucket

Add a column called Bucket. In Excel 2019 or Microsoft 365:

=IFS([@[Days late]]=0, "Current",
     [@[Days late]]<=30, "1-30",
     [@[Days late]]<=60, "31-60",
     [@[Days late]]<=90, "61-90",
     TRUE, "Over 90")

In any version of Excel, LOOKUP does the same in one line:

=LOOKUP([@[Days late]], {0,1,31,61,91}, {"Current","1-30","31-60","61-90","Over 90"})

To change the buckets (for example 1-15, 16-30), edit the two lists; the first list holds the lowest number of days in each bucket.

Step 3: totals by customer with a PivotTable

Click inside the table and choose Insert > PivotTable, on a new sheet.
Drag Customer to Rows, Bucket to Columns, and Open balance to Values (it shows as Sum of Open balance).
The buckets may sort alphabetically ("1-30" before "Current"). Drag the column headings into order: Current, 1-30, 31-60, 61-90, Over 90.
Sort customers by the Grand Total column, largest first, so the biggest balances are at the top.

When you paste in next month's export, use PivotTable Analyze > Refresh.

Step 4: highlight the accounts to call

Select the Days late column in the detail table.
Choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than, enter 90 and pick a red fill.
For the whole row, use New Rule > Use a formula with =$E4>90 (adjust the column and first row) and apply it to the table.

A summary at the top helps too: total open, total over 90 days, and the over-90 share of the total, using SUMIFS on the Bucket column.

=SUMIFS(Table1[Open balance], Table1[Bucket], "Over 90")

Doing it every month

Once it's built, the monthly routine is: export, paste over the old data (keeping the table), update the as-of date, refresh the PivotTable. The fragile part is the export itself: if the columns move, an extra subtotal line appears, or the report only comes as a PDF, the cleanup starts again.

Wheelbarrow handles that part. Drop in the aging or open-invoice report exactly as your software exports it (PDF, Excel or CSV) and describe what you want, for example "buckets by days late, a tab per bucket, red over 90 days, totals by customer on the summary". You see it built from your own export before you pay, and the tool you keep rebuilds the workbook from next month's file in seconds, even offline. See accounting reports for more examples.

Rebuilding the aging report every month? See it built from your own export first. Try it with my files

Questions

What are the standard AR aging buckets?

The most common are Current, 1-30, 31-60, 61-90 and over 90 days past due. Some businesses add 91-120 and over 120, or use shorter buckets like 1-15 and 16-30 for fast-paying customers.

Should I age invoices by due date or invoice date?

Due date shows who has broken their payment terms, which is what most collection work uses. Invoice date shows how old the sale is regardless of terms; some lenders and owners ask for that view. Use one consistently and label the report.

What formula puts invoices into aging buckets in Excel?

Calculate days late with =MAX(0, AsOfDate - DueDate), then bucket it with IFS (Excel 2019 and Microsoft 365) or LOOKUP with two lists, for example =LOOKUP(days, {0,1,31,61,91}, {"Current","1-30","31-60","61-90","Over 90"}).

How do I show totals by customer for each bucket?

Insert a PivotTable with Customer in Rows, Bucket in Columns and the open balance in Values, then drag the bucket columns into order and sort by the grand total.