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. FAQ
6. 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.

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.