Ageing report from pivot table - Advance Excel

CA Kumar Mukesh , Last updated: 26 April 2021
Share

We all are finance professionals, some may be in INDUSTRY few in PRACTICE and most of us are student to become future professionals. But whatever stage we are everyone have to face computer screen filled with Microsoft Excel Spreadsheets, outlining with financial result, budgets, forecasts and plans used to make business decisions ranging from operational to the tactical.

Most of us aware that Microsoft Excel can add, subtract, multiply and divide functions, but if we are able to use its advance functions like Vlookup, Hlookup, Index + Match etc. coupled with IF function, we can able to demeanor DATA MINING within few countable minutes which generally takes hours to complete the task and sometime impossible if we have some complex data.

Having said so, but after getting experience from different class of user of Microsoft Excel, one thing comes in mind why they people are wasting their valuable hours in completing their task in Microsoft Excel, for the things which could be completed within few minutes.

And to be honest, this gives me a sense that I should write articles as many I can on important functions Microsoft Excel.

AGEING REPORT FROM PIVOT TABLE

Excel’s Pivot Table feature is an incredibly powerful tool that makes easy to summarize data in speared sheet, particularly if someone finds difficulties in writing lots of formulas to summarize data in excel (such as COUNTIF and SUMIF). Not only to that but it also allows to quickly change how data is summarized with almost no efforts at all.

This time I will explain how to prepare a report on ageing, ageing may be for Debtor/Creditor or simple stock ageing in few seconds.

For this first you need to download practice workbook to get detailed and easy understanding of below mentioned steps. (Click Here)

STEP-1 For creating data pivot table you need to go INSERT tab and then PIVOT TABLE.

Excel Pivot Table

STEP-2 The next window will be

Excel Create Pivot Table

STEP-3 In this tab you can modify range of data for Pivot table and location where pivot table. After modify, if you needed, you need to click OK

Excel Modify Pivot Table

STEP-4 After this you will get this type of sheet.

Excel Pivot Table Fields

STEP-5 Here you need to first drag “Product description” and “Product code” in Rows tab, “Cost” in values tab and “Holding period” in column tab.

Excel Pivot Table Product Description

STEP-6 After that you need to right click on holding period row and select group.

Excel Pivot Table Select Group

STEP-7 After selecting group you will get starting and ending numbers and range group. Here I have chosen for default "100" as range difference between lower and upper limit. You may choose something else.

Pivot Table Grouping