Scribd Logo
Upload

Welcome to Scribd!

  • 144 views

    Pivot Table in Excel 2007 Training

    Uploaded by

    Cristina Nakajima Nakano
    This document provides instructions for creating and modifying pivot tables and pivot charts in Excel 2007. It begins with an overview of what pivot tables are and how they can be used to summarize and analyze data. It then walks through how to create a basic pivot table with examples. The document also demonstrates how to modify pivot tables by adding filters, showing percentages, and grouping items. Finally, it shows how to create a pivot chart based on a pivot table and customize the chart.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd

    Pivot Table in Excel 2007 Training

    Uploaded by

    Cristina Nakajima Nakano
    144 views35 pages
    This document provides instructions for creating and modifying pivot tables and pivot charts in Excel 2007. It begins with an overview of what pivot tables are and how they can be used to summarize and analyze data. It then walks through how to create a basic pivot table with examples. The document also demonstrates how to modify pivot tables by adding filters, showing percentages, and grouping items. Finally, it shows how to create a pivot chart based on a pivot table and customize the chart.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides instructions for creating and modifying pivot tables and pivot charts in Excel 2007. It begins with an overview of what pivot tables are and how they can be used to summarize and analyze data. It then walks through how to create a basic pivot table with examples. The document also demonstrates how to modify pivot tables by adding filters, showing percentages, and grouping items. Finally, it shows how to create a pivot chart based on a pivot table and customize the chart.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    144 views35 pages

    Pivot Table in Excel 2007 Training

    Uploaded by

    Cristina Nakajima Nakano
    This document provides instructions for creating and modifying pivot tables and pivot charts in Excel 2007. It begins with an overview of what pivot tables are and how they can be used to summarize and analyze data. It then walks through how to create a basic pivot table with examples. The document also demonstrates how to modify pivot tables by adding filters, showing percentages, and grouping items. Finally, it shows how to create a pivot chart based on a pivot table and customize the chart.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 35

    Pivot Table in Excel 2007

    Thomas Glassenhart

    Agenda

    Part 1: What is a Pivot Table Part 2: How to create a Pivot Table Part 3: Modifying a Pivot Table

    Using Filters
    Percentages in Pivot Tables

    Part 4: Grouping items in a Pivot Table

    Grouping of Non-Numeric items


    Grouping of Numeric Items

    Part 5: How to create a Pivot Chart Q&A - More Examples?

    Part 1

    What is a Pivot Table

    What is a Pivot Table

    Pivot Tables are an excellent means to organize and summarize long and detailed data lists Pivot Tables help to reveal patterns, relations and trends which are often hidden in hundreds or thousands of data rows and to aggregate the data to a level which can be presented and easily understood Due to the flexibility of Pivot Tables, it is possible to analyze different aspects of the same data set within the same table and show the results in a table or graph Pivot Tables can be used, e.g. to analyse the results of Volumes, Model Mix or Specs Into Model Mix queries Pivot Tables are also useful to analyse CRM data or any other data sets

    Example

    Data List 7345 rows of data

    Pivot Table Summarized info in 62 lines (created in ca. 20 seconds)

    Part 2 How to create a Pivot Table Example Volumes Analysis

    How to create a Pivot Table


    Step 1: Use a data list, e.g. the results of a Volumes / Model Mix / Specs Into Model Mix Query that contain the Identification items (e.g. Make, Model, Fuel Type, Body Style, HP etc.) and the Volumes period(s) you want to use in your analysis. Note: The basis of a Pivot Table has to be a list of data rows with headers for each column

    Data Rows

    Header for each column

    How to create a Pivot Table


    Step 2:
    Place

    the cursor in one cell within the data area. Insert/Pivot Table from the Excel Toolbar

    Select

    How to create a Pivot Table


    Step 3:
    Excel

    selects automatically the whole data list (alternatively, modify the Table Range) It is recommended to place the Pivot Table in a new worksheet (default setting)

    Click OK to create the Pivot Table in a new Worksheet

    How to create a Pivot Table


    Step 4: In the new worksheet the fields in the Pivot Table Field List can be a moved to the areas of the Pivot Table and arranged as required (Drag and Drop or right click on field)

    Pivot Table Field List Pivot Table

    Pivot Table Areas

    How to create a basic Pivot Table The Areas in a Pivot Table

    Report Filter: Can be used to filter data without displaying the individual values of a field

    Column Label: Defines the Column Headers

    Row Labels: Defines the Row Headers

    Values: Shows numbers only! In Volumes analysis, move volumes period(s) here. Non-numeric fields will be shown as Count

    How to create a Pivot Table


    Example

    How to create a Pivot Table


    The

    fields in the Pivot table can be moved, combined and re-arranged at any time

    How to create a Pivot Table


    The

    fields in the Pivot table can be moved, combined and re-arranged at any time

    Live Demonstration

    Exercise 1 Creating a Pivot Table


    A) Open the Excel Workbook Pivot Table Exercise.xlsx B) From worksheet Data Shet create a Pivot Table showing the Body Types for each Model (compare below)

    C) Change the Pivot Table, so you can see the Fuel Types for each Model

    Part 3

    Modifying a Pivot Table

    How to Filter Data in Pivot Table


    The It

    fields in the Pivot table can be filtered to reduce the amount of data

    is possible to filter multiple fields at a time

    Any field can be filtered in the Pivot Table or within the Field List

    Percentages in Pivot Tables


    The Value field in a pivot table is always shown as a number The number can be shown normal or as a percentage
    Right click into the Value area of the Pivot Table and select Value Field Settings form the context menu

    Percentages in Pivot Tables


    The Value field in a pivot table is always shown as a number The number can be shown normal or as a percentage

    Percentages in Pivot Tables


    The Value field in a pivot table is always shown as a number The number can be shown normal or as a percentage

    Live Demonstration

    Exercise 2 Percentages and Filters


    A) Use the Pivot table of Exercise 1 and show the Fuel Type Mix for each Model in %-age!

    B) Exclude now from the table all wagons!

    Part 4

    Grouping Items in a Pivot Table

    Grouping Items

    Sometimes it can be useful to group different items and show them together under a new Field name; e.g. in order to show one model with its name and to summarize all others

    It is also possible to automatically group numeric items and create equal bands (e.g. HP or Prices)

    Grouping Items

    An additional field is created and added to the Pivot Table Select all items that should be grouped, right click and click Group in the context menu

    Grouping Items

    Remove the field that is not required

    Grouping Items Field names and items can be renamed


    Click the field or item that should be renamed and type in the new text in the formula line

    Grouping Numeric Items


    It the item is numeric, Excel offers to create groups automatically in equal steps
    1. Arrange a table that contains a numeric item as Row or Column field 2. Right-click into the area that contains the are that should be grouped and select Group 3. In the new dialogue enter the required values

    Grouping Numeric Items


    It the item is numeric, Excel offers to create groups automatically in equal steps

    Live Demonstration

    Note: If your data list contains Blanks the numeric item, Excel may not group it automatically

    Exercise 3 Grouping data


    A) Use the Pivot Table you created in the previous example and show the Fuel Typ Mix if the Clio vs. the other models. Group the other models

    B) Show HP-Bands comparing the Clio vs. Others. Use 10 HP steps.

    Part 5

    How to create a Pivot Chart

    How to create a Pivot Chart

    Every Pivot Table can be easily shown as a Chart and is a powerful and flexible tool to visualize data

    Once a Pivot Chart has been created, it can still be modified in the same way as a Pivot Table

    How to create a Pivot Chart


    Use a Pivot Table as basis for the Pivot Chart

    Click in the pivot table and select Pivot Chart from Options ribbon within the PivotTable Tools menu.

    Select the chart Options in the dialogue

    It is also possible to create a chart by using the F11-key

    How to create a Pivot Chart


    If the Pivot Chart is created with the Chart icon from the Excel ribbon, it is placed in the same worksheet as the Pivot table. When it is created with F11, it is in a new worksheet

    Right-click chart and use Move-Chart from the context menu to move the Chart to a new worksheet

    How to create a Pivot Chart

    Select the respective Ribbons in the PivotChart Tools to edit, format and further analyse the data

    Live Demonstration

    Exercise 4 Pivot Charts


    A) Use the Pivot Table you created in the previous example and create a Pivot Chart

    B) Modify the Chart by rearranging fields!

    You might also like