Introduction Excel PivotTable

In the realm of Excel data analysis, PivotTables stand out as a powerful tool for organizing and gaining insights from your data. This feature equips you with the ability to manipulate data sets, add or remove values, perform calculations, and effectively filter and sort information to suit your analytical needs.

PivotTable essentially serves as a structural scaffold, enabling you to efficiently arrange and comprehend extensive data sets. For optimal utilization of this tool, your data must be formatted in a tabular layout, featuring rows and columns that delineate the information cohesively.

PivotTable

PivotTable is a functionality in Excel which helps you organize and analyze data.

It lets you add and remove values, perform calculations, and to filter and sort data sets.

PivotTable helps you structure and organize data to understand large data sets.

The data that you use needs to be in tabular format.

The tabular form is data in a table format (rows and columns).

How a PivotTable Works

PivotTables have four main components:

1. 1. Columns

Columns are vertical tabular data.

The column includes the unique header, which is on the top.

The header defines which data you are seeing listed downwards.

In this example, D5(Sum of Attack) is the header.

D6(110), D7(100), D8(50), D9(73), and so on are the data.

1. 1. Rows

Rows are horizontal tabular data.

Data in the same row are related.

In this example, A8(Alakazam) is the Pokemon name.

B8(500), C8(55), D8(50), E8(45) represents the pokemons stats.

The type of stats is read in the header in the columns.

1. 1. Filters

Filters are used to select what data you see.

In this example, there are two filters enabled: Generation and Type 1.

The filters are set to Generation (1) and Type (Psychic).

We will only see Generation 1 pokemon that is Type 1, Psychic.

All pokemon in the table below the filter are of this generation and type.

Filter view:

1. 1. Values

Values define how you present the data.

You can define how you Summarize and Show values.

In this example, values are defined for the range B5:E5.

The range B5:E5 has all the same value setting: Sum

The Sum is summarized in the range B14:E14.

Values settings view:

You can change the name and settings of the values.

Fields and layout

The TablePivot is displayed how by your settings.

The PivotTable Fields panel is used to change how you see the data.

The settings can be separated in two: Fields and Layout.

1. 1. Fields

The checkboxes can be selected or unselected to display or change the property of the data.

In this example, the checkbox for Speed is selected.

Speed is now displayed in the table.

You can click the downwards arrow to change how the data is presented.

1. 1. Layout

Drag and drop fields to the boxes to the right to display data in the table.

You can drag them to the four different boxes that we mentioned earlier (four main components):

1. 1. 1. Filters
      2. Rows
      3. Columns
      4. Values

In this example, we will drag and drop Sp. def to Values.

Sp. Def is now displayed in the table.

The settings of the fields that you have moved to the right side can be changed.

Click the downwards arrow to access the settings.

This is possible in the four areas (Filters, rows, columns and, values).

Chapter summary

TablePivot can be used both simple and advanced.

It can be set up in many different ways.

You can configure on many different layers cross filters, rows, columns and values.

How you set it up depends on your needs and how you want to present the data.