GST Course

Share on Facebook

Share on Twitter

Share on LinkedIn

Share on Email

Share More


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

STEP-8 And after your raw data is converted into meaningful report

Pivot Table Row Data

STEP-9 But still this report need some formatting.

First select uncheck of +/- button,

Pivottable Tools

STEP-10 Then go to design remove subtotal

Excel Pivot Table Design

STEP-11 Select “Show in tabular form from Report layout tab 

Excel Pivot Table Report Layout

FINALLY YOUR REPORT IS READY 

Pivot Table Report

Hope you got an idea of this article. And if you have any query related to this article or anything in Microsoft Excel, feel free to get in touch.

The author can also be reached at camukeshkumar@outlook.com

You may also make reference on my other articles on CCI on Advance Functions of Microsoft Excel 

Vlookup's Advance Function (Advance Excel)
Data Consolidation in Microsoft Excel Part-2 (Advance Excel)
Data Consolidation in Microsoft Excel (Advance Excel)
Linking Tally with Excel Steps (Advance Excel)
Scenario Manager in Excel (Revised)
Data Protection In Microsoft-Office


Tags :



Category Info Technology, Other Articles by - CA Kumar Mukesh 



Comments


update