How to Do Aging Analysis in Excel (with Quick Steps)

Step 1 – Create the Dataset

• In the following image, you can see the basic dataset of the Aging Analysis report.
• We have Customer names, Invoice numbers, Date, and Amount.
• We have inserted the columns Days Sales Outstanding and Status of Invoice.

• Create the categories of the invoice according to their day’s sales outstanding to dictate the condition.

• Select the cells and go to the Formulas tab, then select Name Manager.

• When the New Name dialog box appears, enter the name as LIMITS in the Name box.

Step 2 – Use Formulas for the Aging Analysis

• To calculate days sales outstanding, use the following formula in cell F5:

• Press Enter.

• Dag the Fill handle icon down to fill the column.

• To determine the status of the invoice, use the following formula in the cell F5:

• Press Enter.

• Drag the Fill handle icon down.

Step 3 – Create a Pivot Table for an Aging Analysis Summary

• Select the dataset.
• Go to the Insert tab and select PivotTable.

• In the PivotTable from table or range dialog box, choose New Worksheet.
• Click on OK.

• In the PivotTable Fields window, drag Customer to the Rows area, Amount to the Values area, and Status of Invoice to the Columns area.

• You will get the following pivot table.

Step 4 – Generate a Dynamic Aging Analysis Report

• Select the dataset and go to the Insert tab.
• Select the Clustered Column chart.

• You will get the following Clustered Column chart.

• Select Chart Design and select the Style 8 option from the Chart Styles group.

• You will get the following Clustered Column chart

• Here’s the final result.

Things to Remember

✎ You have to adjust the row height after following each method.

Download the Practice Workbook

<< Go Back to Ageing | Formula List | Learn Excel