Search functions: the pivot table in Excel or spreadsheets

14/07/26 •0•0

The pivot table in Excel automates everything we have learned to date with just a few clicks

Scritto daLeon

Tradotto daEditorial

Rivisto daEditorial

The Pivot Table

We call a Pivot Table the table automatically created by Excel or Sheets. Its purpose is to provide quick and accurate information about your data. The table has some characteristics:

> It is possible to divide items into 4 categories: ROWS, COLUMNS, VALUES, and FILTERS

> When adding an item to one of the categories, the table is automatically updated

To create a pivot table, simply

1. select your data

2. Click on "Data"

3. Click on "Pivot Table"

ROWS and COLUMNS in the table

Items are added in rows and columns. You will only add the variables that you would like to use as criteria in your study. To add an item, go to the "rows" or "columns" section and click add, as in the example below:

Notice that the rows will be formed according to the item you add; in the example, it was with the suppliers' names. The same applies to the columns, which were added automatically with each possible time period.

So now we have a table with SUPPLIER by TIME, the data you would like to work with. The example table can be viewed here.

VALUES in the table

The values go inside the table. While rows and columns are ways to categorize, the values will actually be what is studied.

Click "add" to add a value and then see your table being filled in. Notice that the chosen value will be grouped. In the case of the example below, "profit" becomes a value that is being grouped by supplier and by time:

Also notice from the example that the value was grouped by "sum", that is, all the values corresponding to that supplier and that time period were added together. This does not make much sense because the table refers to the number of products that each supplier sells you and the profit per product in this case; if there are more products, when added together there will be more profit. To make sense, we will change from "sum" to "average", which is "average" in English.

FILTER

The filter has this name because it "filters" the values, that is, removes values according to some criterion. To add a filter, simply click "add". In this example, we will add a profit filter, showing only profit above 5 reais:

When adding profit, the filter will default to "showing all items", in other words, there is no filter. Click this button to add a filter. It will open a new window; in this window you will have the option to "filter by values" and "filter by condition". To filter by values, you click on the values one by one that you would like to remove. Filtering by condition allows you to create a rule for what to filter.