What is a Pivot Table? How to use it?

Using Pivot Table, we can group data and perform summary operations (totaling, ratios, historical comparison, conversion to graph). We can also summarize this process as converting rows to columns.

Pivot Table

Let's expand a little on the summary description above. I mentioned that we need a data input (source data) for the Pivot operation and that rows are converted to columns. In other words, we need to specify a column as a pivot (basis for grouping) from many columns. As a result of the process, a row is generated for each unique value based on the values in the specified column, and other rows are grouped and included as columns of this row. At the end of this process, a summary table is created. With a Pivot Table, tables that contain many data can be turned into simple and understandable summary tables. This makes it possible to compare average values and interpret data by grouping them. If we summarize this process as a list;

• Pivot Table enables easy summarization (grouping), interpretation, filtering, conditional formatting, sorting, and operations on large data tables.
• It allows us to take totals and subtotals of numerical data and perform operations on the data at the category and subcategory levels.
• We can adjust the level of detail by expanding or collapsing operations.
• It enables us to make comparisons (such as rows/columns) by creating different summaries.
• Pivot Table can be included in reporting processes and can be converted to graphs.
• It allows the use of different calculation functions (numerical, text, and date).

Using Pivot Table

I will explain the example process using Microsoft Excel and Google Sheets. Additionally, I will expand on the topic with separate articles on examples from Google Analytics, Data Studio, R, and Tableau. So, let's get started. You can download the table from the Excel Sample Data page for use as an example. To summarize the content of the table ¹:

The table has 7 columns and 43 rows, and the columns and first 5 rows look like the one shown above.

Basic Pivot Table (PivotTable) Operations

Here are some of the basic operations that we can perform on a Pivot Table in Excel and Google Sheets.

Grouping Data

We can group data in a Pivot Table by dragging a field into the Rows or Columns area and then right-clicking on one of the values and selecting Group from the context menu. We can then specify the group size and range.

Filtering Data

We can filter data in a Pivot Table by clicking on the drop-down arrow next to a field in the Rows or Columns area and then selecting the values that we want to include or exclude.

Sorting Data

We can sort data in a Pivot Table by clicking on the drop-down arrow next to a field in the Rows or Columns area and then selecting the sort order that we want.

Calculating Totals

We can calculate totals in a Pivot Table by dragging a field into the Values area and then selecting the calculation type that we want to use, such as sum, average, or count.

Changing Layout

We can change the layout of a Pivot Table by dragging fields between the Rows, Columns, and Values areas of the Pivot Table Fields pane.

These are just a few of the basic operations that we can perform on a Pivot Table in Excel. With Pivot Tables, we can quickly and easily summarize and analyze data in a way that is both powerful and flexible.

Microsoft Excel

With Microsoft Excel, we can quickly create a Pivot Table (under the name PivotTable). To do this, we can select the relevant table and follow the steps Insert > PivotTable. A recommended Pivot application can also be applied as an example in this section. The Pivot process will create two new tabs: PivotTable Analyze and Design. With these tabs, you can customize and manage the Pivot process as you wish.

I added the Region field to the columns area, the Item field to the rows area, and used the Total field as the value to create a summary.

As a result of this operation, I obtained the table image above. The Sum of Total and Column Labels appear as headings for the summary table in the Field Headers area. We can also apply data according to rows (Row Labels) and columns (Column Labels) using filter fields if we want. Let's expand our table a bit and include the Rep information as well.

From the Pivot Table we have obtained, we can see the total sales amounts for products by Rep and region, as well as the total sales by region. Of course, it is possible to make different comparisons and calculations as well. So let's make sure that the sales are evaluated as a percentage (%).

Google Sheets

To perform these operations on the Google Sheets service, you need to select the relevant spreadsheet and then follow the steps Data > Pivot Table. After this process, the Pivot table editor will open on the right side. You can also evaluate the Recommended options provided in this area. You should specify the Rows, Columns, Values and if necessary, Filters fields for your own table. As in the Excel example, I want to see the sales of Items by Region, and I specify the Rows field as Item and the Columns field as Region. I also choose to summarize the values based on Total (the option Show summary of can also be selected as % of grand total as in our other Excel example).

Compared to Microsoft Excel, Google Sheets offers simpler and more practical calculation and summarization processes. The calculation functions discussed in the "Summarization Process" section can also be applied in Google Sheets, but can be a topic for another article.

Things to Consider

After a simple pivot table example created with both Excel and E-Tablolar, I would like to emphasize some points.

1. Column names (headers) should exist (otherwise, a warning message will appear) and these names should explain the data in the relevant column as clearly as possible.
2. It is useful to consider how to convert this data into summary tables during the process of creating the source table. Therefore, creating detailed columns (fields) will increase the number of questions that can be asked based on the data and thus allow you to gain new insights.
3. Columns should always have the same data type. Type differences, especially in key columns used for grouping in the summary process, will cause operations to be performed incorrectly.
4. Standardization is important in data entry. For example, the expression E-mail should not be used in another row as E-mail, and the definitions should remain as consistent as possible. This applies to the use of decimal points and commas in numerical values as well.

That's it for our operations for now. I will expand on the topic of pivot tables with different applications. There are quite comprehensive and understandable explanations on the Microsoft Office > Excel Help and Training page². Additionally, the Google Document Editors Help page contains some basic information about pivot table operations in the E-Tablolar application³. I recommend reviewing both sources.

Conclusion

In conclusion, Pivot Table is a powerful tool that can be used for summarizing and analyzing large amounts of data in a way that is both efficient and flexible. With basic operations such as grouping, filtering, sorting, and calculating totals, it is possible to turn complex data tables into simple and understandable summary tables. By paying attention to some key points, such as standardizing data entry and ensuring consistent column names and data types, the accuracy and effectiveness of Pivot Tables can be further enhanced. Overall, Pivot Table is a valuable tool for anyone working with data and can provide valuable insights and new perspectives on complex datasets.