Pivot Table (Pivot Table) is a powerful tool for calculating, summarizing, and analyzing data that lets you see comparisons, patterns, and trends in your data.
Imagine that you need to make a short analytical report for decision making from a large table that contains a lot of different information. Suppose you have data that displays information on the Bank's loan portfolio, where detailed information on each borrower is recorded.
Let's assume that the following table is loaded from the database into Excel to analyze the loan portfolio:
In it, each individual line contains complete information about one credit agreement:
Naturally, if sales managers know their business and work hard, then every day several tens of lines will be added to this table, and by the end of, for example, a year or at least a quarter, the size of the table will become monstrous.
However, even greater horror will cause you the need to create reports based on this data. How many loans were issued each month? What is the size of the loan portfolio in terms of lending by quantity and amount? Top ten borrowers? etc.
Answers to all questions can be obtained more easily than you think. About three minutes. With one of the most amazing tools Microsoft Excel — Summary tables .
Let's go...
Consider the following data preparation tips before creating PivotTables:
Select our entire data table and click on the menu Insert - Summary table (Insert - PivotTable ).
This dialog box will appear, in which we can choose where to place our pivot table and adjust the data range.
By default, Excel chooses to place the pivot table on a new sheet (New Worksheet), but we can, if necessary, manually select the current or any other workbook sheet (Existing Worksheet). It is better to choose a new sheet for this - then there is no risk that the consolidated table will "overlap" with the original list and we will get a bunch of circular links. Press the OK button and proceed to the most interesting stage, the construction stage of our report.
What you will see after clicking the OK button is called the layout (layout) of the summary table:
It is not difficult to work with it - you need to drag the names of the columns (fields) with the mouse from the List of fields window of the summary table (PivotTable Field List) in the area:
The only caveat is to do it more precisely, don't miss! Let's go...
In the process of dragging, the consolidated table will begin to change its appearance before your eyes, displaying the data you need. After crossing all the four fields we need from the list, you should get an almost ready report. It remains only to format it well.
Select the formatting of the pivot table in the PivotTables Tools - Design menu:
But we get the following look:
It's not that complicated, is it?
The only drawback of summary tables is the lack of automatic updating (recalculation) when the data in the original list changes. To perform such a calculation, it is necessary to click on the summary table with the right mouse button and select the command in the context menu Update (Refresh) .