How to Create an AR Aging Report in Google Sheets

Struggling to track which customers have paid and which invoices are alarmingly overdue? Building an Accounts Receivable (AR) Aging Report is the answer, giving you a clear snapshot of your company's cash flow and financial health. This guide will walk you through creating a dynamic, automated AR Aging Report from scratch using only Google Sheets.

What is an AR Aging Report and Why Is It So Important?

An AR Aging Report is a simple table that categorizes all of your unpaid customer invoices based on how long they've been outstanding. Instead of just seeing a single "Accounts Receivable" number, you see it broken into time-based buckets, typically:

• Current: Invoices that are not yet due.

• 1-30 Days Past Due: Invoices overdue by one to 30 days.

• 31-60 Days Past Due: Invoices overdue by 31 to 60 days.

• 61-90 Days Past Due: Invoices overdue by 61 to 90 days.

• 91+ Days Past Due: Invoices overdue by more than 90 days.

This simple categorization is incredibly powerful. It helps you instantly see where your cash is tied up, which customers are slow to pay, and which debts are at risk of becoming uncollectible. Maintaining this report is one of the most effective ways to manage your cash flow, prioritize collection efforts, and make informed financial decisions without getting overwhelmed by raw data.

Step 1: Set Up Your Invoice Data Tab

Before any formulas can work their magic, you need a clean, organized source of data. The best practice is to have one dedicated tab in your Google Sheet that acts as a master list for all your invoices. Let's call this tab "Invoices".

Every report is only as good as the data it’s built on. For your AR aging report to be accurate, your "Invoices" tab needs to include these essential columns:

• Customer Name: The name of the client or company you billed.

• Invoice Number: A unique identifier for each invoice.

• Invoice Date: The date the invoice was issued.

• Due Date: The date payment is due.

• Invoice Amount: The total amount of the invoice.

• Amount Paid: How much the customer has paid towards the invoice so far. This will be 0 for unpaid invoices.

• Status: The current status of the invoice, like "Open" or "Paid". Using a drop-down menu via Data > Data validation for this column is a great way to avoid typos.

Creating the "Outstanding Balance" Formula

You'll also need one calculated column in this tab. Add a column titled "Outstanding Balance" and use a simple formula to calculate what’s still owed:

Assuming the "Invoice Amount" is in column E and the "Amount Paid" is in column F, this formula subtracts the paid amount from the total, giving you the balance. Just drag this formula down for all your invoices.

Step 2: Calculate "Days Overdue" for Each Invoice

With our data organized, we can now calculate how many days each invoice is past due. This number will be the key to sorting each invoice into the correct aging bucket.

Go back to your "Invoices" tab and add a new column called "Days Overdue". Here, we'll use a formula that checks two things: is the invoice still open, and has its due date passed?

In the first cell of your new column, enter this formula:

Let's quickly break this down:

• IF(G2="Open", ...) checks if the Status in column G is "Open". The rest of the formula will only run if this is true.

• TODAY()-D2 is the core calculation. The TODAY() function always returns the current date. We subtract the Due Date (in column D) from today’s date.

• The formula gives us a number. If the number is positive, the invoice is past due. If it's negative, the due date is still in the future.

• , 0) tells the formula to simply return 0 if the status is not "Open" (meaning it’s Paid or Closed).

After dragging this formula down, your "Invoices" tab is now fully prepared. The "Days Overdue" column automatically updates every day, making your final report fully dynamic.

Step 3: Build the AR Aging Report Tab

Now for the fun part. Create a new, blank tab and name it "AR Aging Report". This is where your summary will live.

Get a Unique List of Customers

First, we need a clean list of all your customers without any duplicates. The UNIQUE function is perfect for this. In cell A2 of your new "AR Aging Report" tab, enter:

This formula pulls every name from the "Customer Name" column of your "Invoices" tab and automatically creates a new list with all duplicates removed.

Set Up Your Aging Buckets as Columns

Next, set up your report's columns. In the first row, starting from column B, type out the headers for your aging categories:

• Current

• 1-30 Days

• 31-60 Days

• 61-90 Days

• 91+ Days

• Total Overdue

Your blank report structure is now ready for the final formulas.

Step 4: Use SUMIFS to Sort Invoices into Buckets

To pull the outstanding balances and drop them into the correct buckets, we’re going to use the powerful SUMIFS function. This function adds numbers that meet multiple conditions - perfect for our needs.

We’ll build one formula, and then you'll see how easy it is to adapt for the rest.

Formula for the "1-30 Days" Bucket

In the first cell under your "1-30 Days" header (cell C2), enter this formula:

Let’s break it down piece by piece:

• The Numbers to Add: Invoices!$H$2:$H is the "Outstanding Balance" column from our data tab. This is what we want to sum up. The dollar signs ($) lock the rows and columns so they don’t change when we drag the formula.

• Condition 1: Match the Customer: Invoices!$A$2:$A, $A2 tells Sheets to only sum amounts if the "Customer Name" in the "Invoices" tab matches the customer in cell A2 of our report.

• Condition 2: Check Lower Bound: Invoices!$I$2:$I, ">=1" checks the "Days Overdue" column and only includes amounts where the invoice is 1 day overdue or more.

• Condition 3: Check Upper Bound: Invoices!$I$2:$I, "<=30" adds a final check on the same "Days Overdue" column, ensuring it only includes amounts where the invoice is 30 days overdue or fewer.

In plain English, this formula says:

"Hey Google Sheets, look through my Invoices tab. For the customer in cell A2, add up all the outstanding balances, but only for invoices that are between 1 and 30 days past due."

Adapting the Formula for Other Buckets

This is where the magic happens. We can use the same formula across a whole report with small tweaks for each bucket.

Current (Invoices Not Yet Due):

(The change: we're only looking for values in Days Overdue that are less than 1).

31-60 Days:

(The change: we update the day parameters from >30 to <=60).

61-90 Days:

91+ Days:

(The change: here we only need one condition, because there is no upper limit).

Simply drag these formulas down the column for each customer, and your report will populate automatically as if by magic!

Step 5: Add Totals and Polish The Report

Your report is functional, but a few final touches will make it much easier to use.

Calculate Totals

Add a total for each customer row. After your "91+ Days" column, simply use a SUM formula to get a full view of what's owed:

You should also add totals at the bottom of each column to see the total amount outstanding in each aging bucket. This gives you a great bird's-eye view of your company's receivables.

Add Visuals with Conditional Formatting

Conditional formatting helps you spot problems instantly. You can set rules to highlight cells or rows based on their values.

A good starting point is highlighting any amount in the "61-90 Days" column as yellow and anything in the "91+ Days" bucket as light red. This immediately draws your eye to the oldest, most at-risk accounts without you having to read every single number.

Final Thoughts

Building an AR Aging Report in Google Sheets takes a bit of setup, but the payoff is enormous. Using a combination of a clean data source and the versatile SUMIFS formula, you can create a single source of truth for your receivables that stays current and supports better financial decision-making for your business.

While this manual process is a huge step up from disorganized data, keeping it running requires constant discipline and manual data entry. At Graphed, we created a service to eliminate this friction entirely. We connect directly to your financial platforms - like QuickBooks, Stripe, Shopify, and Salesforce - and pipe the live data into a fully automated system. Instead of building endless formulas, you can just ask in plain English: "Create an AR Aging report and break it down by a customer." We'll build the updated, interactive dashboard for you in seconds, saving you hours of tedious spreadsheet work and letting you focus on the insights, not just the data prep.