Exploring Data with Pivot Tables

1
Example 2.11
Exploring Data with Pivot Tables
2
ACTORS.XLS

• Recall that this data set contains information on
  66 movie stars, including their Gender, Domestic
  Gross, Foreign Gross and Salary.
• Female actresses claim they are being underpaid
  relative to male actors. Do the data support this
  claim?

3
Pivot Tables

• Pivot tables are one of Excels most powerful
  tools. They provide an incredible amount of of
  useful information about a data set.
• Pivot tables allow us to slice and dice the
  data in a variety of ways.That I, they break the
  data down into subpopulations.
• Statisticians often refer to the resulting tables
  as contingency tables or crosstabs.

4
Creating a Pivot Table

• The first step in answering the question of
  whether the data support the claim by the
  actresses is to determine the male-female
  breakdown.
• Although there are other ways to do this we will
  use a pivot table to determine the breakdown.
• The following steps show how it can be done.

5
Creating a Pivot Table -- continued

• Position the cursor in the data range.
• Select Data/PivotTable Report menu item. This
  step takes you to a four step Pivot Table Wizard.
• In the first step, click Next to indicate that
  the pivot table data are in an Excel spreadsheet.
• In the second step, specify the range of the data
  set. If you placed cursor in the data range this
  should be automatically set.

6
Creating a Pivot Table -- continued

• The third step is the crucial one, where you
  specify the variables you want in the pivot
  table.
• To put any field in any of the four areas (page,
  row, column or data), just click on the
  variables button and drag it to the appropriate
  area.
• The areas allow you to break the data down by the
  categories of the variables in these areas.
• For this example, drag Gender to the row are and
  Gender to the data area.
• The last step of the Wizard allows you to specify
  the location of the pivot table. If you leave
  this blank it will place the table on a new
  sheet.

7
Creating a Pivot Table -- continued

• The table shows there are 66 stars 48 are male
  and 18 are female.
• We still dont know whether women are underpaid
  so we will create another pivot table to examine
  the distribution of salaries, classified by
  gender.

8
Creating a Pivot Table -- continued

• The following steps accomplish this
• Place cursor in data range.
• Select Data/PivotTable menu item and click on
  Next in first two steps.
• Drag the salary variable to the row area, drag
  Gender to the column area, drag Gender to the
  data area, and click on Next.
• Click on Finish to accept the defaults on the
  final screen.

9
Resulting Pivot Table
10
Creating a Pivot Table -- continued

• The pivot table shows too much detail. To solve
  this problem we can group the salaries in various
  ranges. To do this follow these steps
• Click in the Salary column.
• Click right arrow on PivotTable toolbar.
• Specify that the groups should begin with 2, end
  at 20 and use increments of 3.
• The resulting table should look as follows after
  expressing the counts as percentages and creating
  a 3-D column-chart.

11
Modified Pivot Table
12
Analyzing the Table

• The pivot table makes it clear that over half the
  women are in the lowest salary category, whereas
  only 19 of the men are in this category.
• Also, no women are in the highest two salary
  categories, whereas 23 of the men are in these
  categories

13
More Pivot Tables

• Another way to compare the salaries would be to
  compare average salaries by gender. This can also
  be done by using a pivot table.
• To complete this table
• Proceed as before to get to the third step of the
  Pivot Table Wizard.
• Drag Gender to the row area and Salary to the
  data area. Note that the data area says sum of
  salary. When a numerical variable is dragged to
  the data area the default is to show the sum.

14
More Pivot Tables -- continued

• Double click the Sum of Salary. In the Summary by
  box click on Average. Then click OK and Finish.

15
More Pivot Table Analysis

• The analysis so far appears to indicate that the
  movie industry discriminates against women.
• However, it is possible that women are paid less
  because movies with female leads gross less money
  than movies with male leads.
• To analyze this further, we look at the average
  salary of men and women for each domestic gross
  level.

16
Another Pivot Table

• This pivot table should be able to be done on
  your own.
• If you need help here are the basic steps
• Drag DomesticGross to the row area
• Gender to the column area
• Salary to the data area - summarize by average
• Set the domestic gross group values in increments
  of 20.

17
Another Pivot Table -- continued
18
Another Pivot Table -- continued

• Men average more than women in the two lowest
  domestic gross categories, but only barely in the
  third. Beyond the third category, it is hard to
  tell because no females were leads in real
  blockbusters.
• Thus, we can now say with more assurance that the
  industry does appear to discriminate against
  women in terms of salary.