Create contingency tables

Contingency tables are a way to visualize the relationship between two qualitative variables. Create them in Excel using the XLSTAT add-on software.


What is a contingency table

A contingency table is an efficient way to summarize the relation (or correspondence) between two categorical variables V1 and V2. It has the following structure:

V1 V2Category 1Category jCategory m2
Category 1n(1,1)n(1,j)n(1,m2)
Category in(i,1)n(i,j)n(i,m2)
Category m1n(m1,1)n(m1,j)n(m1,m2)

where n(i,j) is the frequency of observations that show both characteristic i for variable V1, and characteristic j for variable V2.

XLSTAT tool for creating a contingency table

From two or more qualitative variables, XLSTAT enables you to create instantly contingency tables summarizing the structure of the dataset. The data should be selecting in the general tab of the tool using the below fields:

Row variable(s): Select the data that correspond to the variable(s) that will be used to construct the rows of the contingency table(s).

Column variable(s): Select the data that correspond to the variable(s) that will be used to construct the columns of the contingency table(s).

By group analysis: Activate this option and select the data that describe to which group each observation belongs. XLSTAT will then run the analysis for each group separately. Useful for creating three-way crosstabs. 

Weights: Activate this option if the observations are weighted. If you do not activate this option, the weights will be considered as 1. Weights must be greater than or equal to 0. If a column header has been selected, check that the "Variable labels" option is activated.

XLSTAT tests on contingency tables

XLSTAT provides you with two criteria to characterize the relationship between the two variables:

  • The Chi-square distance has been suggested to measure the distance between two categories. The Pearson chi-square statistic, which is the sum of the Chi-square distances, is used to test the independence between rows and columns.
  • Inertia is a measure inspired from physics that is often used in Correspondence Analysis, a method that is used to analyse in depth contingency tables. The inertia of a set of points is the weighted mean of the squared distances to the center of gravity.

XLSTAT charts for contingency tables

3D view of the contingency table: Activate this option to display the 3D bar chart corresponding to the contingency table.

2D Bar Charts options

Chart type

  • Grouped: Choose this option to display the graphs as bars grouped by modality.
  • Stacked bars: Choose this option to display the chart as stacked bars. These charts are used to compare the frequencies of sub-samples to those of a full sample.

Bar charts

  • Frequencies: Choose this option to display the frequencies corresponding to each bar.
  • Percentages: Choose this option to display the % of population corresponding to each bar.
ternary diagramneural network diagram

analyze your data with xlstat

14-day free trial