How to Create an AR Aging Report in Excel

Building an accounts receivable (AR) aging report in Excel is a powerful way to get a clear picture of your company's cash flow and customer payment habits. This simple report helps you track outstanding invoices and identify which customers are paying on time versus those who are falling behind. This article will walk you through, step-by-step, how to create your own AR aging report from scratch using common Excel functions.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What is an Accounts Receivable Aging Report?

An AR aging report categorizes your unpaid customer invoices by the length of time they have been outstanding. Its main purpose is to help you manage your receivables and make informed decisions about collections. Think of it as a financial health checkup for your client payments.

Typically, the report groups invoices into buckets, such as:

• Current: Invoices that are not yet due.
• 1-30 Days Overdue: Invoices that are 1 to 30 days past their due date.
• 31-60 Days Overdue: Invoices that are 31 to 60 days past their due date.
• 61-90 Days Overdue: Invoices that are 61 to 90 days past their due date.
• 91+ Days Overdue: Invoices that are more than 90 days past their due date.

By organizing data this way, you can quickly see how much money is tied up in outstanding invoices and identify which accounts require immediate attention. It's an essential tool for protecting your cash flow and reducing bad debt.

First, Gather Your Data

Before you open Excel, you need a clean list of all your outstanding invoices. You can typically export this data from your accounting software (like QuickBooks or Xero) or your CRM (like Salesforce). At a minimum, your data set should include the following columns:

• Customer Name: The name of the client.
• Invoice Number: A unique identifier for each invoice.
• Invoice Date: The date the invoice was issued.
• Due Date: The date payment is contractually due.
• Invoice Amount: The total amount due for the invoice.
• Amount Paid: How much the customer has paid so far.

Once you have this data, open a new Excel worksheet and organize it into a table with these column headers. For the best results, format this data as an official Excel Table by selecting any cell in your data and pressing Ctrl + T (or Cmd + T on Mac). This makes formulas easier to manage and ensures they automatically apply to new rows.

Here's an example of how your initial data table might look:


AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step-by-Step: Building Your AR Aging Report in Excel

With your data prepped, you can start building the brains of the report. We'll add a few new columns and use formulas to automatically categorize each invoice.

Step 1: Calculate the Outstanding Balance

First, we need to know the amount that is actually outstanding for each invoice. Create a new column called Outstanding Balance and use a simple subtraction formula to calculate it.

=[@[Invoice Amount]]-[@[Amount Paid]]

The @[Column Name] syntax is part of using Excel Tables and makes your formulas much more readable. If you're not using an Excel Table, your formula would look like E2-F2 (assuming Invoice Amount is in column E and Amount Paid is in column F).


Step 2: Calculate the "Days Overdue"

Next, we need to determine how many days have passed since the invoice due date. Create a column named Days Overdue. Here, we'll use the TODAY() function, which always returns the current date.

The formula subtracts the due date from today's date. We'll also wrap it in an IF statement to check if the invoice still has an outstanding balance. This prevents calculation on fully paid invoices.

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

This formula says: "If the Outstanding Balance is greater than zero, calculate the number of days between today and the Due Date. Otherwise, just show zero." A negative number in this column indicates an invoice that is not yet due.


Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 3: Create and Populate the Aging Buckets

Now it's time to set up our aging buckets. Add new columns to your table for each category: Current, 1-30 Days, 31-60 Days, 61-90 Days, and 91+ Days.

We'll use a series of IF and AND formulas to place each outstanding balance into its correct bucket. For each cell, we're asking Excel to check if the Days Overdue falls within a specific range and, if it does, to display the outstanding balance in that column. Otherwise, it should show 0.

Formula for "Current"

This checks for invoices that are not yet due (where "Days Overdue" is less than or equal to 0).

=IF([@[Days Overdue]]<=0, [@[Outstanding Balance]], 0)

Formula for "1-30 Days"

This checks for invoices that are between 1 and 30 days overdue.

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

Formula for "31-60 Days"

This checks for invoices that are between 31 and 60 days overdue.

=IF(AND([@[Days Overdue]]>=31, [@[Days Overdue]]<=60), [@[Outstanding Balance]], 0)

Formula for "61-90 Days"

This checks for invoices that are between 61 and 90 days overdue.

=IF(AND([@[Days Overdue]]>=61, [@[Days Overdue]]<=90), [@[Outstanding Balance]], 0)

Formula for "91+ Days"

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

=IF([@[Days Overdue]]>=91, [@[Outstanding Balance]], 0)

After entering these formulas, your table will now show the outstanding balances sorted neatly into the correct aging buckets.


Summarizing Your Report with a Pivot Table

The detailed table is great for seeing individual invoices, but a summary view is more effective for high-level analysis. A Pivot Table is the perfect tool for this.

1. Click anywhere inside your Excel Data Table.
2. Go to the Insert tab on the Ribbon and click PivotTable.
3. Excel will automatically select your table range. Click OK to create the Pivot Table in a new worksheet.
4. In the PivotTable Fields pane that appears on the right, drag and drop the fields like this:

This will instantly generate a clean, summarized report showing the total amount owed by each customer, broken down by aging category. Be sure to format the numbers as currency for better readability.


AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Bonus Tip: Visualize with Charts and Conditional Formatting

To make your report even more digestible, you can supplement your data with visuals. Here are two quick enhancements:

Conditional Formatting: Go back to your main data table. Select the "Days Overdue" column and click on Conditional Formatting > Color Scales. Choose a red-yellow-green scale. This will instantly color-code your overdue invoices, making the most urgent ones stand out in red.

Create a Chart: From your Pivot Table, go to PivotTable Analyze > PivotChart. A clustered or stacked bar chart works well. Plot the customer names on one axis and the aging buckets on the other. This gives you a quick visual comparison of who owes you the most money and how overdue they are.


Final Thoughts

Creating an AR aging report in Excel provides you with a clear, actionable view of your company's accounts receivable. Following these steps transforms a simple list of invoices into a powerful tool for managing cash flow, prioritizing collections, and making strategic financial decisions.

While Excel is a fantastic tool, this process can become repetitive if you're constantly exporting CSVs from your accounting software, CRM, and payment processors to build reports. To help with this, we've designed Graphed to connect directly to tools like QuickBooks, Stripe, Salesforce, and HubSpot. Instead of wrangling formulas, you can simply ask in plain English for a real-time AR aging dashboard that updates automatically, helping your team get answers and stay on top of collections without the manual work.