XLSTAT: Creating Contingency (Cross-tab) Tables in Excel

 

Dataset for Example

The dataset contains information on 20 clients:

• age

• city of residence

• gender.

We will create a contingency table based on the Age and City variables.

 

Setting Up

• Open XLSTAT.

• Click on the Preparing data menu and select the Create a contingency table feature.

 

• Once you've clicked the relevant button, the dialog box appears.

In the General tab:

• Select the category variable you wish to use in rows.

• Select the Age variable by selecting the entire column.

• Select the variable to be used in columns. Here we choose the City variable.

• The columns B and C contain variable labels so the option Variable labels should be ticked.

• Activate the By group analysis field if you want to use a layer variable, such as the gender, and generate a three-way cross-tab.

In the Options tab:

• You can decide how the categories of the variable should be treated.

• Choose to run a Chi-square test. 

• Select only the Sort the categories alphabetically option.

In the Outputs tab:

• Select the Contingency table as well as the observed and theoritical frequencies.

In the Charts tab:

• Select the following options:

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The first result is the contingency table.

Notice that clients are absent in certain crossed categories.

For example there is no detected client in Paris of age class 25-34.

The next output is the 3-D plot followed by a stacked bar chart, which is a good visualization of the data distribution.

 

Next are two tables containing the frequencies by Age and City.

You can compare the actual distribution of the clients and the theoritical distribution.

 

Statistical Tests on Contingency Tables

It is possible to run Chi Square and Fisher's Exact Tests on the two qualitative variables that shape the contingency table independently.

 

Advantages of XLSTAT Contingency Tables (vs. Excel Pivot Tables)

1. XLSTAT is able to automatically output test results on the contingency tables.

2. You can enter as many qualitative variables as you want in both the row and column variable fields in XLSTAT. XLSTAT will produce one result for each possible pair of row/column variables.