Contents
Video
Lyrical introduction or motivation
Imagine yourself as a sales manager. Your company has two warehouses from which you ship to customers, for example, fruits and vegetables. To account for the sold in Excel, the following table is filled out:
In it, each individual line contains complete information about one shipment (deal, batch):
- which of our managers made a deal
- with which customer
- what kind of product and for what amount sold
- from which of our warehouses was the shipment
- when (month and day of the month)
Naturally, if sales managers know their business and plow seriously, then every day several dozen lines will be added to this table, and by the end, for example, of a year or at least a quarter, the size of the table will become terrifying. However, you will be even more horrified by the need to create reports on this data. For example:
- How many and what products were sold each month? What is the seasonality of sales?
- Which of the managers concluded how many orders and for what amount? Which manager is entitled to how many bonuses?
- Who are our top five largest customers?
… etc.
All of the above and many similar questions can be answered more easily than you think. We will need one of the most stunning tools of Microsof Excel – pivot tablesы.
Go…
If you have Excel 2003 or older
We put the active cell in the table with data (in any place of the list) and click on the menu Data – PivotTable (Data — PivotTable and PivotChartReport). Starts a three-step PivotTable Wizard (Pivot Table Wizard). Let’s go through his steps with the help of buttons Next (Next) и Back (Back) and in the end we get what we want.
Step 1. Where does the data come from and what is the output?
At this step, you need to choose where the data for the pivot table will be taken from. In our case, there is nothing to think about – “in a list or Microsoft Excel database”. But. In principle, data can be loaded from an external source (for example, a corporate database in SQL or Oracle). Moreover, Excel “understands” almost all existing types of databases, so most likely there will be no big problems with compatibility. Option In multiple consolidation ranges (Multiple consolidation ranges) is used when the list on which the pivot table is built is divided into several sub-tables, and they must first be combined (consolidated) into one whole. The fourth option “in another pivot table …” is needed only in order to build several different reports on one list and not load the list into RAM each time.
Type of report – to your taste – only a table or a table immediately with a diagram.
Step 2. Highlight the source data, if necessary
In the second step, you need to select a range with data, but, most likely, you won’t even have to do this simple operation – as a rule, Excel does it itself.
Step 3. Where to put the pivot table?
In the third last step, you only need to choose a location for the future pivot table. It is better to choose a separate sheet for this – then there is no risk that the pivot table will “overlap” with the original list and we will get a bunch of cyclic links. Click the button Finish (Finish) and move on to the most interesting – the stage of constructing our report.
Working with the layout
What you will see next is called layout. (layout) pivot table. It is easy to work with it – you need to drag the names of columns (fields) from the window with the mouse PivotTable Field List (Pivot Table Field List) in lines (Rows), the columns (Columns), pages (pages) и data (Data Items) layout. The only caveat – do it more precisely, do not miss! As you drag and drop, the PivotTable will begin to change before your eyes, displaying the data you need. Having transferred all five fields we need from the list, you should get an almost finished report.
It remains only to format it with dignity:
If you have Excel 2007 or newer
In the latest versions of Microsoft Excel 2007-2010, the procedure for building a pivot table has been noticeably simplified. Put the active cell in the table with the source data and click the button summary table (Pivot Table) tab Insert (Insert). Instead of the 3-step Wizard from previous versions, one compact window will be displayed with the same settings:
In it, as well as before, you need to select the data source and the output location of the pivot table, click OK and go to layout editing. Now it is much easier to do this, because. you can transfer fields not to the sheet, but to the lower part of the PivotTable Field List window, where the areas are presented:
- Row names (Row labels)
- Column names (Column labels)
- The values (Values) – earlier it was the area of data elements – calculations take place here.
- Report filter (Report Filter) – used to be called Pages (pages), the meaning is the same.
You can drag fields into these areas in any order, the risk of missing (unlike previous versions) is minimal.
PS
The only relative disadvantage of pivot tables is the lack of automatic updating (recalculation) when the data in the original list changes. To perform such a recalculation, right-click on the pivot table and select the command from the context menu Update & Save (Refresh).
- Set up calculations in PivotTables
- Grouping dates and numbers with the desired step in pivot tables
- Pivot table across multiple ranges from different sheets