Skip to content
Related Articles

Related Articles

How to Create a Contingency Table in Excel?

View Discussion
Improve Article
Save Article
Like Article
  • Last Updated : 22 Mar, 2022

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

Dataset

 

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. 

 Choose-the-PivotTable-option

 

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. 

Choosing-the-range-and-location

 

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 chose in the location in Step 2. 

Empty-contingency-table-in-the-G1-cell

 

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

Filling-the-data

 

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

Resultant-Contingency-Table

 

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.
My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!