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

Tracking who owes you money - and how overdue their payments are - is one of the most important parts of keeping your business's cash flow healthy. A well-organized Accounts Receivable (AR) aging report is the perfect tool for this, but building one can feel like a clunky spreadsheet project. This guide will walk you through creating a powerful AR aging report directly in Google Sheets, covering both the traditional, formula-based method and the MUCH faster approach using modern AI.

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 is it Critical?

An AR aging report is simply a table that categorizes all of your unpaid invoices by the length of time they've been outstanding. It shows you exactly which customers are late on payments and by how much, sorting the data into time-based buckets.

This isn't just an accounting exercise, it's a vital health check for your business. A clear aging report helps you:

• Forecast Cash Flow: By seeing what payments are expected soon versus what's significantly delayed, you get a much more realistic picture of your incoming cash.
• Prioritize Collections: You can immediately spot which customers need a friendly reminder and which high-value, long-overdue invoices require more urgent action.
• Identify At-Risk Customers: A customer who is consistently paying later and later might be facing financial trouble. Your aging report acts as an early warning system.

Typically, AR reports are broken into these standard buckets:

• Current (0-30 days): Invoices that are not yet due or are only slightly overdue.
• 31-60 days: Invoices that are clearly past due and need attention.
• 61-90 days: Invoices that are becoming a concern.
• 91+ days: These are the problem accounts that pose a real risk of becoming bad debt.

Step 1: Get Your Invoice Data Organized

The foundation of any good report is clean, well-structured data. Before you can build anything, you need to have your accounts receivable information laid out logically in a Google Sheet. If you're exporting data from QuickBooks, Stripe, or another invoicing system, it will likely already be in a similar format.

At a minimum, your spreadsheet should have these columns:

• Customer Name: Who the invoice is for.
• Invoice Number: A unique identifier for the specific invoice.
• Invoice Date: The date the invoice was issued.
• Invoice Amount: The total amount due for that invoice.
• Due Date: When the payment is expected.
• Payment Status: A simple status like "Paid" or "Unpaid" to filter out completed transactions.

Your raw data table might look something like this:

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 2: Building the Report With Google Sheets Formulas

Once your data is neatly arranged, it's time to add some formulas to do the heavy lifting. This "traditional" method involves creating new columns to calculate the age of each invoice and then place it into the correct aging bucket.

Calculate 'Days Past Due'

First, we need a column that dynamically calculates how many days have passed since the invoice due date. We can do this with the TODAY() function, which always returns the current date.

1. Add a new column header called "Days Past Due".
2. In the first cell of that column, enter the following formula (assuming your 'Due Date' is in column E and your 'Payment Status' is in column F):

=IF(F2="Unpaid", TODAY()-E2, 0)

This formula checks if the "Payment Status" is "Unpaid." If it is, it subtracts the 'Due Date' from today's date to find the number of days it's overdue. If it's been paid, it simply returns 0. You can then click and drag the small blue square in the corner of the cell to apply this formula to all your rows.

Categorize Invoices into Aging Buckets

Next, we'll create separate columns for each aging bucket and use IF formulas to place the invoice amount in the correct column. This is where it can get a bit tedious.

Create four new columns with the headers: "0-30 Days", "31-60 Days", "61-90 Days", and "91+ Days".

In the first row of your "0-30 Days" column, enter:

=IF(AND(G2>0, G2<=30), D2, 0)

This checks if "Days Past Due" (column G) is between 1 and 30. If so, it displays the 'Invoice Amount' (column D). If not, it displays 0.

You'll then do the same for the other columns with slight modifications:

• For "31-60 Days":

=IF(AND(G2>=31, G2<=60), D2, 0)

• For "61-90 Days":

=IF(AND(G2>=61, G2<=90), D2, 0)

• For "91+ Days":

=IF(G2>90, D2, 0)

After dragging these formulas down for all your unpaid invoices, you'll have successfully categorized every overdue dollar amount into the correct bucket.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Summarize and Visualize Your Data

Now that your data is properly bucketed, the final step is to create a clean summary and visualization that you can understand at a glance.

Using a Pivot Table to Summarize by Customer

A pivot table is the perfect tool for consolidating this information by customer.

1. Select all of your data, including the new formula columns.
2. Go to the main menu and click Insert > Pivot Table. Choose to add it to a new sheet.
3. In the Pivot Table editor on the right:

You’ll instantly see a clean report listing each customer and a summary of their overdue balances across each aging category.

Creating a Chart for a Quick Overview

A chart helps you see the bigger picture instantly. A stacked bar chart works great for visualizing AR aging.

1. Select the data in your newly created pivot table.
2. Go to Insert > Chart.
3. Google Sheets will likely recommend a chart type, but if not, select "Stacked column chart" from the chart editor.

This chart will give you a clear, visual representation of your total outstanding receivables, with colors segmenting each aging bucket. It makes it very easy to see how much of your AR is worryingly overdue.

Graphed

Still Building Reports Manually?

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

Watch the Demo

The Game Changer: Using AI to Create Your AR Report

The manual process above works perfectly fine, but let’s be honest - it’s meticulous, time-consuming, and if you make one mistake in a formula, the whole report can be wrong. You have to remember the formulas, create several new columns, and carefully construct a pivot table every time you want to update it.

The new generation of AI-powered data tools completely changes this experience. Instead of painstakingly building your report, you can simply ask for it using plain English.

Imagine, instead of writing multiple complex formulas, you connect your Google Sheet to an AI analyst and type a prompt like this:

“Show me an accounts receivable aging trend from my sales data, with totals broken down by customer for Current, 31-60 days, 61-90 days, and over 90 days outstanding.”

The AI handles everything else. It interprets your request, automatically calculates the days past due for each invoice behind the scenes, buckets the amounts correctly, and in seconds, produces the exact summary table or chart you need. There are no formulas to write and no new columns to manage.

This conversational approach also makes follow-up analysis instant. You can ask follow-up questions to dig deeper, such as:

• "Which three clients have the highest balance in the 90+ day bucket?"
• "Filter this report to only show invoices over $1,000."
• "Visualize this data as a stacked bar chart."

This eliminates the spreadsheet-wrangling and allows you to immediately get to the insights.

Best Practices for Managing Your Accounts Receivable

Regardless of how you build your report, it’s crucial to actively use it. Here are a few quick tips:

• Review Regularly: Set a schedule to review your AR aging report consistently. This helps you to quickly identify any risk.
• Automate Communication: Connect your AR aging report with automated reminders or invoices to keep customers aware of their due payments.
• Clear and Concise Invoices: Ensure your invoices are easy to understand and include important details like due dates and payment terms.
• Act Quickly on Overdue Accounts: Don't let overdue accounts pile up. Start action when an account enters the 31+ day bucket.

Final Thoughts

Maintaining a clear accounts receivable aging report is essential for managing your cash flow and identifying potential problems. While Google Sheets offers tools to build one manually with formulas, AI transforms this task from a spreadsheet puzzle into a simple conversation, giving you actionable insights in minutes.

You can now update and manage datasets more efficiently, take action when needed, and automate processes where applicable. Graphed provides a seamless way to connect your Google Sheets account, allowing you to visualize data and quickly gain insights into your accounts receivable. Their interactive reports and dashboards show overdue accounts and customer trends, giving you the tools to manage collections effectively.