A contingency table in Microsoft Excel helps you analyze the relationship between two sets of categorical data by showing how often different combinations occur.
- It is commonly created using a Pivot Table, which automatically summarizes and organizes the data into a clear, structured format.
- This makes it easier to compare categories, identify patterns, and perform basic statistical analysis.
Steps to Make a Contingency Table in Excel
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. Follow the following steps to create the contingency table from this dataset.
Step 1: Choose the PivotTable Option
Choose the PivotTable from the Insert tab. This will open a below dialog box.
Step 2: Choose the Range Value in Dialouge Box
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: Click OK to See the Contingency Table
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.
Step 4: Fill the Populated Table
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 Tablechose
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 the Table Total
- 2 products are made by manufacturer X.
- 1 product is made by manufacturer Y.
- 2 products are made by manufacturer Z.
Based on the 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.