The first time an intern saw a pivot table, he thought it was downright ugly. Like Aurélien and Bérénice, love seems impossible between the student and the pivot table. But don’t worry, Business Cool offers you a little tutorial so that you and your pivot table can make perfect love. To follow this course more easily, download the file here.
Pivot table, definition
As the name does not suggest, the pivot table converts a database into a multi-entry table. The word dynamic comes from the fact that a change in the database is reflected in the table. This makes it possible to clearly summarize a set of statistics taken from the database.
Prerequisites
The database must meet several criteria to be able to produce a pivot table:
- Data must be in columnar format
- Each column must have a non-empty header (a title)
- No empty lines
So it should look like this:
This database lists the 14 salespeople of a company and their sales for the week. Since sales prices are negotiated individually, one salesperson can sell more units than another while obtaining a lower turnover.
Creation
Once your base is ready, Select it without forgetting the column titles (here you should select the range from A1 to E15). Then go to InsertThen 
Then Okay. A new sheet has been created with the following window:
We now understand why it was essential to title each column. In fact, we will cross them to reveal new data. To do this, you must fill in the boxes Columns, Lines And Values. The idea is that Excel displays the distribution of a value (entry in Values) between aggregates (entered into Columns And Lines).
Let’s take an example, if we want to determine the turnover by sector and by gender we must first slip the label Turnover into Values. Then label Sex in Columnsand finally the Sector label in Lines.
NB: in this case, the Gender and Sector labels can go indifferently in Columns Or Lines. Only the display will differ but the values will be the same.
The pivot table displayed is therefore this one:
As desired, Excel has distributed the turnover by Gender and Sector. Thus, we read that women made €1,897 in the East and that men made €1,582 in the West. To improve readability it is possible to remove the totals; right-click on Grand Total then on Delete grand total.
In order to have a more detailed analysis of the sales figures, it is interesting to have the average turnover by sector and gender rather than the total turnover. To do this, you must click on the pivot table to bring up the window with the labels. Then click on the field label Valueso here Total turnover. Then Value field settings and replace Sum with Average.
Conclusion
Pivot tables are very powerful for summarizing thousands of rows and columns of data. This introduction will have allowed you to approach pivot tables with a small database to better understand the tool.
