SPSS Library
An Introduction to SPSS Pivot Tables


This page was adapted from a web page at the SPSS web page.  We thank SPSS for their permission to adapt and distribute this page via our web site.


What is a pivot table?

A pivot table is a table capable of dynamically displaying and rearranging multiple dimensions. There are three display areas of a pivot table: rows, columns, and layers. The dimensions of any table can be easily moved from row to column to layer. The ability to position multiple dimensions in each of these areas increases the number of data dimensions that can be viewed.

SPSS adds a Title, Caption, and Footnotes to the general idea of a pivot table. The parts of an SPSS pivot table are shown in Figure 1.

Pivot table
Figure 1: SPSS 7.0 Pivot Table

This table contains four dimensions, two in the rows (Sex and Tumor) one in the columns (Statistics) and one in the layer (the dimension name is Variable. The current value showing in this dimension is "Week of death"). The layer helps to reduce the dimensionality by displaying one slice of the table at a time. In this case, there are three visible dimensions being shown for the variable called "Week of Death". Stepping through the layer values will show the same three dimensions for other variables that are present in the table.

What are the advantages of pivot tables?

How does SPSS use pivot tables?

There is not a single new procedure added to SPSS that creates a pivot table. Rather, the current SPSS procedures are being updated so that all of their output is displayed as a series of pivot tables and charts. The output from all procedures is stored in a single document, which includes text, pivot tables, and charts. SPSS 7.0 has added a new Output Navigator to organize and display the contents of this document. The Output Navigator displays both the document and an outline view, which makes it easier to find and organize the output. An example of the Output Navigator is shown in Figure 2.

Pivot table
Figure 2: SPSS 7.0 Output Navigator

Procedures planned for upgrade in the first release of SPSS 7.0 are: Anova, Correlations, Crosstabs, Descriptives, Examine, Frequencies, GLM, Nonpar Correlations, Nonparametric Tests, Oneway, Regression, T-Tests, Tables, Means, and a new case reporting procedure called Summarize. In subsequent releases other procedures will be added to this list. Customizing the Format of Pivot Tables

In addition to the ability to rearrange the dimensions in a table, the SPSS pivot table can be customized to fit the look the user needs. There are a variety of ways to change the look of a pivot table.

Portability of pivot tables

All pivot tables are created as OLE 2.0 objects. This allows them to be displayed and edited in any application that is an OLE 2.0 container. For example the output from SPSS can easily be dragged into most popular word processor (such as Microsoft Word for Windows), and further edited directly inside the word processor document when desired. There is no difference in the way the user edits a table in SPSS or in any other OLE 2.0 container.

Example

To demonstrate a pivot table and the advantages of rearranging multidimensional output, a data set collected to study FD&C Red Dye #40 will be used. Mice were fed one of four different diets: a control group, which did not receive any red dye, and groups that got either a low, medium, or high dose of red dye #40. During the two year study, each mouse that died was autopsied to determine if a tumor was present. Of the original 400 mice, 122 died during the two year period. The data presented below is that of those mice that died during the study.

This data was originally analyzed in Lagakos and Mosteller (1981) and later published in Andrews and Herzberg (1985). Default Pivot Table

Figure 3 shows the original table from the SPSS Crosstabs procedure of Tumor by Dose controlling for Sex. Both counts and percents based on Tumor were requested for this table. After the output was produced, a caption was added to the table.

Pivot table
Figure 3: Pivot Table from Crosstabs Procedure

This table has four dimensions: Sex (Male, Female), Tumor (Absent, Present, and Total), and Statistics (Count, % of Tumor) in the rows and Dose (Control, Low, Medium, High and Total) in the columns.


Move Statistics

If the desire is to compare tumor percents for different dose groups by sex, one might start by moving the innermost row dimension (Statistics) to the first row dimension, thus getting all of the counts followed by all of the percents. This is done for Figure 4 by a simple drag of the mouse. To learn more about the user interface for pivot, be sure to see the Tutorial in SPSS 7.0.

Pivot table
Figure 4: Pivot Statistics to First Row Dimension
This view makes it easiest to see the difference between Tumor absence and presence for Female and then for Male. For females, 28 did not have tumors and 23 did. For males, 59 exhibited no tumor while 12 did have tumors. Red dye #40 seems to be more dangerous to female mice.


Compare Sex Differences

Another interesting view of this table can be created by switching the order of Sex and Tumor, as is shown in Figure 5.

Pivot table
Figure 5: Change Order of Tumor, Sex

This view makes it easier to compare the male and female differences for tumor absence and presence. There are 23 females and 12 males that had tumors. Concentrate on Mice with Tumors

Since there are only two tumor values (Present and Absent), if one wants to compare tumor percents it is redundant to see both. This table can be simplified by putting the Tumor dimension in the layers, so that only one value is visible at a time. This was done for Figure 6.

Pivot table
Figure 6: Pivot Tumor to Layers

This view makes it even easier to see the patterns of tumor percent and how they differ for Male and Female, especially in the Medium and High doses. For Females, there was 26.1% with tumors in both he medium and high dosage groups, while males had fewer in medium (16.7%) and more in the high dosage group (33.3%). Examining the counts shows that these percents aren't worth interpreting since there are so few males that had tumors in any of the groups.

Given the small sample size for males, this table would be the logical place to stop. The rest of this example shows steps which might be taken had the sample size been larger. Hide Rows

When the interest is in showing the percents, the Count section of the table can be hidden. This is shown in Figure 7.

Pivot table
Figure 7: Hide Counts

Instead of hiding one could have instead pivoted the Statistics dimension into the layer. In situations where more than two categories exist, the decision to hide or pivot to a layer would be made based on how many categories you wish to be visible. When only a single category is desired, a pivot into the layer might be easier. When all but a few categories is desired, hiding is the way to accomplish the goal.

Figure 8 shows the same table as Figure 7 but with Statistics pivoted into the layer instead of hiding the Count category.

Pivot table
Figure 8: Pivot Statistics into Layers

Add a Footnote

Finally, Figure 9 shows the table after selecting the Male Medium Dose cell, bolding the text and inserting a footnote.

Pivot table
Figure 9: Add a Footnote

Summary

SPSS pivot tables provide the researcher with a tool to further investigate relationships in the output of statistical procedures. Pivoting makes it easy to rearrange the output in many interesting ways. The SPSS pivot table also opens up the output to formatting that was previously unavailable.

All the tables in this paper were created from a single run of the Crosstabs Procedure in SPSS. The different arrangements of the dimensions in the output are accomplished by interacting with the output, rather than going back and requesting different procedures. Thus, the user can pivot to understand and discover different parts of the story that is contained in the output. All the permutations of the dimensions into rows, columns, and layers are easily accessible from a single piece of output.

This paper was created by copying a single pivot table from the SPSS 7.0 output into Microsoft Word. This table was then copied and pasted into each of the additional figures, then edited by double clicking the figure to in-place activate the OLE 2.0 object. Thus, all the customization, pivoting, and hiding can be done either in SPSS or in any other application that is an OLE 2.0 container.


This page was adapted from a web page at the SPSS web page.  We thank SPSS for their permission to adapt and distribute this page via our web site.

How to cite this page

Report an error on this page or leave a comment

The content of this web site should not be construed as an endorsement of any particular web site, book, or software product by the University of California.