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

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some special tricks to do aging analysis in Excel, youâ€™ve come to the right place. There is one way to do aging analysis in Excel. This article will discuss every step of this method to do aging analysis in Excel. Letâ€™s follow the complete guide to learn all of this.

## What Is Aging Analysis?

Accountants use aging to determine any irregularities in a companyâ€™s accounts receivables. To figure out how long an outstanding bill has been unpaid, customer invoices are usually categorized by 30 days.

## How to Do Aging Analysis in Excel: Step-by-Step

In the following section, we will use one effective and tricky method to do aging analysis in Excel. To create a more understandable aging report, it is necessary to make a basic outline and calculations with formulas, as well as convert the dataset to a pivot table. This section provides extensive details on this method. You should learn and apply all of these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

### Step 1: Create Dataset

Here, we have created the dataset of aging analysis.

• In the following image, we can see the basic dataset of the Aging Analysis report.
• Here, we have Customer names, Invoice numbers, Date, and Amount in the following dataset.
• For further calculations, we have inserted the columns Days Sales Outstanding and Status of Invoice.

Now, we are going to create another outline for analyzing the status of the invoice in the Category sheet.

• You have to create the categories of the invoice according to their dayâ€™s sales outstanding to dictate the condition. Here, we have also named the range of the cells as LIMITS.

• To name the range of the cells as LIMITS, you have to select the cells as shown below and go to the Formulas tab and select Name Manager.

• Next, when the New Name dialog box appears, enter the name as LIMITS in the NameÂ box.

### Step 2: Use Formulas for Aging Analysis

Now, we will do further calculations for aging analysis. Here we use TODAY and IF functions to calculate the dayâ€™s sales outstanding column values. We also use the VLOOKUP function to determine the status of the invoice.

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

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

Here, D5 is the date of each invoice, and the TODAY function will return todayâ€™s date which is 13-06-22. The IF function will return 0 if the difference between two values is negative, otherwise the Days Sales Outstanding value will be equal to the difference between two positive values.

• Then, press Enter.

• Next, drag the Fill handle icon.
• As a consequence, you will get the following Days Sales Outstanding column.

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

=VLOOKUP(F5,LIMITS,2,TRUE)

By applying the above formula, we will be able to identify the conditions of the invoice by looking up the values of days sales outstanding. Here, F5 is the look-up value which we are going to look up in the LIMITS named range. 2 is the column index number and TRUE is for an approximate match.

• Then, press Enter.

• Next, drag the Fill handle icon.
• As a consequence, you will get the following Status of Invoice column.

### Step 3: Create Pivot Table for Aging Analysis Summary

Now, we are going to create a pivot table to organize the data table for dictating the status of the invoice.

• First of all, you have to select the range of the cells as shown below.
• Then, go to the Insert tab and select Pivot Table.

• When the PIVOT Table from table or range dialog box appears, select the range of the cells and choose New Worksheet.
• Next, click on OK.

• When the PivotTable Fields window appears, drag down the Customer to the Rows area, Amount to the Values area, and Status of Invoice to the ColumnsÂ area.

• As a result, you will get the following pivot table. Through this pivot table, we can figure out how long an outstanding bill has been unpaid by categorizing customer invoices by 30 days.

### Step 4: Generate Dynamic Aging Analysis Report

To create a dynamic summary of the aging analysis, we are going to create a chart.

• To create a clustered column chart, select the range of data and go to the Insert tab. Next, select the Clustered Column chart.

• As a consequence, you will get the following Clustered Column chart.

• To modify the chart style, select Chart Design and then, select your desired Style 8 option from the Chart Styles group.

• As a consequence, you will get the following Clustered Column chart

• This is how we will get the final report of aging analysis. Through the following summary, we can figure out how long an outstanding bill has been unpaid.

## ðŸ’¬ Things to Remember

âœŽ When you use the IF function carefully gives all the required parentheses. If you donâ€™t use the if function to identify the dayâ€™s sales outstanding, we will get negative values. To avoid negative values we use the if function.

âœŽ You have to adjust the row height after following each method.

## Conclusion

Thatâ€™s the end of todayâ€™s session. I strongly believe that from now you may be able to do aging analysis in Excel. l. If you have any queries or recommendations, please share them in the comments section below.

<< Go Back to Ageing

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

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