From an account statement, we can create an accounts receivable aging report in Google Sheets. The process involves automatically moving outstanding amounts to corresponding aging columns.

Additionally, as part of the report, we will create a stacked bar chart that helps you easily see which range each customer’s outstanding amounts fall into. This allows you to prioritize following up with your customers for payment.

Here, we will use standard logical tests for automation. This is the simplest method and easiest way to create a chart. You can also use QUERY or Pivot Tables for creating AR aging reports, which I have detailed in separate tutorials linked in the Resources section at the bottom of this tutorial.

I am confident that you will find this AR aging report to be a tremendous time-saver. You won’t need to manually move due amounts to columns based on the length of time they have been unpaid.

Preparing the Sample Data

The top row, i.e., A1:J1, should contain the following field labels:

Customer | Invoice Number | Invoice Date | Due Date | Amount | Current | 1-30 days | 31-60 days | 61-90 days | 90+ days

Fill data in the first five columns.

Select the range and click Format > Convert to Table.

Using a structured table ensures that when you add new rows, the formulas, and formatting are automatically applied to the new rows.

From the 6th column onwards, we will use structured formulas to move the due amount from the fifth column based on the due date in the fourth column.

Creating Accounts Receivable Aging Report

For this example, we will use the following sample data in the range A1:J13, structured as described above.

Formulas for Moving Due Amount to Aging Columns:

The table name is Table_1 (please see the top left corner of the table). I’ve used that table name in the formulas below. When using these formulas, you should either rename your table to Table_1 or modify the formula to replace Table_1 with your actual table name. To rename the table name, simply click on it and edit it.

To create the aging report, enter the following formulas in cells F2 to J2.

F2 Formula to Move Current Amounts to Column F:

=IF(TODAY()-Table1[Due Date]<1, Table1[Amount],"")

G2 Formula to Move Amount Due by 1-30 Days to Column G:

=IF((TODAY()-Table1[Due Date]>0)*(TODAY()-Table1[Due Date]<31)=1, Table1[Amount],"")

H2 Formula to Move Amount Due by 31-60 Days to Column H:

=IF((TODAY()-Table1[Due Date]>30)*(TODAY()-Table1[Due Date]<61)=1, Table1[Amount],"")

I2 Formula to Move Amount Due by 61-90 Days to Column I:

=IF((TODAY()-Table1[Due Date]>60)*(TODAY()-Table1[Due Date]<91)=1, Table1[Amount],"")

J2 Formula to Move Amount Due by 90+ Days to Column J:

=IF(TODAY()-Table1[Due Date]>90, Table1[Amount],"")

Once entered, copy F2:J2 and paste them from F3 to the last row of your table. For this sample data, you should copy F2:J2 and paste them into F3:J13.

This will move all the due amounts to the corresponding aging columns.

Adding a Total Row:

To add a total row, leave two rows below the table. Then use the following formulas in columns F to J in the row immediately below the table:

=SUM(Table1[Current])
=SUM(Table1[1-30 days])
=SUM(Table1[31-60 days])
=SUM(Table1[61-90 days])
=SUM(Table1[90+ days])

This ensures that the total row is not included within the table.

The next step in the AR aging report is to create a bar chart. Here are those steps.

Download Sample Sheet

Bar Chart for Accounts Receivable Aging Report

To create the AR bar chart, we need the customer name column and the aging columns. The Invoice Number, Invoice Date, and Due Date columns are not necessary.

We will extract the required columns and use them to create the bar chart.

Preparing the Data for the Chart:

In a second sheet in the same file, enter the following labels in the first row, i.e., A1:F1:

Customer | Current | 1-30 days | 31-60 days | 61-90 days | 90+ days

In cell A2, enter the following formula to copy the values in the customer name and aging columns:

=HSTACK(Table1[Customer], Table1[Current]:Table1[90+ days])

(Note: This formula uses structured references, so when you add new rows in the table, they will be reflected here as well.)

Creating the Accounts Receivable Aging Chart:

1. Select A1:F.
2. Click Insert > Chart.
3. Under Chart Type in the chart editor sidebar panel, select Stacked Bar Chart.
4. Under “Y-Axis,” check “Aggregate” data.

Your aging analysis bar chart is ready. You can use the customization options within the chart editor panel to remove or modify titles, legend positions, etc.

That’s all about creating an accounts receivable age analysis report in Google Sheets. Please find the resources below for the pivot table and QUERY approaches.

Resources

• Age Analysis with Google Sheets Pivot Tables
• Finding Due Payments: Age Analysis with Google Sheets QUERY
• Create an Accounts Receivable Aging Heat Map in Google Sheets