How to Create an Aging Report in Excel

Tracking overdue invoices is one of the most critical, yet tedious, jobs for any business owner. An accounts receivable (A/R) aging report is the standard tool for this, giving you a clear snapshot of who owes you money and for how long. This guide will walk you through, step-by-step, how to build a powerful and dynamic A/R aging report from scratch using Microsoft Excel.

What is an Accounts Receivable Aging Report?

An A/R aging report, or aging schedule, is a record of a company's accounts receivable categorized by the length of time an invoice has been outstanding. It's used to determine which invoices are overdue for payment and helps you manage your collections process.

In simple terms, it groups unpaid customer invoices into time-based "buckets" such as:

• Current: Invoices that are not yet due (typically 0-30 days old).
• 31-60 days: Invoices that are 1-30 days past due.
• 61-90 days: Invoices that are 31-60 days past due.
• 91-120 days: Invoices that are 61-90 days past due.
• Over 120 days: Invoices that are more than 90 days past due.

Why bother? Maintaining this report is essential for healthy cash flow. It helps you quickly identify late-paying customers, allowing you to prioritize collection efforts and recognize potential bad debts before they cripple an otherwise healthy business. It’s a fundamental tool for financial health management.

Getting Your Data Ready for Excel

Before you can build the report, you need to collect the right data. Your accounting software (like QuickBooks, Xero, etc.) should have this, or you can manage it manually if you're just starting out. For each outstanding invoice, you’ll need at minimum:

• Customer Name: Who owes the money.
• Invoice Number: The unique identifier for the transaction.
• Invoice Date: The day the invoice was issued.
• Invoice Amount: The total amount due for that invoice.

Create a simple table in Excel with these four columns. For this tutorial, we will assume you've compiled a list of all your unpaid invoices. Trying to mix paid and unpaid invoices in the same sheet without filtering will make things unnecessarily complicated.

Step-by-Step Guide: Building Your Aging Report with Formulas

Once your data is neatly organized in a table, we can start adding the formulas that will power the report. In this example, let's assume your data is in columns A through D.

Step 1: Set Up The Basic Table

First, add a new column called "Days Overdue." This will be the engine of our report. Then, add columns for each of your aging buckets. Your spreadsheet should look something like this:

• A: Customer Name
• B: Invoice Number
• C: Invoice Date
• D: Invoice Amount
• E: Days Overdue
• F: Current (0-30)
• G: 31-60 Days
• H: 61-90 Days
• I: 91+ Days

We'll populate the last five columns using formulas.

Step 2: The Core Formula - Calculating "Days Overdue"

To calculate how long an invoice has been outstanding, we need to subtract the Invoice Date from today's date. Excel has a perfect function for this called TODAY(), which always returns the current date.

In cell E2 (the first cell in your "Days Overdue" column), enter the following formula:

=TODAY()-C2

Press Enter. If you see a date instead of a number, don't panic. Excel is just trying to be helpful by formatting the result as a date. To fix it, right-click the cell, choose "Format Cells," and select "Number" or "General" from the list. Make sure it's set to 0 decimal places.

Now, click on the small green square in the bottom-right corner of cell E2 and drag it down to apply this formula to all your rows. This column will now automatically update every day, making your report dynamic.

Step 3: Using the IF Function to Categorize Invoices

Now for the magic. We'll use a series of IF functions to place each invoice amount into the correct aging bucket. The IF function checks if a condition is true, and then does one thing if it is, and another thing if it isn't. Its structure is: IF(logical_test, [value_if_true], [value_if_false]).

We'll go column by column.

For the "Current (0-30)" bucket (Column F):

We want to check if the "Days Overdue" (Column E) is less than or equal to 30. If it is, we'll show the invoice amount. If it's not, we'll show a 0.

In cell F2, enter this formula:

=IF($E2<=30, $D2, 0)

For the "31-60 Days" bucket (Column G):

Here, we need to check two conditions: is the day count greater than 30 AND less than or equal to 60? We can use the AND function inside our IF function for this.

In cell G2, enter this formula:

=IF(AND($E2>30, $E2<=60), $D2, 0)

For the "61-90 Days" bucket (Column H):

Following the same logic, we'll now check for values between 61 and 90.

In cell H2, enter this formula:

=IF(AND($E2>60, $E2<=90), $D2, 0)

For the "91+ Days" bucket (Column I):

Finally, we'll check for any invoice that is over 90 days past due.

In cell I2, enter this formula:

=IF($E2>90, $D2, 0)

After entering the formulas in F2, G2, H2, and I2, drag the entire selection down to fill out the rest of your table. Each invoice amount should now appear in only one of the aging bucket columns.

Step 4: Summing the Results

To finish the report, scroll to the bottom of your data and create a "Totals" row. Use the SUM function to total the original "Invoice Amount" column as well as each of the aging bucket columns. This provides a high-level summary of exactly how much money is sitting in each category.

Advanced Techniques: Pivot Tables & Conditional Formatting

While the formula-based table is perfectly functional, we can make it much easier to read and analyze with a couple of extra Excel features.

Creating a Summary with a Pivot Table

A formula-based report can get very long. If you have many invoices from the same few clients, a pivot table is the best way to summarize the data.

1. Click anywhere inside your data table (cells A1 through I, whatever your last row is).
2. Go to the "Insert" tab on the ribbon and click "PivotTable."
3. Excel will auto-detect your table range. In the dialog box, just click "OK" to create the pivot table in a new worksheet.
4. Now, you'll see a "PivotTable Fields" panel on the right. Drag and drop the fields like this:

Instantly, you have a neat, clean summary showing the total amount owed by each customer, broken down by aging category. It’s a much faster way to spot which clients are consistently behind on payments.

Making Overdue Accounts Stand Out with Conditional Formatting

Our brains process visual cues like color much faster than they process numbers. We can use Conditional Formatting to make dangerously old invoices visually pop.

1. Go back to your original data sheet.
2. Highlight the "Days Overdue" column (Column E).
3. On the "Home" tab, click "Conditional Formatting."
4. Go to "Color Scales" and choose the "Red - Yellow - Green" option.

Excel will automatically color the cells, with the highest numbers (oldest invoices) in red, mid-range values in yellow, and the lowest numbers in green. This gives you an at-a-glance heat map of your biggest collection risks.

Interpreting Your Report: Now What?

An aging report is useless if you don't act on it. Your freshly made report is now a guide for your collections strategy.

• Large balances in the "Current" bucket? Great! That means your clients are paying on time and cash flow is healthy.
• Money piling up in the "31-60 Days" column? This is the time to start sending gentle payment reminders.
• Significant amounts in "61-90 Days" or "91+ Days"? This is a red flag. These invoices require immediate attention. Pick up the phone, send a formal letter, and pause any new work for these clients until old balances are settled. The longer a debt sits unpaid, the less likely you are to ever collect it.

Final Thoughts

By following these steps, you can create a robust and automated accounts receivable aging report right in Excel. Setting it up takes a little bit of time, but its value in managing your business's cash flow is immense, helping you stay on top of who owes you money so you can take action before it becomes a problem.

While Excel is powerful, the process of exporting data, checking formulas, and refreshing reports can still feel like manual work. This is one of the reasons we built Graphed . We wanted to eliminate the spreadsheet busywork by connecting directly to your data sources - like QuickBooks, Stripe, Shopify, or even a Google Sheet. Once connected, we allow you to build live, interactive dashboards just by describing what you want to see. Instead of wrestling with IF functions, you could simply ask, "create an A/R aging report summarized by customer," and get a real-time view of your finances that updates automatically.