How to Create a Contingency Table in Excel?

A contingency table, also known as a crosstab is used to show the relationship between two categorical variables. In excel, we can make a contingency table using the pivot table function. They are best for summarizing the relationship between categorical variables. A Contingency table is just like a frequency distribution table in which we can show two variables simultaneously. To further derive results from a Contingency table, chi-square tests are used. In this article, we will learn how to create a Contingency Table.

Creating a Contingency Table

Let us take the following dataset and convert it into a contingency table. This dataset consists of product IDs, product names, and the product manufacturer. The dataset that you want to convert into a contingency table can have any number of rows or columns. T

 

Follow the following steps to create the contingency table from this dataset.

Step 1: Choose the PivotTable option from the Insert tab. This will open a dialog box. 

 

Step 2: In the dialog box that appears, choose the range of values as $A1:$C6 and then choose another cell in the sheet where you want to place the pivot table. Here, we have chosen cell G1. The range of values is the entire space that contains the dataset. 

 

Step 3: Now click ok and you will see an empty contingency table in the G1 cell. It appears in the G1 cell because that was what we choose in the location in Step 2. 

 

Step 4: Now, we have to populate the table. In other words, we have to fill this empty table with the dataset values. But, we do not have to do this manually. In the window on the left, drag Manufacturer into the box named Rows, drag Names into the box named Columns, and drag Id in the box named Values. 

 

Notice that the frequency values are automatically populated in the contingency table. This is the required contingency table for the dataset assumed above. 

 

Now when we have successfully created the contingency table, let us see how we can read it to derive useful information from it. 

Interpretation of the Contingency table

One of the major advantages of using a Contingency table is that it makes the visualization of the relationship between variables very easy. We can interpret a Contingency table based on rows, columns as well as cells. 

Based on row total

• 2 products are made by manufacturer X.
• 1 product is made by manufacturer Y.
• 2 products are made by manufacturer Z.

Based on column total

• Milk sold was 1.
• Soya sold were 2.
• Tofu sold was 2.

Based on individual cells

• 2 Tofus were purchased from Manufacturer X.
• 1 Milk was purchased from Manufacturer Y.
• 2 Soyas were purchased from Manufacturer Z.