There are many ways to prepare debtors aging report in Excel. Among all of these, the below method is considered to be the best.

Step1: Arrange your data into Tabular form and name that sheet as “Data”

Step2: Create a master defining your Time buckets and name that sheet as “Master” and you can hide this sheet afterward s.

Step3:Create sheet named “Report”

Step4: Go to Data sheet and Calculate No.of days from the date of invoice to date of report. And Put the Vlookup Function with Range_lookup value as 1 (True) instead of usual 0(False).

Step5: Go to Report sheet and Put a pivot Table from Data sheet or Use Sumproduct formula as shown in the above picture.

All these steps has been done in one single sheet in the above picture to get the bird’s eye view.

Instead of entering ranges like A1:D20 range names has been utilized in these formulas

How to create/define names for ranges? And how to make these names Dynamic to accommodate increase or decrease in number of  rows or columns?

See this article….

Tweet This