How to Create an AR Aging Report in Excel with AI

An accounts receivable (AR) aging report is one of the most practical tools for understanding your business’s cash flow health. It shows who owes you money and, more importantly, how long they’ve owed it. This article breaks down exactly how to build this report in Microsoft Excel and then shows you a newer, much faster method using AI.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is an Accounts Receivable Aging Report?

An AR aging report is a table that categorizes and sums unpaid customer invoices based on how long an invoice has been outstanding. It is an essential snapshot of your financial health, helping you quickly identify which customers are late on payments and which invoices are at risk of becoming bad debt.

Typically, the report groups invoices into time-based buckets, such as:

• Current: Invoices that are not yet due.
• 1-30 Days Past Due: Invoices 1 to 30 days overdue.
• 31-60 Days Past Due: Invoices 31 to 60 days overdue.
• 61-90 Days Past Due: Invoices 61 to 90 days overdue.
• 91+ Days Past Due: Invoices more than 90 days overdue.

Monitoring this report helps you proactively manage collections, make decisions about extending credit, and forecast cash flow with greater accuracy. Without it, you’re flying blind, unable to see which unpaid invoices need your immediate attention.

Gathering Your Data for the Report

Before you can build anything, you need the right raw materials. Your first step is to pull a list of all open invoices from your accounting software (like QuickBooks, Xero, or Stripe Billing) or your company’s ledger. Your export should include a few essential columns of data for each invoice:

• Customer Name: Who owes you the money.
• Invoice Number: A unique identifier for the transaction.
• Invoice Date: The date the invoice was issued.
• Due Date: The date payment is contractually due.
• Amount Due: The outstanding balance on the invoice.

Export this data as a CSV or Excel file and open it as a new spreadsheet. Your raw data should look something like a simple table without any formatting or analysis applied yet.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Traditional Way: Building an AR Aging Report in Excel (Step-by-Step)

Building an AR aging report manually in Excel is a common task, but it requires a handful of formulas and a PivotTable to get right. It’s effective, but as you’ll see, it involves quite a few steps that have to be repeated every time you want an updated report.

Step 1: Set Up Your Excel Spreadsheet

Open your exported data in Excel. The first thing you'll want to do is add new columns to serve as your aging "buckets." Next to your existing data (Customer Name, Invoice Number, Due Date, etc.), add the following headers in separate columns:

• Days Overdue
• Current
• 1-30 Days
• 31-60 Days
• 61-90 Days
• 91+ Days

Your sheet is now structured and ready for some formulas.

Step 2: Calculate the "Days Overdue" for Each Invoice

To categorize invoices, you first need to know how many days past the due date each one is. The TODAY() function in Excel is perfect for this. It returns the current date, which you can then subtract the invoice's due date from.

Assuming your 'Due Date' is in column D and your table starts on the second row, click into the first cell of your “Days Overdue” column (let’s say it's F2) and enter this formula:

=TODAY()-D2

After hitting Enter, a number will appear. This is the difference in days between today and the due date. If the number is negative, it means the invoice isn't due yet. Click the small square at the bottom-right corner of the cell and drag it down to apply this formula to all your invoices.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Write Formulas to Categorize Invoices into Aging Buckets

Now it's time to put each invoice's balance into the correct aging bucket. This requires using an IF statement (or a nested set of them) to check the value in the "Days Overdue" column.

Let’s assume "Amount Due" is in column E and "Days Overdue" is in column F. You’ll write a separate formula for each aging bucket column.

For the "Current" column (cell G2):

This formula checks if the days overdue is less than or equal to 0. If it is, it pastes the invoice amount, otherwise, it leaves the cell blank.

=IF($F2<=0, $E2, "")

For the "1-30 Days" column (cell H2):

This formula checks if the days overdue is between 1 and 30, inclusive.

=IF(AND($F2>0, $F2<=30), $E2, "")

For the "31-60 Days" column (cell I2):

This formula checks if the days overdue is between 31 and 60.

=IF(AND($F2>30, $F2<=60), $E2, "")

For the "61-90 Days" column (cell J2):

This checks for invoices between 61 and 90 days overdue.

=IF(AND($F2>60, $F2<=90), $E2, "")

For the "91+ Days" column (cell K2):

Finally, this checks for invoices more than 90 days overdue.

=IF($F2>90, $E2, "")

Enter each formula in the correct column on the second row, then drag them all down to apply them to every invoice. Your table now has all the data it needs but isn't summarized yet.

Step 4: Summarize the Data with a PivotTable

So far, you’ve categorized each invoice one by one. The final step is to create a summary report that groups totals by customer. This is the perfect job for a PivotTable.

1. Select all the data in your sheet, including the new columns you just created.
2. Go to the Insert tab in Excel and click PivotTable.
3. A dialog box will pop up. Your data range should already be selected, so just click OK to create the PivotTable in a new worksheet.
4. On the right side of the screen, you’ll see the "PivotTable Fields" panel. Now, you’ll build the report:

Instantly, Excel will generate a clean summary table showing each customer's total outstanding balance distributed across the aging categories. You now have a complete Accounts Receivable Aging Report.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Faster Way: Creating an AR Aging Report with AI

The manual Excel method works, but let’s be honest: it’s tedious. You download a CSV, mess with formulas, and build a PivotTable - a process you have to repeat dutifully every week or month. A mistake in a single IF statement can throw off the entire report.

Modern AI-powered analytics tools were built to eliminate this exact type of manual reporting. Instead of executing steps yourself, you simply describe the report you want in plain English.

For example, you could connect your Google Sheet or accounting software and ask: “Create an accounts receivable aging report. Use data from my 'Open Invoices' sheet. Show totals for each customer grouped into Current, 1-30, 31-60, 61-90, and 91+ day buckets.”

Instead of you writing formulas, the AI analyst does the work behind the scenes:

• It understands what an "AR aging report" is.
• It calculates the days overdue for each invoice based on the current date, just like the TODAY() formula.
• It places each invoice amount into the correct bucket automatically.
• It generates a perfectly summarized table and can even visualize it as a chart for you.

The entire process takes about 30 seconds. And because the report is live, you don't have to keep rebuilding it. Best of all, you can ask follow-up questions to dig deeper into the data without having to filter and rearrange another PivotTable:

• "Which five customers have the highest balance in the 91+ days category?"
• "Show me a pie chart of the total receivables broken down by aging bucket."
• "What is the total amount due from 'ABC Corp' that is over 60 days late?"

This conversational approach lowers the barrier to getting insights. You don't need to be an Excel power user anymore, you just need to know what question to ask.

Final Thoughts

Knowing how to build an accounts receivable report in Excel is a valuable skill, but it’s a manual process that consumes time better spent on strategy and operations. While the step-by-step formula and PivotTable method certainly works, modern finance and sales teams need faster, more flexible ways to get answers from their data.

For our own reporting, we developed Graphed to be the conversational AI analyst we always wanted. Connecting a Google Sheet or your core business applications is a one-time setup, after which you can generate complex reports like an AR aging summary simply by describing what you need. This cuts through the manual grunt work, turning hours of spreadsheet wrangling into a brief chat and freeing your team to focus on collecting revenue, not just reporting on it.