How to Create an AR Aging Report in Excel with ChatGPT

Manually tracking outstanding invoices can feel like a full-time job, especially when you're trying to figure out who owes you what and for how long. An Accounts Receivable (AR) aging report is the classic solution, but building one in Excel often means wrestling with complicated formulas. This guide will walk you through creating a powerful AR aging report from scratch, using ChatGPT to write the formulas for you and speed up the entire process.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is an AR Aging Report (and Why You Need One)?

An AR aging report is a simple but essential financial tool that categorizes your outstanding customer invoices by the length of time they've been unpaid. It helps you see, at a glance, which accounts are current and which are past due.

Why is this so important? Because it directly impacts your cash flow. By monitoring how "old" your receivables are, you can:

• Prioritize Collections: Immediately identify which clients need a friendly reminder. Invoices outstanding for over 90 days are a red flag and require urgent attention.
• Manage Cash Flow: Forecast your incoming cash more accurately. If you see a large chunk of your receivables creeping into the 60-90 day category, you know you need to step up collection efforts to meet your own financial obligations.
• Spot Payment Trends: Notice if a specific customer is consistently paying late. This might influence you to change their payment terms or hold off on future work until their balance is cleared.

Typically, AR reports are broken into "buckets" like this:

• Current: 0-30 days past due
• Past Due: 31-60 days
• Past Due: 61-90 days
• Past Due: 91+ days

The goal is to keep as much of your money in the "Current" bucket as possible. Let's build a report that shows you exactly where you stand.

Getting Your Data Ready for Analysis

The saying "garbage in, garbage out" is especially true here. Before you can build your report, you need to organize your invoice data into a clean, simple table in Excel. A messy spreadsheet will only lead to frustration and incorrect results.

Create a worksheet with the following essential columns. Your accounting software likely exports a report like this, but make sure it includes these fields:

• Invoice Number: A unique ID for each invoice.
• Customer Name: The name of the client.
• Invoice Date: The date the invoice was sent.
• Due Date: The date the payment is due.
• Invoice Amount: The total amount of the invoice.
• Amount Paid: How much of the invoice has been paid so far. (This will often be zero for unpaid invoices).

For this tutorial, we will add one more column that you will calculate:

• Outstanding Balance: The remaining balance to be paid. This is a simple formula: = [Invoice Amount] - [Amount Paid]

Your data should look something like this, formatted as an Excel Table (you can do this by selecting your data and pressing Ctrl + T). Using tables makes formulas much easier to manage.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Build the Report, Step-by-Step

Now that your data is clean and organized, we can start building the aging report. This is where we'll lean on ChatGPT to do the heavy lifting with formulas.

Step 1: Calculate "Days Past Due"

First, we need to know how many days have passed since each invoice's due date. We can create a new column called "Days Past Due" for this.

This is a perfect first task for ChatGPT. Why search for vague formula syntax when you can just ask for exactly what you need? Open ChatGPT and use a prompt like this:

I have an invoice due date in cell D2 of my Excel table. I need a formula to calculate how many days past due it is compared to today's date. If the invoice is not yet due, the result should be 0.

ChatGPT will likely give you a formula using the TODAY() and IF() functions. The TODAY() function always returns the current date, ensuring your report stays up-to-date automatically.

Here's a common formula it might suggest:

=IF((TODAY()-[@[Due Date]])>0, TODAY()-[@[Due Date]], 0)

Let's break that down:

