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.

Create Primary Outline

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.

How to Do Aging Analysis in Excel

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

How to Do Aging Analysis in Excel

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

Use Formulas for Aging Analysis

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

How to Do Aging Analysis in Excel

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

How to Do Aging Analysis in Excel


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.

Create Pivot Table for Aging Analysis Summary

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

How to Do Aging Analysis in Excel

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

How to Do Aging Analysis in Excel


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.

Generate Dynamic Aging Analysis Report

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

How to Do Aging Analysis in Excel

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

How to Do Aging Analysis in Excel


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


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets and graphs in different spreadsheets for a clear understanding.


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 | Formula List | Learn Excel

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 Shuvo
Saquib Ahmad Shuvo

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

4 Comments
  1. Reply
    Kamarozzaman Ismail Sep 21, 2022 at 2:14 PM

    Thank you. A first-timer doing the aging analysis and the tutorial is very helpful.

  2. Excellent, thanks a lot

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo