How to Create an AR Aging Report in Google Sheets with ChatGPT

Creating an accounts receivable (AR) aging report from scratch can feel daunting, especially if complex spreadsheet formulas aren't your strong suit. Thankfully, you can now use ChatGPT as a personal formula assistant to build a powerful AR aging report right in Google Sheets. This guide will walk you through setting up your data, prompting ChatGPT for the right formulas, and visualizing the final report to get a clear view of your company’s financial health.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is an AR Aging Report and Why is it Important?

An Accounts Receivable (AR) Aging Report is a critical financial tool that categorizes your outstanding customer invoices based on how long they've been unpaid. It's not just a list of who owes you money, it’s a detailed snapshot of your cash flow and the health of your customer accounts.

The report typically sorts outstanding receivables into time-based buckets, such as:

• Current: Invoices within a 0-30 day payment term.
• 31-60 Days: Invoices that are 1 to 30 days past due.
• 61-90 Days: Invoices that are 31 to 60 days past due.
• 91+ Days: Invoices more than 90 days past due.

Running this report regularly helps you quickly identify late-paying customers, anticipate potential cash flow gaps, and decide when it's time to escalate collection efforts. It's an indispensable tool for managing the financial pulse of your business.

Step 1: Set Up Your Invoice Data in Google Sheets

Before you can build the report, you need your raw data organized in a Google Sheet. Your sheet should act as a simple bare-bones ledger of all outstanding invoices. For this report to work, structure your data with the following essential columns:

• Customer Name
• Invoice Number
• Invoice Date
• Invoice Amount
• Outstanding Balance (This is the amount you still need to collect)

Your sheet should look something like this:

Quick Tip: Ensure your Invoice Date column is formatted as a date in Google Sheets (Format > Number > Date). This is crucial for the formulas to work correctly.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 2: Calculate "Days Overdue" for Each Invoice

The foundation of an aging report is knowing how old each invoice is. To do this, we'll add a new column to our sheet called Days Overdue. This column will calculate the number of days between the invoice date and today.

In cell F1, type the header "Days Overdue".

In cell F2, enter the following formula:

=TODAY() - C2

Let's break that down:

• =TODAY(): This is a dynamic function that always returns the current date.
• - C2: This subtracts the date in Invoice Date (cell C2) from today's date.

After you press Enter, click on the small blue square in the bottom-right corner of cell F2 and drag it down to apply the formula to all your invoice rows. Now you'll have the age of every invoice, updating automatically each day.

Step 3: Ask ChatGPT to Generate Your Aging Report Formulas

Now comes the fun part. Instead of wrangling with complex SUMIFS formulas yourself, you'll ask ChatGPT to do the heavy lifting. We'll build a separate summary table that uses the raw invoice data to create our report.

Create the Summary Table Structure

Find an empty space in your sheet (e.g., cell A10) and set up the headers for your aging report. It should look like this:

Under the "Customer Name" header (in cell A11, for example), list the unique customer names from your raw data.

Crafting Your Prompt for ChatGPT

Clear prompting is everything. You need to give ChatGPT context about your data structure and exactly what you want to achieve. Open ChatGPT and use a prompt like this:

"Hi, I'm creating an AR aging report in Google Sheets. My raw data is in a sheet named 'Invoices' from rows 2 to 100. The columns are:

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Implementing ChatGPT's Formulas

ChatGPT will likely return formulas using the SUMIFS function. Here's what it will probably suggest and how to use it:

1. For the "Current (0-30 Days)" bucket (Cell B11):

=SUMIFS(Invoices!$E$2:$E$100, Invoices!$A$2:$A$100, $A11, Invoices!$F$2:$F$100, ">=0", Invoices!$F$2:$F$100, "<=30")

This sums the Outstanding Balance (Column E) only if the Customer Name (Column A) matches the name in cell A11 and the Days Overdue (Column F) is between 0 and 30.

2. For the "31-60 Days" bucket (Cell C11):

=SUMIFS(Invoices!$E$2:$E$100, Invoices!$A$2:$A$100, $A11, Invoices!$F$2:$F$100, ">=31", Invoices!$F$2:$F$100, "<=60")

Same logic, but now the Days Overdue are between 31 and 60.

3. For the "61-90 Days" bucket (Cell D11):

=SUMIFS(Invoices!$E$2:$E$100, Invoices!$A$2:$A$100, $A11, Invoices!$F$2:$F$100, ">=61", Invoices!$F$2:$F$100, "<=90")

4. For the "91+ Days" bucket (Cell E11):

=SUMIFS(Invoices!$E$2:$E$100, Invoices!$A$2:$A$100, $A11, Invoices!$F$2:$F$100, ">=91")

Finally, in your "Total Due" column (cell F11), add a simple SUM formula to total the buckets:

=SUM(B11:E11)

Once you've pasted all these formulas for your first customer, you can drag them down to automatically calculate the values for all other customers listed in your summary table. The absolute references ($) ensure the data range stays constant, while the relative reference ($A11) updates for each row.

Step 4: Visualize Your Data with a Chart

Numbers in a table are great, but a chart can instantly show you who your problem accounts are. A stacked bar chart works perfectly for this.

1. Select your summary data, from the 'Customer Name' header through the '91+ Days' column.
2. Go to Insert > Chart.
3. Google Sheets will often suggest a useful chart type. If not, open the chart editor and select a "Stacked bar chart."

This chart will give you a clear, visual breakdown of each customer's debt, with colors representing the different aging buckets. You'll be able to see at a glance whose outstanding balance is mostly "Current" versus who has a large amount in the "91+ Days" category.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Bonus Tip: Use ChatGPT for Refinements

What if you want to make an adjustment? Just ask ChatGPT! Your conversation is a running context, so you can easily ask for tweaks. For example:

**"That's perfect. Now, can you give me a formula for the bottom row of my summary table to calculate the grand total for each aging bucket column (B, C, D, and E)?"**

ChatGPT will quickly provide simple SUM formulas, saving you even more time and mental energy.

Final Thoughts

By pairing the organizational power of Google Sheets with the formula-generating muscle of ChatGPT, you can build a professional and dynamic AR aging report without being a spreadsheet wizard. This approach transforms a complicated task into a simple, conversational process, empowering you to better manage your business's finances and keep cash flow healthy.

While this method is a massive upgrade from manual tracking, it still requires exporting data and updating spreadsheets. We built Graphed to solve exactly this problem. Instead of downloading CSVs from QuickBooks or Stripe and pasting them into Google Sheets, you can connect your financial apps directly to our platform. From there, you just ask for what you need in plain English - like "create an AR aging report dashboard showing each customer's overdue balance" - and it automatically generates a live, interactive dashboard that stays up-to-date in real time. It's the same conversational approach, but without any of the manual data prep.