• TODAY()-[@[Due Date]] calculates the difference in days between today and the due date.
• IF(... > 0, ..., 0) checks if that difference is greater than zero (meaning it's past due). If it is, it shows the number of days. If not, it shows 0.

Add a new column to your table called "Days Past Due" and paste this formula into the first cell. If you're using an Excel Table, it should automatically fill down for all your rows.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 2: Create the Aging Buckets with a ChatGPT Formula

Now comes the tricky part - sorting each invoice's outstanding balance into the correct aging bucket (0-30 days, 31-60 days, etc.). Manually writing nested IF statements for this is tedious and prone to error. Let's have ChatGPT build a much cleaner formula for us.

We'll create four new columns: "0-30 Days", "31-60 Days", "61-90 Days", and "91+ Days". We want to place the outstanding balance in the one column that matches its aging period.

Use a specific prompt in ChatGPT for each column. For the first bucket:

In my Excel table, I have the "Days Past Due" in the `[@[Days Past Due]]` column and the "Outstanding Balance" in the `[@[Outstanding Balance]]` column. I need a formula for a new column called "0-30 Days".

If "Days Past Due" is a number between 1 and 30 (inclusive), the formula should return the "Outstanding Balance". Otherwise, it should return 0.

ChatGPT will generate a formula like this:

=IF(AND([@[Days Past Due]]>=1, [@[Days Past Due]]<=30), [@[Outstanding Balance]], 0)

Repeat this process for the other three aging buckets by slightly modifying your prompt:

• For "31-60 Days": "...a number between 31 and 60..."
• For "61-90 Days": "...a number between 61 and 90..."
• For "91+ Days": "...a number greater than 90."

After adding these four columns and their formulas, your table will now show each outstanding amount neatly sorted into its proper aging category.

Step 3: Summarize Your Data with a PivotTable

Your table is now fully prepared, but it's still just a long list of invoices. To get a high-level view, we need a summary. The fastest and most powerful way to do this in Excel is with a PivotTable.

If you're not familiar with them, don't worry. PivotTables do the hard work of aggregating and summarizing your data for you, without writing a single extra formula.

Here’s how to create one:

1. Click anywhere inside your data table.
2. Go to the Insert tab on the Excel ribbon and click PivotTable.
3. A dialog box will appear. Your entire table should already be selected as the data source. Just click OK to create the PivotTable in a new worksheet.
4. You'll now see a blank PivotTable and a "PivotTable Fields" panel on the right. This is where the magic happens.

Now, simply drag and drop the fields like this:

• Drag Customer Name to the Rows area.
• Drag the four aging bucket fields ("0-30 Days", "31-60 Days", "61-90 Days", "91+ Days") to the Values area.
• You can also drag Outstanding Balance to the Values area to get a grand total for each customer.

Instantly, you have a clean, summarized report showing the total amount each customer owes, broken down by how overdue their payments are.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 4: Visualize the Report with a Chart

Numbers are great, but a visual chart makes it immediately obvious who your problem accounts are. A stacked bar chart is perfect for this.

Not sure which chart is best? You can ask ChatGPT for advice. Here's a quick prompt:

I have a PivotTable where rows are customer names and columns are AR aging buckets (0-30, 31-60, 61-90 days). What's the best chart to visualize which customers owe the most money and how overdue that money is?

ChatGPT will likely recommend a stacked column or bar chart, because it excels at showing both individual parts and the total sum.

To create the chart:

1. Click on any cell inside your finished PivotTable.
2. Go to the Insert tab and find the Charts section.
3. Select a Stacked Column or Stacked Bar chart.

Excel will instantly generate a chart visualizing your PivotTable data. Each bar will represent a customer's total outstanding balance, and the colored segments within the bar will show how much of that balance falls into each aging bucket. Now you can see in seconds which customer has the largest amount of debt in the "91+ Days" category.

Final Thoughts

Creating an AR aging report in Excel is a fundamental skill for managing your business's financial clarity and your processes for managing finances proactively. By leveraging a tool like ChatGPT to generate formulas and offer suggestions, you can get through the technical hurdles much faster and focus more on what the data is telling you about your collections process.

While using AI to speed up Excel work is a huge step up from manual formula writing, you may still be spending time exporting data and keeping reports up to date. We've built Graphed to remove that friction entirely. Instead of building cell by cell, imagine securely connecting your accounting software once, then simply asking in plain English something like, "Show me our top overdue customer accounts with invoices aging over 90 days." Graphed instantly builds a real-time interactive dashboard to help you get the insights quickly.