How to Build an AR Aging Report in Excel

Why AR Aging Is the Most Important Report You're Probably Not Running

If you extend credit to customers, an AR aging report is your most important financial management tool. More important than your P&L for day-to-day cash management. More actionable than your cash flow statement for identifying immediate problems.

An AR aging report tells you: for every customer, every outstanding invoice, and exactly how many days each invoice has been outstanding.

Here's how to build one that's actually useful — and how to use it to collect faster.

Table of Contents

1. What an AR Aging Report Shows
2. Building the Aging Report in Excel: Step by Step
3. Using the Report Weekly
4. Connecting to Zoho Books
5. Reading the Report Strategically, Not Just Building It
6. FAQ
7. Conclusion

What an AR Aging Report Shows

A standard AR aging report groups outstanding invoices into time buckets:

What to do with each bucket:

• Current: No action yet
• 1–30 days overdue: Friendly reminder
• 31–60 days: Escalated follow-up, confirm payment commitment
• 61–90 days: Senior-level contact, potential service hold discussion
• 90+ days: Formal demand, potential write-off evaluation

Building the Aging Report in Excel: Step by Step

Step 1: Set Up Your Data Table

Create a table with these columns:

• Invoice Number
• Customer Name
• Invoice Date
• Due Date
• Invoice Amount ($)
• Amount Paid ($)
• Balance Outstanding ($)
• Days Overdue (formula: =MAX(0, TODAY()-[Due Date]))

Step 2: Create the Aging Buckets

Add these columns with IF formulas:

Current (not yet due): =IF([Days Overdue]=0, [Balance Outstanding], 0)

1–30 Days Overdue: =IF(AND([Days Overdue]>=1, [Days Overdue]<=30), [Balance Outstanding], 0)

31–60 Days: =IF(AND([Days Overdue]>=31, [Days Overdue]<=60), [Balance Outstanding], 0)

61–90 Days: =IF(AND([Days Overdue]>=61, [Days Overdue]<=90), [Balance Outstanding], 0)

90+ Days: =IF([Days Overdue]>90, [Balance Outstanding], 0)

Step 3: Create the Summary Table

Use SUMIF to aggregate by customer. This gives you one row per customer showing their total AR distribution across aging buckets.

Step 4: Add Conditional Formatting

• 61-90 Days column: Orange background when value > 0
• 90+ Days column: Red background when value > 0
• Add a "Risk Flag" column that says "HIGH" for any customer with 61+ days outstanding

Step 5: Build the Action Column

The most useful addition to a standard aging report: an Action column that auto-populates based on days overdue:

=IF([Max Bucket]="90+", "URGENT: Demand Letter", IF([Max Bucket]="61-90", "Senior Escalation", IF([Max Bucket]="31-60", "Follow Up Call", IF([Max Bucket]="1-30", "Reminder Email", "No Action"))))

This column is your weekly action list.

Using the Report Weekly

The aging report is a weekly tool, not a monthly one:

Every Monday morning:

1. Update invoice status (any payments received since last week)
2. Update Due Date for any invoices where terms were extended
3. Review anything that moved into a new bucket since last week
4. Take action for everyone with an action flag
5. Note responses received in the Comments column

Total time: 15–20 minutes for a business with 20–30 active customers.

Connecting to Zoho Books

If you're on Zoho Books, you don't need to build this from scratch:

1. Go to Receivables → Aging Summary
2. The report is pre-built with customizable date buckets
3. You can export to Excel for further analysis

For advanced analysis (custom filtering, customer segment breakdown), the Excel model above is more flexible.

Reading the Report Strategically, Not Just Building It

A correctly built aging report is only half the value — the other half is reading it for patterns, not just individual overdue invoices:

• Watch bucket migration, not just bucket totals. A customer moving from "current" to "1-30 days" for the first time is a different signal than a customer who's been camped in "31-60 days" for three months. The report shows both, but only a week-over-week comparison reveals which is which.
• Segment by customer, not just in aggregate. A single large customer sliding into 90+ days can distort your total AR picture even if every other customer pays on time. Reviewing the report by customer, not just the summary total, catches this.
• Weight buckets by realistic collection probability, not face value, when estimating expected cash inflow — see the FAQ below for illustrative weighting.
• Common mistake: basing aging on invoice date instead of due date. This makes a Net 60 customer paying on day 45 look identical to a Net 15 customer paying on day 45 in the report, even though one is early and the other is a month late — due-date-based aging is what actually reflects risk.

For the collections process that acts on what this report reveals, see our guide on reducing DSO, and for the full end-to-end process this report sits inside, see our O2C guide.

FAQ

Conclusion

An AR aging report maintained weekly is the single most effective tool for improving collection rates and reducing DSO. If you're not running one today, start with a simple version — even a basic Excel table beats managing AR from memory or email inbox.

If you'd like our Excel AR aging template (pre-built with all formulas and conditional formatting), contact us at hello@financebridge.tech and we'll send it across.