Imagine you need to do the short report from huge table which contains a lot of different information (for decision-making). Suppose we have data about credit portfolio, where also is recorded information about each borrower. So, for further analysis of credit portfolio, from data base was downloaded such table:
There are each separate row contains full information about one loan agreement:
Course of time the table can increase to a huge size.
But even more horror you will feel necessity to do reporting using that data. How many loans were issued in each month? Which loan size of credit portfolio by splitting of loan category by the numbers and amounts? Top 10 customers? And so on...
Answers to all questions can be obtain easier than you think. Nearly, during three minutes. By means of one of the amazing tool in Microsoft Excel - Pivot tables.
Let's go ...
Select all our table with data and press Insert - PivotTable.
Will appear such dialog box, in which we can choose where insert our pivot table and adjust data range.
By default is chosen insert pivot table at New Worksheet, but we can also, if necessary, manual choose current or any other worksheet (Existing Worksheet). Will be better to select new worksheet - there is no risk that the pivot table will overlap the source list and we get a lot of circular references. Press button OK and proceed to the most interesting stage - report construction.
The fact that you will see by click button OK named "layout" of pivot tables:
To work with its is not difficult - you should drag columns label (fields) by mouse from window PivotTable Field List in the area Row Labels, Column Lables, Report Filter and Values. The only nuance - do it precise, don't muff! Let's go ...
During draging the fields the pivot table will be changed its appearance and show data you need. Moving all four required field from list, you will take prepared report. Remain its only worthy format.
Choose format pivot table in menu PivotTables Tools - Design:
And you will take the following appearance:
It is so easy, isn't it?
The only disadvantage of pivot tables - absence of automatic refresh (recalculation) while changing data in existing list. For recalculation you should click on the pivot table by the right mouse button and choose command Refresh in context menu.