Recommended Free Tools
The most dependable way to create an accounts receivable (AR) aging report in Excel is to place one open receivable per row in an Excel Table, calculate days past due from a fixed report date, assign each balance to an aging bucket, and summarize the results with SUMIFS or a PivotTable.
Use the invoice’s due date—not usually its invoice date—to measure lateness. The finished workbook should also separate credits and disputes, identify missing data, and reconcile to the AR control balance in your accounting system.
What an accounts receivable aging report shows
An AR aging report organizes unpaid customer balances according to how long they have been outstanding or overdue. It is a date-sensitive analysis, not simply a list sorted by invoice date.
- Open invoice: An invoice with a remaining balance.
- Current: Not past its due date.
- Past due: The due date has passed.
- Days past due: The number of days between the due date and the selected report date.
- As-of date: The date used to calculate the report.
Common buckets are Current, 1–30, 31–60, 61–90, and 91+ days past due. These are operating conventions, not universal accounting rules. Your business may use different ranges.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
#1 Best Overall
A 91+ balance is more than 90 days overdue; it is not automatically uncollectible. Collectibility depends on payment history, disputes, credit risk, legal issues, and your accounting policy.
Prepare the invoice data
Start with an export from your accounting or invoicing system. Ideally, the data has one row per invoice or receivable transaction and includes:
| Column | Purpose |
|---|---|
| Customer ID | Stable identifier for grouping customers |
| Customer Name | Human-readable customer name |
| Invoice Number | Transaction reference |
| Invoice Date | Date issued |
| Due Date | Contractual payment deadline |
| Original Amount | Original invoice value |
| Payments/Credits | Amounts applied to the invoice |
| Open Balance | Remaining amount due |
| Currency | Currency of the balance |
| Status | Open, paid, disputed, written off, or on hold |
| Dispute Flag | Whether collection is being challenged |
| Last Payment Date | Useful collection context |
Before building formulas, confirm the following:
- Dates are real Excel dates rather than text. Test a due date with
=ISNUMBER([@[Due Date]]). - Fully paid invoices are removed or assigned a zero report balance.
- Invoice numbers are unique at the level you intend to report.
- Customer names and IDs are standardized.
- Balances use one currency, or currency is included as a grouping field.
- You have decided how to show credit memos, overpayments, unapplied cash, disputed invoices, and written-off balances.
- The source export’s total open balance can be compared with the accounting system’s AR control balance.
Do not age the original invoice amount when partial payments exist. A $10,000 invoice with $9,500 applied should contribute $500 to the report.
Set up the workbook
Separate raw data, settings, calculations, presentation, and controls. A practical workbook can use these sheets:
- Instructions: Purpose, data source, owner, refresh date, aging policy, credit and dispute treatment, and reconciliation expectations.
- Settings: Report date, bucket limits, currency, and source-system balance.
- AR_Data: Imported source records and calculated columns.
- AR_Report: Summary totals, customer analysis, exceptions, and optional charts.
- Reconciliation: Control totals and data-quality checks.
On Settings, enter a fixed report date in B2, such as 8/18/2026. You can name that cell ReportDate by selecting it, clicking the Name Box beside the formula bar, typing ReportDate, and pressing Enter.
A fixed report date makes month-end reports repeatable. TODAY() is useful for a live dashboard, but it causes historical workbooks to change as days pass.
Convert the source range into an Excel Table
- Select any cell in the invoice export.
- Press Ctrl+T on Windows, or choose Insert > Table.
- Confirm that the table has headers.
- On Table Design > Table Name, rename it
tblAR.
Tables expand when new rows are added and allow readable structured references such as tblAR[Due Date]. They are safer than formulas tied to a fixed range such as A2:A500.
Add the aging calculations
Calculate the open balance
If the source does not provide an open balance, inspect how payments and credits are signed. If payments are positive amounts, use:
Free tools Windows power users keep installed
One-click scans. No signup required.
=[@[Original Amount]]-[@[Payments/Credits]]If payments or credits are exported as negative amounts, the formula may instead be:
=[@[Original Amount]]+[@[Payments/Credits]]Test the result against a known invoice before filling the column down.
Calculate days past due
Add a Days Past Due column:
=IF([@[Due Date]]="","",ReportDate-[@[Due Date]])The result is negative when the invoice is not yet due, zero when it is due on the report date, and positive when it is overdue.
Keeping the signed value is usually better than using MAX(0,...), because it distinguishes future-due invoices from overdue ones. If your policy deliberately removes negative values, use:
=IF([@[Due Date]]="","",MAX(0,ReportDate-[@[Due Date]]))Assign an aging bucket
This version treats invoices due today as current:
=IF([@[Open Balance]]=0,"Paid",
IF([@[Due Date]]="","Missing due date",
IF([@[Days Past Due]]<=0,"Current",
IF([@[Days Past Due]]<=30,"1–30",
IF([@[Days Past Due]]<=60,"31–60",
IF([@[Days Past Due]]<=90,"61–90","91+"))))))If your policy places invoices due today in the first overdue bucket, change the <=0 test to <0. Document the choice on the Instructions sheet.
Create an overdue or collection flag
=IF([@[Open Balance]]<=0,"No balance",
IF([@[Dispute Flag]]="Yes","Disputed",
IF([@[Days Past Due]]>90,"Escalate",
IF([@[Days Past Due]]>0,"Follow up","Current"))))This is an operational flag, not a bad-debt calculation. Disputed balances may be overdue but require a different collection action.
Control which balances are aged
If the export contains paid or written-off items, add Balance Used for Aging:
=IF(OR([@[Status]]="Paid",[@[Status]]="Written off"),0,[@[Open Balance]])Do not silently remove disputed invoices. Include them with a flag, or report disputed and collectible balances separately.
Summarize the report with SUMIFS
Create a summary table with the bucket names in cells such as A2:A6. Using the calculated balance column, the bucket totals are:
=SUMIFS(tblAR[Balance Used for Aging],tblAR[Aging Bucket],"Current")
=SUMIFS(tblAR[Balance Used for Aging],tblAR[Aging Bucket],"1–30")
=SUMIFS(tblAR[Balance Used for Aging],tblAR[Aging Bucket],"31–60")
=SUMIFS(tblAR[Balance Used for Aging],tblAR[Aging Bucket],"61–90")
=SUMIFS(tblAR[Balance Used for Aging],tblAR[Aging Bucket],"91+")Total open receivables:
=SUM(tblAR[Balance Used for Aging])Total overdue:
=SUMIFS(tblAR[Balance Used for Aging],tblAR[Days Past Due],">0")Total more than 90 days overdue:
=SUMIFS(tblAR[Balance Used for Aging],tblAR[Days Past Due],">90")In SUMIFS, criteria such as ">90" are text strings. The criteria ranges must align with the summed range.
Build a customer-by-bucket matrix
Set up headings like this:
Customer | Current | 1–30 | 31–60 | 61–90 | 91+ | TotalPut customer names in column A. In B2, enter this formula and copy it across and down:
=SUMIFS(tblAR[Balance Used for Aging],
tblAR[Customer Name],$A2,
tblAR[Aging Bucket],B$1)Customer total:
=SUM(B2:F2)Overdue percentage, assuming the total is in column G:
=IF(G2=0,0,SUM(C2:F2)/G2)Format the result as a percentage. This matrix shows both the customer’s total exposure and the portion already overdue.
Create a PivotTable version
A PivotTable is preferable when users need to filter by customer, salesperson, currency, status, or dispute flag.
- Click any cell in
tblAR. - Choose Insert > PivotTable.
- Select a new worksheet or a location on
AR_Report. - Place Customer Name in Rows.
- Place Aging Bucket in Columns.
- Place Balance Used for Aging in Values.
- Place Salesperson, Currency, or Status in Filters.
- Confirm that the value field uses Sum, not Count, and apply currency formatting.
Use Data > Refresh All after changing the source. PivotTables do not generally update merely because new records were pasted, although using an expanding Excel Table improves the source range. Microsoft explains how to change a value field’s summary function when a PivotTable shows Count instead of Sum in its PivotTable guidance.
You can add a PivotChart for management reporting, but keep the detailed invoice table available for collection follow-up. Microsoft’s documentation also covers PivotTables, PivotCharts, multiple tables, and Data Model analysis.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Automate recurring reports with Power Query
Power Query is useful when the same AR export arrives weekly or monthly. Microsoft describes Power Query as the Excel experience for importing and shaping data, while Power Pivot is used for modeling imported data; see Microsoft’s overview of Power Query and Power Pivot.
- Choose Data > Get Data.
- Select the source, such as From Workbook, From Text/CSV, From Folder, or a database.
- In Power Query Editor, assign correct data types: dates as Date, amounts as Decimal Number or Fixed Decimal Number, and invoice IDs as Text.
- Remove irrelevant rows and columns.
- Filter out fully paid items if appropriate.
- Add a custom column for days past due.
- Add a conditional column for the aging bucket.
- Load the result to a worksheet or the Data Model.
- Use Data > Refresh All for future updates.
An explicit Power Query expression for days past due is:
Duration.Days(Date.From(ReportDate) - Date.From([Due Date]))Example bucket logic:
if [Open Balance] = 0 then "Paid"
else if [Due Date] = null then "Missing due date"
else if [Days Past Due] <= 0 then "Current"
else if [Days Past Due] <= 30 then "1–30"
else if [Days Past Due] <= 60 then "31–60"
else if [Days Past Due] <= 90 then "61–90"
else "91+"Power Query can also combine files from a folder and keep transformations separate from the report layout. If a source header changes, open the query from the Queries & Connections pane, update the affected step, and confirm the output columns before refreshing the PivotTable.
Add visual warnings and controls
Use conditional formatting to make the report actionable. Microsoft documents conditional formatting for ranges, Excel Tables, and supported PivotTable reports in its conditional-formatting guidance.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minute- Current: neutral or green.
- 1–30: pale yellow.
- 31–60: orange.
- 61–90: dark orange.
- 91+: red.
- Disputed: blue or purple.
- Missing due date: strong warning color.
- Negative balance: separate credit color.
For formula-based formatting, select the relevant range, choose Home > Conditional Formatting > New Rule, and use a formula such as:
=$D2="91+"Keep missing due dates visible. A blank due date must not be interpreted as current.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Reconcile the workbook to the accounting system
On a Reconciliation sheet, record the source-system AR balance, Excel’s total, the difference, and data-quality counts.
If the source balance is in Settings!B10:
=Settings!B10-SUM(tblAR[Balance Used for Aging])Status:
=IF(ABS(B12)<0.01,"OK","Investigate")A tolerance may be appropriate for rounding, but its amount should reflect currency precision, translation, and source-system rules.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitchesBest Value
Useful checks include:
=COUNTBLANK(tblAR[Due Date])
=COUNTBLANK(tblAR[Customer ID])
=COUNTIF(tblAR[Balance Used for Aging],"<0")
=COUNTIF(tblAR[Aging Bucket],"Missing due date")To identify repeated invoice numbers:
=SUM(--(COUNTIF(tblAR[Invoice Number],tblAR[Invoice Number])>1))This counts duplicate occurrences, not necessarily the number of distinct invoice numbers that are duplicated. Also determine whether the source is one row per invoice or one row per invoice line. Summing line-level data can be valid, but joining payments or other tables can multiply rows and overstate AR.
Handle important edge cases
Invoice date versus due date
Invoice-date aging answers “How old is the invoice?” Due-date aging answers “How late is payment?” Use due dates for overdue analysis when they are available. If terms vary, import the contractual due date or calculate it from the invoice date and the customer’s agreed terms. Do not assume every customer is Net 30.
Future-dated records
If the report date precedes an invoice or due date, flag the record for review. Do not silently hide impossible or future-dated transactions.
Credits and unapplied cash
Negative balances may represent credit memos, overpayments, unapplied cash, or errors. Do not force them into ordinary overdue buckets. Show credits separately and explain whether unapplied cash is included in the reconciliation. Unapplied cash generally cannot be assigned reliably to a particular invoice.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Disputed and written-off invoices
A disputed invoice can be overdue without being immediately collectible. Keep the dispute flag and consider separate overdue totals for disputed and collectible balances. Written-off items should be excluded only under a documented policy.
Multiple currencies
Do not add USD, EUR, GBP, or other currencies together. Create separate reports by currency, or convert using a documented exchange rate and rate date. The accounting system’s reporting-currency balance may be the appropriate control total.
Troubleshoot common errors
| Problem | Likely cause and fix |
|---|---|
| Aging formula returns an error | Due dates are text or invalid. Convert the source field to real dates and test with ISNUMBER. |
| Totals are too high | Paid invoices, duplicate rows, original amounts, or multiplied line-level joins may be included. |
| Current invoices appear overdue | Check the report date, due-date field, and the policy for invoices due today. |
| PivotTable shows Count | The balance field contains text, blanks, or nonnumeric values. Convert it to a numeric type and select Summarize Values By > Sum. |
| PivotTable is missing new rows | Confirm the source is tblAR, then use Data > Refresh All. |
| Reconciliation does not agree | Check paid items, credits, disputes, write-offs, currency conversion, rounding, source timing, and whether both totals use the same report date. |
Choose the right Excel method
- Formulas: Best for small or moderately sized exports and transparent calculations. They are easy to inspect but more vulnerable to accidental edits and repeated manual cleanup.
- PivotTables: Best for interactive customer, salesperson, currency, and status summaries. They aggregate quickly but require refreshes and correctly typed numeric values.
- Power Query: Best for recurring exports and multiple files. It makes cleaning refreshable, but source-header changes can break transformation steps.
- Power Pivot/Data Model: Best for large datasets or related invoice, payment, customer, calendar, and salesperson tables. It supports reusable measures but has a steeper learning curve and varies by edition and platform. Microsoft documents creating measures in Power Pivot.
When Excel is no longer the right tool
Excel is practical for a small or moderately sized process, especially when the source export is consistent and one person owns the workbook. Consider accounting or dedicated AR software when you need automated payment matching, customer portals, reminders, collections workflows, audit trails, permission controls, many concurrent users, large transaction volumes, multiple entities, or complex multi-currency reporting.
Excel can calculate and display an aging report, but it cannot by itself guarantee that the accounting export is complete, that balances are collectible, or that bad-debt expense is recorded correctly. Those conclusions require accounting policy and reliable source data.
Quick Recap
Final operating checklist
- Verify the source export date and data grain.
- Set and document a fixed report date.
- Confirm due dates are real Excel dates.
- Use remaining balances, not original invoice amounts.
- Review paid, written-off, negative, and duplicate records.
- Separate credits, unapplied cash, and disputes where appropriate.
- Check currency treatment.
- Refresh Power Query and PivotTables.
- Reconcile Excel’s total with the AR control balance.
- Save a dated copy for month-end reporting.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
