To build a receivables ageing report in Excel, calculate how many days each unpaid invoice is past its due date, then add up the outstanding amounts for each customer in buckets such as 0-30, 31-60 and 61-90 days. SUMIFS does the adding, and a PivotTable can do the same job with no formulas for the buckets.
What columns does the invoice list need?
- A: customer
- B: invoice number
- C: invoice date
- D: due date
- E: invoice amount
- F: amount received so far
- G: outstanding = E2-F2
- H: days overdue = $R$1-D2
Put the as-of date in R1. Type a fixed date such as the last day of the month when you are producing a month-end report, or use =TODAY() for a live view. A fixed date keeps last month's ageing from changing when you reopen the file. A negative number in H simply means the invoice is not yet due.
Which SUMIFS formulas build the buckets?
List each customer once in column J. Then in the bucket columns, filled across and down:
- Not yet due (K): =SUMIFS($G$2:$G$300, $A$2:$A$300, $J2, $H$2:$H$300, "<0")
- 0-30 days (L): =SUMIFS($G$2:$G$300, $A$2:$A$300, $J2, $H$2:$H$300, ">=0", $H$2:$H$300, "<=30")
- 31-60 days (M): =SUMIFS($G$2:$G$300, $A$2:$A$300, $J2, $H$2:$H$300, ">=31", $H$2:$H$300, "<=60")
- 61-90 days (N): =SUMIFS($G$2:$G$300, $A$2:$A$300, $J2, $H$2:$H$300, ">=61", $H$2:$H$300, "<=90")
- Over 90 days (O): =SUMIFS($G$2:$G$300, $A$2:$A$300, $J2, $H$2:$H$300, ">90")
Each condition pair says which days-overdue range to include. Add a total column that sums the five buckets, and compare it with =SUMIFS($G$2:$G$300, $A$2:$A$300, $J2). If they differ, an invoice has fallen between your boundaries, which usually means a blank or text date.
How does it look on a few invoices?
Take an as-of date of 30 November and four made-up unpaid invoices for different customers:
| Invoice | Due date | Days overdue | Outstanding | Bucket |
|---|---|---|---|---|
| INV-401 | 10 Nov | 20 | 40,000 | 0-30 |
| INV-402 | 5 Oct | 56 | 25,000 | 31-60 |
| INV-403 | 15 Sep | 76 | 18,000 | 61-90 |
| INV-404 | 15 Dec | -15 | 30,000 | Not yet due |
The days are counted from the due date: 5 October to 30 November is 26 days left in October after the 5th plus 30 in November, which is 56. The grand total outstanding is 40,000 + 25,000 + 18,000 + 30,000 = ₹1,13,000, and only ₹83,000 of it is actually overdue.
Is there a PivotTable alternative?
Yes, and it needs only one helper formula. Add a bucket label in column I:
=IF(H2<0, "Not yet due", IF(H2<=30, "0-30", IF(H2<=60, "31-60", IF(H2<=90, "61-90", "Over 90"))))
Then select the list, choose Insert, PivotTable, put Customer in Rows, Bucket in Columns and Outstanding in Values as Sum. Refresh the pivot after changing the as-of date. The labels may sort alphabetically, so drag the columns into the order you want.
What do you do with the numbers?
Read the report from the right. Anything in 61-90 or over 90 deserves a personal call, not another automated reminder. The 31-60 bucket is where a polite email still works. If one customer holds most of the overdue total, look at the account before you look at the formulas.
Before you share the report
- Check that no outstanding amount is negative, which would be an overpayment or a credit note recorded wrongly.
- Make sure the totals agree with your ledger's receivables balance.
- Write the as-of date at the top.
- Remove paid invoices, or keep them with an outstanding of 0 so they drop out of the buckets.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


