How to How to Create Accounts Receivable Aging Report in Excel in Excel - Practical Guide

Learn to create a professional Accounts Receivable Aging Report in Excel to track customer payment status by age brackets (current, 30/60/90+ days overdue). This essential financial tool helps identify slow-paying customers, manage cash flow, and prioritize collection efforts. You'll organize invoice data, calculate aging periods, and format results for executive reporting.

Why This Matters

This report is critical for cash flow management and credit control decisions in any business. It directly impacts financial forecasting and collection strategy effectiveness.

Prerequisites

•Basic Excel skills: data entry, formulas, and formatting
•Customer invoice data with invoice dates and amounts due
•Understanding of aging categories (Current, 30, 60, 90+ days)

Step-by-Step Instructions

Set Up Your Data Structure

Open a new Excel workbook and create column headers: Customer Name, Invoice Date, Invoice Amount, Days Outstanding (blank for now), and Aging Category. Enter your customer invoice data starting in row 2.

Calculate Days Outstanding

In the 'Days Outstanding' column (e.g., column E), enter formula =TODAY()-D2 to calculate days between invoice date and today. Copy this formula down for all invoices using Ctrl+C, then select range and Ctrl+V.

Create Aging Categories Using IF Formula

In the 'Aging Category' column, use formula =IF(E2<=0,"Current",IF(E2<=30,"30 Days",IF(E2<=60,"60 Days","90+ Days"))) to categorize by age brackets. Copy formula down to all rows.

Build Summary Table

Create a new section below your data with headers: Aging Category, Total Amount, Percentage of Total. Use SUMIF function: =SUMIF($F$2:$F$100,"Current",$C$2:$C$100) to sum amounts by category. Calculate percentages with =B8/SUM($B$8:$B$11)*100.

Format for Professional Presentation

Apply number formatting to currency columns (Home > Number Format > Currency). Use conditional formatting (Home > Conditional Formatting > Color Scales) to highlight aging categories, and add borders via Home > Borders > All Borders.

Alternative Methods

Using Pivot Tables for Dynamic Reporting

Create a Pivot Table (Insert > Pivot Table) to automatically group and sum aging data by category, which updates dynamically when you refresh source data.

VBA Macro Automation

Write a VBA macro (Tools > Macros > Visual Basic Editor) to automate the entire aging report calculation and formatting for recurring monthly reports.

Power Query Data Import

Use Power Query (Data > Get & Transform Data > From Database) to import live customer invoice data directly from your accounting system.

Tips & Tricks

✓Use absolute references ($) for summary table formulas so they don't change when copying across rows or columns.
✓Update your report monthly by simply changing the source invoice data; all formulas recalculate automatically with TODAY().
✓Add a 'Contact Status' column to track collection activity (Contacted, Awaiting Payment, In Dispute) alongside aging data.
✓Create a dashboard chart (Insert > Column Chart) to visualize aging distribution and quickly spot collection priorities.

Pro Tips

★Use DAYS() function instead of TODAY()-Date for cleaner, more readable date difference calculations.
★Create separate aging brackets (Current, 1-30, 31-60, 61-90, 90+) for more granular analysis of collection risk.
★Sort your detail data by 'Days Outstanding' (Data > Sort > Descending) to instantly see the oldest unpaid invoices at the top.
★Link your Accounts Receivable balance to your General Ledger for audit trail verification.

Troubleshooting

Formula shows #VALUE! error in Days Outstanding column

Check that your Invoice Date column contains actual dates, not text. Convert text dates to real dates using Data > Text to Columns or the DATEVALUE() function.

SUMIF totals don't match your General Ledger balance

Verify that all outstanding invoices are included in your data range and check for duplicate invoice entries. Cross-reference invoice numbers against your accounting system.

Aging categories show all invoices as '90+ Days' incorrectly

Check your TODAY() function is active (ensure Automatic Calculation is on via Formulas > Calculation Options > Automatic). Recalculate with F9 or Ctrl+Shift+F9.

Percentage column shows decimals instead of percentages

Select the percentage cells and format as Percentage via Home > Number Format > Percentage, then adjust decimal places.

Related Excel Formulas

SUMIF IF TODAY DAYS DATEDIF VLOOKUP COUNTIF AVERAGE

Frequently Asked Questions

How often should I update my Accounts Receivable Aging Report?

Update monthly at minimum to stay on top of collection activities. Many companies update weekly or daily for large customer bases. The TODAY() function in your formulas automatically updates ages, so you only need to refresh your source invoice data.

What aging brackets are standard for AR aging reports?

Most businesses use: Current (0-30 days), 30+ Days, 60+ Days, and 90+ Days overdue. Some add 120+ Days for severely aged accounts. Your choice depends on your industry's typical payment terms and collection policies.

Should I include invoices that customers have disputed or placed on hold?

Yes, include them in your aging report but add a separate 'Dispute Status' column to flag these accounts. This prevents skewing your true outstanding balance and helps identify collection bottlenecks.

How can I track which invoices are paid after the aging report date?

Add a 'Payment Date' column and 'Paid Amount' column to your detail data. Use conditional formatting to highlight paid invoices, and filter to show only unpaid invoices in your aging analysis.

Can I automate this report to email stakeholders weekly?

Yes, use VBA macro with Outlook integration (Tools > Macros > Visual Basic) or save as PDF and use Windows Task Scheduler to email reports automatically. Alternatively, use Power Automate to trigger email notifications.