Tutorials References Exercises Videos Menu
Free Website Get Certified Upgrade

Excel Tutorial

Excel HOME Excel Introduction Excel Get Started Excel Overview Excel Syntax Excel Ranges Excel Fill Excel Move Cells Excel Add Cells Excel Delete Cells Excel Undo Redo Excel Formulas Excel Relative Reference Excel Absolute Reference Excel Arithmetic Operators Excel Parentheses Excel Functions

Excel Formatting

Excel Formatting Excel Format Painter Excel Format Colors Excel Format Fonts Excel Format Borders Excel Format Numbers Excel Format Grids Excel Format Settings

Excel Data Analysis

Excel Sort Excel Filter Excel Tables Excel Conditional Format Excel Highlight Cell Rules Excel Top Bottom Rules Excel Data Bars Excel Color Scales Excel Icon Sets Excel Manage Rules (CF) Excel Charts

Table Pivot

Table Pivot Intro

Excel Case

Case: Poke Mart Case: Poke Mart, Styling

Excel Functions

AND AVERAGE AVERAGEIF AVERAGEIFS CONCAT COUNT COUNTA COUNTBLANK COUNTIF COUNTIFS IF IFS LEFT LOWER MAX MEDIAN MIN MODE NPV OR RAND RIGHT STDEV.P STDEV.S SUM SUMIF SUMIFS TRIM VLOOKUP XOR

Excel How To

Convert Time to Seconds Difference Between Times NPV (Net Present Value) Remove Duplicates

Guided Projects

Introduction to Excel Learn Data Calculations Learn Data Visualization Learn to Create a Budget Learn to Create a Timeline Learn to Style in Excel

Excel Examples

Excel Exercises Excel Certificate

Excel References

Excel Keyboard Shortcuts


Introduction Excel PivotTable


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


    W3Schools Excel Certificate
  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.