Your data analysis solution

Cross-tab or contingency table in Excel

2017-10-20

This tutorial shows how to create a cross-tab, also called contingency table from two qualitative variables in Excel using the XLSTAT software.

Dataset to create a cross-tab or contingency table

An Excel sheet with both the data and the results can be downloaded by clicking on the button below:
Download the data

The dataset contains information on 20 clients: their age, city of residence and gender. We will create a contingency table on the Age and City variables.

Creating a cross-tab or contingency table

After opening XLSTAT, select the XLSTAT / Preparing data / Create a contingency table command.
XLSTAT Function to create a contigency table

Once you've clicked on 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. Then select the variable to be used in columns. Here we choose the City variable.

The output should be displayed in a new sheet so tick the option Sheet.

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

Setting up a contigency table

In the Options tab, you can decide how the categories of the variable should be treated. Also you can choose to run a Chi-square test. In this case, however, we will only select the Sort the categories alphabetically option.

Setting up a contigency table

In the Outputs tab, select the Contingency table as well as the observed and theoritical frequencies.

Setting up a contigency table

Select the 3D view of the contingency table in the tab Charts.

Setting up a contigency table

The computations begin once you have clicked on the OK button, and the results are displayed on a new sheet.

Results of the creation of a cross-tab or contingency table

The first result is the contingency table.

Notice that there isn't a client in all crossed categories. For example thre is no client in Paris aged 25-34.

Results: Contigency table

The next result is the 3-D plot, which is a good visualization of the data distribution.

Results: 3D view of the contigency table

Next are the two tables containing the frequencies Age/City.

You can compare the actual distribution of the clients and the theoritical distribution if the distribution was random.

Results: observed and theoretical frequencies

Statistical tests on cross-tabs or contingency tables

It is possible to test if the two qualitative variables that shape the contingency table are independent.

Advantages of using XLSTAT cross-tabs instead of Excel pivot tables

Among the many advantages of using the XLSTAT contingency table feature compared to Excel pivot tables:
  • XLSTAT is able to automatically output test results on the contingency tables.
  • 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.
The following video tackles crosstabs with an illustration using XLSTAT:
 
1c26995d494fb3061dd0ae8571ffc0a4@xlstat.desk-mail.com
https://cdn.desk.com/
false
desk
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/portal/articles/autocomplete
9283