EIN 4905/ESI 6912 Decision Support Systems Excel

1
Spreadsheet-Based Decision Support Systems
Chapter 6 Pivot Tables
Prof. Name
name_at_email.com Position
[phone] University Name
2
Overview

• 6.1 Introduction
• 6.2 Pivot Tables
• 6.3 Further Modifications
• 6.4 Pivot Charts
• 6.5 Summary

3
Introduction

• Creating a pivot table
• Filtering for data using pivot table features
• Customizing a pivot table using several
  structural and formatting options
• Performing advance filters using grouping,
  calculated fields, and the GETPIVOTDATA function
• Transforming a pivot table into a pivot chart
• Filtering data using a pivot chart

4
Pivot Tables

• Terminology
• Creating a Pivot Table

5
Pivot Tables

• Transform large amounts of data from a table or
  database into an organized summary report
• Ability to rotate and reorganize the row and
  column headings from your original database into
  this new table
• Ability to filter and sort data as desired
• Allows for easy searching of data

6
Terminology

• Fields are categories of data (these may usually
  be row or column headings in a table)
• Row Fields show each value, or item, in the field
  as a row
• Column Fields show each value as a column heading

7
Terminology (cont)

• Data Area is the main area of the table where
  comparative values are shown
• Grand Totals and Subtotals are sum calculations
  that appear at the end of relative rows or
  columns
• Page Field is a larger category which can group
  all of the data in the table

8
Creating a Pivot Table

• Data gt Pivot Table and Pivot Chart Report
• Three simple steps
• Step 1 Data Location
• Where is the data to analyze?
• Pivot table or pivot chart?
• Step 2 Data Source
• Select range of cells
• Select file using Browse
• Step 3 Creating the Pivot Table
• Location
• Layout
• Options

9
Figure 6.1

• The table contains the Costs for varying
  Maximum Weights and the number of Days to
  Arrive for two different Shipping Companies.

10
Step 1

• The data is in a Microsoft Excel list
• We choose to create a Pivot Table

11
Step 2

• We select our data from the spreadsheet

12
Step 3

• The location will be as a new worksheet
• Must now organize Layout

13
Figure 6.7(a)

• Field buttons are the column titles from the data
  table
• Each field button should be dragged to one of the
  fields
• Row
• Column
• Data area
• Page

14
Figure 6.7(b)

• Row Fields Number of Workers, Machine Number
• Column Fields Shift
• Data Area Units Produced
• Page Field none

15
Figure 6.8

• The pivot table is then created

16
Further Modifications

• An Example
• Pivot Table Toolbar and Options
• Grouping
• Calculated Fields and Items
• GETPIVOTDATA Function

17
An Example (Figure 6.9)

• Consider a data table used for Quality Control
• The number of operators, machine number, and
  number of units produced are recorded for three
  shifts

18
Calculated Fields

• Pivot Table Toolbar gt Pivot Table gt Formulas gt
  Calculated Field to create a new field
• Pivot Table Toolbar gt Field Settings to use an
  Excel-defined calculated field

19
Pivot Table Fields List

• The Pivot Table Fields List can be used to
  further modify the pivot table layout after the
  pivot table has been created.

20
Field Settings

• The default field setting is Sum this affects
  the Grand Total and Sub Total fields

21
Field Settings (cont)

• These field settings can be changed
• For example, changing the setting
• to Max will yield different values
• for the Grand Total and Sub Total
• fields

22
Field Settings (cont)

• These field settings can be applied to Sub Totals
  by selecting a particular row or column first

23
Pivot Table Toolbar

• With the Pivot Table Toolbar, we can easily
  modify the pivot table after it has been created.

24
Pivot Table Options

• Choose Options from Step 3 or right-click on
  completed Pivot Table and choose Table Options
• Name
• Format Options
• Grand Totals columns or rows
• Page Layout
• Formatting
• Error Values and Empty Cells
• Data Options
• Save data

25
Grouping

• Grouping Items in a Row or Column Field allows
  you to further manipulate how you view or search
  for data in your pivot table.
• To group field values, select the field and
  choose Group and Show Detail from the Pivot Table
  drop-down options, or right-click on the table.

26
Figures 6.27 and 6.28
27
Calculated Fields and Items

• To create a Calculated Field or Calculated Item,
  click on Pivot Table gt Formulas gt Calculated
  Field (or Calculated Item) from the toolbar
  drop-down options.
• You can define a formula associated with the
  creation of a new field or item.

28
Figure 6.30
29
GETPIVOTDATA

• The GETPIVOTDATA function extracts a particular
  set of data based values specified for each Row
  and Column Field.
• GETPIVOTDATA(desired_field, range_of_desired_data
  , field1, item1, )
• The desired_field is the field which contains the
  value you are searching for.
• The range_of_desired_data is the range in the
  pivot table which contains this field.
• The remaining field and item values allow you to
  refine your search if desired.

30
Figure 6.31
31
Pivot Charts

• Pivot Table Toolbar gt Pivot Charts icon
• Chart is created from Pivot Table instead of
  initial data table
• Data gt Pivot Table and Pivot Chart Report
• Step 1 Pivot Chart
• Pivot Chart created from data table
• Right-click on current Pivot Table and select
  Pivot Chart from list of options
• Pivot Chart has same filtering options as Pivot
  Table

32
Figure 6.32

• Initial Pivot Chart

33
Figures 6.33 and 6.34

• Filtered Pivot Chart

34
Summary

• Pivot tables transform large amounts of data from
  a table or database into an organized summary
  report.
• The three steps to create a pivot table are
  Specify Location, Select Data, and Create Table
  Layout with specified options.
• Use Grouping to create intervals in field values.
• You can create Calculated Fields or Calculated
  Items to further analyze the data in your table.
• GETPIVOTDATA searches for data in a pivot table
  using field value criteria.
• Pivot Charts use pivot tables as their Source
  Data so that filtering options are transferred to
  the chart as well.

35
Additional Links

• (place links here)