How to create Pivot Table

Derived (Pivot) tables allow summarizing, counting, or aggregating data ¹  from the initial/analytical table. We often hear or say “pivoting” or worse, let’s “pivot” my table.

What are the pivot tables for?

It is a procedure of grouping data according to some criteria and performing the most common collection or some other mathematical operation on the data from the initial table. The initial table whose data we want to process can be in the same or a file that is on a disk or shared folder.

Historically , pivot tables according to wikipedia were created before the use of computers in statistics in the form of a table of the number of occurrences of cases, better known as the table of distribution of occurrences or tables of frequency.

In essence, by using a derived (Pivot) table, cross-amounts can be obtained, in fact, the number of pieces or the sum of certain specimens / samples. When we make it, ie. we perform this procedure and get new information, which we can arrange in almost every possible way, and even enter new mathematical formulas with which we make new calculations.

Home table data source

When we record some data in tables, for the creation of a derived table, it is desirable that they be in the form of a two-dimensional table. In the derived tables, numerical data are collected and textual ones are counted.

Example: Let’s say we have a record of all accounts in a business period, say from the beginning of the year, and we want to know how much we should have charged on those accounts for each month since the beginning of the year?

The initial two-dimensional data table might look like this:

Now you are already wondering what is two-dimensional, when there are 8 columns. For a more detailed acquaintance with this issue, you can see our text on tables in excel, or even better, the article on the inverse antipivot procedure , when you create an initial table from the derived table.

Creating or executing a table

If you have opened an excel spreadsheet , select Insert and the first Pivot Table button as in the following image.

This database contains a large amount of data, but as such does not say much. To make the data more useful, we need to aggregate it, group it, get totals, and thus answer some questions:

• What is the amount of the invoice in each branch, divided according to the type of invoice?
• How many accounts are opened in each branch, by type of account?
• Which branch opens the most accounts for new customers?

Select any cell in the worksheet, then select Insert / Table / PivotTable . The following dialog box will open:

In the Table / Range field, enter the range of cells in the starting table (select the table). If we use data from an external file, select the Use an external data source option . In the lower part of the dialog box, select the place for the derived table: existing or new worksheet.

After OK, Excel will create an empty derived table and open the PivoTable Field List pane , as shown in the following figure:

Arranging derived table elements – you can use the following procedures:

                              

• turn on the switch next to the item at the top of the pane, excel will set this field to one of the four fields at the bottom of the pane

• drag the field names (at the top) to the blank derived table on the worksheet
• drag field names to one of the four fields at the bottom of the Pivot Table Field List pane
• right-click the field name at the top of the Pivot Table Field List pane and select its location from the context menu.

This procedure creates a table, determines sums (or some other calculations) by columns and rows, automatic formatting, the order in which fields appear.

The most important terms of derived (Pivot) tables

• Column label – the fields that define the columns are placed in the table header as columns.
• Row labels – fields that define rows in a derived table
• Grand total – a row or column in which the total is displayed
• Subtotal – Subtotal – row or column with displayed subtotals
• Refresh – updating the totals after changing the source data
• Table filter – a field by which data is extracted
• Item – field elements – eg field is Account type, items are savings, current and giro account

Format or edit a pivot table

Now that the pivot table is done, pay attention to the new toolbar. Two parts, Analysis and Design , have appeared .

On the Analyze bar , you can format the data in the table in the Active Fieald section , e.g. cash amounts in two decimal places, etc. or update the billing information in the Data section of the Refresh button.

In the Design bar , in addition to the style of displaying the table, you can also select the elements to be displayed.

Data aggregation

We drag those columns whose values ​​we want to aggregate (group) and calculate, count, etc. to the Value pane .

By left-clicking on the column in the submenu, select Value field settings, you can select a mathematical function to process the data, e.g. add the amount with the SUM function , override the amounts with the COUNT function , calculate the mean value with the AVERAGE function , display the maximum value with the MAX function , etc.

Making calculation columns

In case you need to calculate new values ​​in the derived table, and such a column does not exist in the initial table. On the Analyze bar , you can format the data in the table in the Active Fieald section , e.g. cash amounts in two decimal places, etc. or to update the calculation data in the Data section of the Refresh button. An update is in progress.

Data grouping – stratum creation

If you want to group some rows of the table, right-click on one or as many values ​​in the first column as you want to group and select the Group option from the submenu .

Filter and slice data

In the Filters pane, we can drag the desired column that produces a new row above the pivot table, allowing the user to filter the results in the table.

A snapshot of the reporting table execution procedure

When we run the Excel program, we can access various templates and download instructions on how to work with pivot tables that we gave in the image. From the given example you will see a typical case when you can report a table with new aggregate data. Let’s say we have a record (table) of expenses of one family. What date, which member bought what and how much he spent.   

Sources

¹ Wikipedia article on the etymological meaning of the word aggregation. https://en.wiktionary.org/wiki/aggregatus#Latin

 

---

---