1. Tutorials
  2. Association in 2-way contingency tables tutorial

Contingency Tables & Association in Excel: Worked ExampleLearn how to test for association between two categorical variables, and reorganize the categories to emphasize the pattern of association.

Snee collected data on the hair color and eye color of 592 students on a statistics course. The question is how to understand the association between the two variables. For more information see Story: Patterns of Association.

In this tutorial you will perform the following tasks:

  1. Cross tabulation of the data
    Create a contingency table and test the hypothesis of independence.
  2. Displaying patterns of association
    Add a mosaic plot to display the pattern of association.
  3. Reordering data
    Reorder the categories to emphasize the pattern and uncover the relationship.

Cross tabulation of the data

When two variables are measured the relationship between them is often of interest. A contingency table describes the joint distribution of the variables. It can also describe the marginal distributions of each variable, or the conditional distribution of one variable given the other.

  1. Open the file tutorials\Hair-Eye Color.xlsx.
  2. Click a cell in the dataset.
  3. On the Analyse-it ribbon tab, in the Statistical Analyses group, click Compare Groups, and then click Contingency Table.
    The analysis task pane opens.
  4. In the Y (response) variable drop-down list, select Hair.
  5. In the X (factor) variable drop-down list, Select Eye.
  6. In the Model: Y by X drop-down list, select Frequency
  7. In the Frequency variable drop-down list, select Count.
  8. On the Analyse-it ribbon tab, in the Compare Groups group, click Test Proportion > Pearson X2.

    A Pearson X2 hypothesis test is added to the analysis task pane.

  9. In the Hypothesis drop-down list, select The variables are not independent as the alternative hypothesis .
  10. In the Significance level edit box, enter 5%.
  11. Click Calculate.
    The results are calculated and the analysis report opens.

The contingency table shows the joint distribution of the variables.

contingency table

The hypothesis test p-value is highlighted as it is less than 5% significance level (the actual p-value is < 0.0001). You can interpret the p-value as indicating a substantial departure from independence.

chi-square test

Displaying patterns of association

A p-value offers little insight into the data. It may indicate that there is an association, but does not identify the nature of the relationship. A mosaic plot expresses the deviations from independence.

  1. On the Analyse-it ribbon tab, in the Compare Groups group, click Mosaic.

    A mosaic plot is added to the analysis task pane.

  2. In the Split drop-down list, select Y.
  3. In the Color by drop-down list, select Residual.
  4. Click Recalculate.
    The results are calculated and the analysis report opens.

The mosaic plot represents the counts in a contingency table by tiles whose area is proportional to the cell count. Column widths show the marginal proportions of hair colors. The heights of the tiles show the conditional frequency of eye color given hair color. Under the null hypothesis of independence, the heights of the tiles in each row should be the same.

mosaic plot

It is easier to see the deviation from independence by coloring and shading the tiles by the residuals from the Pearson test. Positive deviations are blue, negative red. The absolute value of the residual is shown by the shading; cells with residuals less than 2 are unfilled; cells >= 2 are filled; those >= 4 are filled with a darker pattern. Under the assumption of independence, these values roughly correspond to two-tailed probabilities p < .05 and p < .0001 that a given value of exceeds 2 or 4.

You can see from the mosaic plot that Black/Brown and Blond/Blue are over-represented, while Blond/Brown is under-represented.

Reordering data

To increase the visual impact of the mosaic plot you can reorder the rows and columns to make the pattern more coherent. It is useful to re-order the categories so that the deviations from independence have opposite-corner pattern, with positive values running from bottom-left to top-right corners and negative values along the opposite diagonal.

  1. On the Analyse-it ribbon tab, in the Report group, click Goto Dataset.
  2. On the Analyse-it ribbon tab, in the Dataset group, click Dataset.
  3. In the Variables list box. select Hair.
  4. Clear Sort categories.
  5. In the Categories list, click and drag the categories to re-order them into the order: Black, Brown, Red, Blond.
  6. In the Variables list box, select Eye.
  7. Clear Sort categories.
  8. In the Categories list, click and drag the categories to re-order them into the order: Blue, Green, Hazel, Brown.
  9. Click Apply.
  10. Activate the report worksheet.
  11. On the Analyse-it ribbon tab, in the Report group, click Recalculate.

The over-representation of Black/Brown and Blond/Blue is now much more apparent, together with the under-representation of Blond/Brown and people with Black hair and Blue eyes. Although the table was reordered based on the residuals you can see that both dimensions are ordered from dark to light, suggesting an explanation for the association.

mosaic plot re-ordered

Tutorials v6.20.0

Administrator's Guide

User's Guide

Statistical Reference Guide