Elements and filtering of Pivot tables

Pivot tables have built-in options for applying autofilter to display data relative to rows, columns, and filters. By opening the autofilter menu, data sorting can be performed according to the specified reporting dimension, or it can narrow down the set of displayed values in the table by setting the conditions that relate to the description or value of the data displayed in the report. More about filtering Pivot tables can be found in the text that follows …

If we go to the Analyze ribbon, we’ll see a field called Pivot Table Name in its upper left corner. If it is the first Pivot table we created, the value of this field will be “PivotTable1”. Accordingly, each new Pivot table we make will get a name with another ordering number after the text “PivotTable”. By clicking on the name field, we can change the name of the Pivot table and choose another name that suits us. If we click on an row or column within the Pivot table, to the right of the name we will see the Active Field text, under which the name of the field on which we are currently located is located. Similar to the previous case, we can change the name of the field and we will see that the name will be changed at the same time in the panel with the fields on the right side of the Pivot table.

If we have added dimension to Rows, there will be a Row Labels field in the Pivot table header. That is, if we added them to Columns, this field will be called Column Labels. Clicking on the icon marked with the reverse triangle, to the right of the title, will open the menu for sorting and filtering data in rows or columns. Here, standard filtering options are opened, such as those that we encountered using the AutoFilter option. First, we can see options for sorting in a ascending or descending order, as well as the More Sort Options. This option allows you to specify if we are sorting by labels or values. Also, in the Label Filters sub-menu, we can set specific filters for the values ​​of the text describing the dimension in rows, as well as the Value Filters, which indicates the value filters that are in the table itself. In the fields below, it is possible to mark all, one or more values, as well as within the Search box, manually enter the search criteria for the values ​​to be displayed in the table.00075-1

When we want to add more dimensions in rows or columns, they will be sorted by hierarchies. By moving the name of the dimension in the Rows (Columns) field up or down, we determine the hierarchical level of the given in relation to other fields. When there are several hierarchical levels, the higher levels will beside their name also get an icon marked “+” or “-“. The “-” indicates that the subordinate members of the hierarchy are shown (expanded tree), and the “+” sign is not (colapsed tree). That is, clicking on the “+” sign next to the name in the table will expand the table and lower levels of the hierarchy will be displayed. Displaying or hiding the hierarchical levels can also be achieved from the Analyze ribbon, by clicking Expand Field or Colapse Field.

Excel offers the ability to group multiple rows or columns into one. For example, if we made a Pivot table by dragging the AMOUNT value, and in the ITEM NAME, if we mark several items and from the Analyze ribbon select Group Selection we will create a group of items that visually resemble a new hierarchy and will be associated temporarily the name “Group1” that we can change according to the reporting needs. The group can be expanded or colapsed, like any other superior member of the hierarchy. In general, the behavior of the group resembles the usual grouping of rows or columns in Excel. By clicking the Ungroup option, the rows or columns are ungrouped.

00075-2

Pivot table can be formatted like traditional Excel tables. So, we can change the cell formatting, i.e. way the number, type, style and font size, font color, cell background color, etc. are displayed. The only problem is that after removing and re-adding a dimension to the Row or Column, formatting disappears. We can change rows or column headings, as well as other elements in the Pivot table header. Simply, they behave like any other Excel cells.