Money Matters

How to create an aging report & formulas in Excel

Creating aging reports is essential for managing your business accounting. Learn the steps to master the process and keep track of overdue invoices with ease.

June 3, 20254 min read Share

Excel can be tricky at times, and it may even feel like you’re back in high school algebra, dealing with long strings of parentheses and if-then statements. This guide breaks down the simple steps for creating an aging report in Excel. It includes all the formulas you’ll need to figure out who’s the most overdue and how much you’re owed in receivables.

Want to level up your operations with something even better than Excel? Try Sage Accounts Receivable Software and get real-time aging and other AR KPIs in minutes.

Aging report cheat sheet: A step-by-step guide

Follow these simple steps to set up and calculate your receivables:

Step 1: Label your columns

Start by labeling the following cells: 

• A1: customer
• B1: order #
• C1: date
• D1: amount due

Then, enter the corresponding information for your customers and their orders beneath these headers. 

Step 2: Add additional headers

Next, add these additional headers:

• E1: Days outstanding
• F1: Not due
• G1: 0-30 days
• H1: 31-60 days
• I1: 61-90 days
• J1: >90 days

Step 3: Calculate “Days outstanding”

Now, you’ll need to input a formula for the “days outstanding” column to track the number of days each invoice has been unpaid since the due date. 

In cell E2, enter the following formula:

= IF(TODAY()C2,TODAY()-C2,0)

Step 4: Copy the formula down

To apply this formula to the entire column, click the fill handle (the small square in the bottom-right corner of cell E2) and drag it down to the last customer. This step will spread the formula across the entire column.

Step 5: Add conditional formatting

You need to add some color to your aging report to make it easier to read. This step will help you quickly spot overdue invoices.

1. Highlight all the rows in column E.
2. Go to the “Home” tab, click “Conditional Formatting,” and select “New Rule.”

Step 6: Set up the color scale

A separate window will open titled “New Formatting Rule.”

1. In the “Format Style” dropdown, select the 3-Color Scale.
2. In the “Type” dropdown, select “Number.”
3. For “Values”, enter:

• 0 for the minimum,
• 60 for the midpoint,
• 90 for the maximum.

Finally, choose three easy-to-distinguish colors. These will represent different aging categories (for example, green for not due, yellow for 0-30 days, and red for >90 days).

Step 7: Calculate “Not due”

In cell F2, use the following formula to find out which invoices aren’t due yet:

= IF(E2=0,D2,0)

Drag the fill handle down to apply this formula to the whole column.

Step 8: Calculate “0-30 days”

This formula checks if the difference between today’s date and the due date is 30 days or less.

In cell G2, enter the following formula:

= IF(C2<TODAY(),(IF(TODAY()-C2<=30,D2,0)),0)

Again, drag the fill handle down to fill the rest of the column.

Step 9: Calculate “31-60 days”

This formula uses an AND statement to check if the difference is between 31 and 60 days.

In cell H2, enter:

= IF(AND(TODAY()-$C2<=60,TODAY()-$C2>30),$D2,0)

Then, drag the fill handle down to apply the formula.

Step 10: Calculate “61-90 days”

This formula is similar to the one in step 9, just for the 61-90 day range. 

In cell I2, enter:

= IF(AND(TODAY()-$C2<=90,TODAY()-$C2>60),$D2,0)

Once again, drag the fill handle down to fill the column.

Step 11: Calculate “>90 days”

For invoices that are more than 90 days overdue, this formula checks if the difference is greater than 90 days.

In cell J2, enter:

= IF(TODAY()-$C2>90,D2,0)

Drag the fill handle down to apply this formula to the column.

Step 12: Sum up the values

To calculate the total amount for each column, select the first empty cell beneath the “Not Due” column, then press ALT+ = to sum the column. Repeat this for the “>90” column to get the total amounts for each age group.

Did you know you can automate this activity? With Sage Accounts Receivable Software, your aging reports won’t be limited to static Excel files. Take the product tour, discover how to automate real-time data, and effortlessly generate reports.