# 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:

`=IF(TODAY()>D5,TODAY()-D5,0)`

• 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:

`=VLOOKUP(F5,LIMITS,2,TRUE)`

• 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.

<< Go Back to Ageing

Get FREE Advanced Excel Exercises with Solutions!

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio