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. Columns
  2. 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.

    Pivot Table Column Highlighted

  3. Rows
  4. 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.

    Pivot Table Row Highlighted

  5. Filters
  6. 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.

    Pivot Table Filter Highlighted

    Filter view:

    Pivot Table Filter view

  7. Values
  8. 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.

    Pivot Table Value Range Highlighted

    Values settings view:

    You can change the name and settings of the values.

    Pivot Value settings view

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.

Fields and Layout

  1. Fields
  2. 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.

    Fields in panel

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

    Fields properties

  3. Layout
  4. 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. Filters
    2. Rows
    3. Columns
    4. Values

    Drag and drop to display data

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

    Added SP def to table

    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).

    Change settings layout fields

    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.