Pivot tables are among the most useful features Excel has to offer. With the help of pivot tables, even complex data sets can be sorted quickly, leading to useful insights. This tutorial explains step by step the individual ranges, filtering options, sorting, grouping that will help you create the perfect pivot table.
The example table contains the columns order number, product, price, brand and a date. For example, this table could stand for a car dealership and list the sales.
List a pivot table
We click the Insert tab in the Tables area and click the PivotTable button.
Note: In order to create a pivot table, each column in the data set must have a heading. In addition, the headings must be unique and must not occur more than once.
The Create Pivot Table dialog box appears. Excel automatically selects the columns and rows in your worksheet.
Note: It is not necessary to reduce the size of the area, because we can hide irrelevant information later. If you want to select only a certain range of basic data for the pivot table, click on the button further to the right (see green arrow)
We select the New worksheet option in the “Specify where to place the PivotTable report” option. This is recommended because a pivot table can take on large proportions.
Then we click OK.
The pivot workspaces appear, which can be filled with content via drag-and-drop, i.e. by dragging and releasing with the left mouse button.
For our example, we fill the relevant areas as follows:
Drag the Brand field to the ROWs area.
The Price field is dragged to the VALUES area.
The result looks like this: All data in the table is now clearly summarized. The individual car brands are broken down into rows and the sum of all prices is added, which in the case of a car dealership reflects the total sales.
We can immediately see that the highest sales were made with Audis. This is closely followed by Mercedes and, to a lesser extent, Volkswagen.
In the next step we will look deeper into the data and additionally look at the individual models. In the PivotTable Fields area, we will drag the Product field to the Rows area under the Brand entry, as in the screen shot in the example.
and this is the result:
Pivot tables offer filtering options to quickly narrow down the view to a specific data base. In our example, we drag the Brand field to the Filter area to activate the filter option.
We click on the filter button located above the pivot table.
Now the brand Mercedes is selected and confirmed with OK.
Now our table shows only the products of the selected brand.
Next, we would like to sort the individual models by sales. We do not need the respective brand for this. We arrange the fields as follows: Product in the rows area, Price in the Total area.
this is what the result looks like:
it is still a bit confusing. Therefore, we will sort all models based on total sales. We click with the right mouse button on a cell in the column Sum of Price.
A context menu appears. We select the Sort option and click Sort by size (descending).
Now the numbers are sorted. We see that the SLK model brings in significantly more sales than the A6. Since it is mainly expensive cars that bring in the most sales, it is reasonable to assume that expensive cars are sold in a similar number of units as inexpensive cars. We will check this in the next chapter.
Change calculations of the fields
We click in the PivotTable Fields area on the Sum of Price field with the left mouse button on the small black triangle.
A context menu appears and we select the Value field settings option.
In the Value Field Settings dialog box, we click Number. Finally, we click OK.
This is how the result looks in the selection area:
The result is still unsorted. As described in the chapter above we sort the column with a right click and the option Sort by size (descending).
Now we see an overview of the sales frequencies. This is the great strength of pivot tables: to quickly summarize even large data sets and expand them as desired with just a few clicks.
A two-dimensional pivot table
Columns and rows can be quickly filled with values. For this example, we are interested in sales by brand in the respective months of a year. For this reason, we proceed as follows:
Drag the Mark field to the COLUMNS area.
Drag the Months field to the ROW area.
The Price field is dragged to the VALUES area.
The result looks like this: in rows the months of a year are displayed and in columns the marks are broken up.
Group date values
In order to make the results clearer, our aim is to summarize the individual months into quarters.
The first quarter of the year includes the months of January, February and March. A year is divided into four quarters.
We right-click on the column with the date values and select the Group option.
The Grouping dialog box appears. We set the marker to Quarters and confirm with OK.
And this is how our table looks now: