Contingency Tables, which help us summarize a large set of data, are commonly used in various statistical analyses. In Excel, we can make a Contingency Table by following two simple methods. So, let’s start this article and explore these methods.
What Exactly Is a Contingency Table?
Contingency Tables are nothing but a summary of various categorical variables. Contingency Tables are also known as Cross Tabs, and Two-way Tables. Generally, a Contingency Table displays the frequency distribution of several variables in a table or matrix format. It gives us a quick overview of the interrelationships between the variables in the table. Contingency Tables are widely used in various research sectors like survey research, scientific research, etc.
How to Make a Contingency Table in Excel: 2 Simple Methods
In this section, we will learn two simple methods for creating a Contingency Table in Excel. Let’s say an online retailer sent an Email about promotional discounts to potential customers of different Regions. Here, we have the Purchase Status of some of the customers. Our goal is to make a Contingency Table using these data in Excel.
Not to mention that we have used the Microsoft Excel 365 version for this article; you can use any other version according to your convenience.
1. Creating PivotTable
Using the PivotTable option is one of the easiest ways to make a Contingency Table in Excel. Let’s follow the steps outlined below to do this.
Steps:
- Firstly, select the dataset and go to the Insert tab from Ribbon.
- After that, choose the PivotTable option from the Tables group.
As a result, the PivotTable from table or range dialogue box will open on your worksheet.
- Now, in the dialogue box, choose the Existing Worksheet option as marked in the following image.
- Then, click on the Location field and select cell C21.
- Finally, click OK.
Consequently, the PivotTable Fields dialogue box will open.
- Now, in the PivotTable Fields dialogue box, drag the Region option into the Rows section.
- After that, drag the Email option into the Values section.
- Then, drag the Purchase Status option into the Columns sections.
- Following that, click on the Sum of Email as marked in the image below.
- Next, select the Value Field Settings option.
As a result, the Value Field Settings dialogue box will be available on your worksheet.
- Now, in the dialogue box, select the Count option.
- Finally, click OK.
Consequently, you will have a Contingency Table as demonstrated in the following picture.
Read More:Â How to Calculate Percent Frequency Distribution in Excel
2. Applying Excel Formula
Applying an Excel formula is another smart way to make a Contingency Table in Excel. We will use the COUNTIFS function of Excel here. Now, let’s use the steps mentioned below.
Steps:
- Firstly, create a table as shown in the following picture.
- After that, enter the following formula in cell D23.
=COUNTIFS($D$5:$D$19,$C23,$C$5:$C$19,D$22)Here, the range of cells $D$5:$D$19 indicates the cells of the Region column, cell C23 represents the selected Region, the range of cells $C$5:$C$19 refers to the cells of the Purchase Status column, and cell D22 indicates the selected Purchase Status.
- Then, press ENTER.
As a result, you will know how many customers in the Midwest region purchased after receiving the promotional Email.
- Then, drag the Fill Handle up to cell E23 to get the following outputs.
- Now, select cells D23 and E23 together, and drag the Fill Handle up to cell E26.
Consequently, you will have the count of both the customers who have purchased and didn’t purchase after getting the promotional Email for all Regions, as shown in the image given below.
- After that, enter the formula given below in cell D27.
=SUM(D23:D26)Here, the range of cells D23:D26 indicates the count of customers who have purchased after getting the promotional Email. Then, the SUM function will return the sum of the cells of the selected range.
- Then, hit ENTER.
As a result, you will have the total number of customers who have purchased after getting the promotional Email in cell D27.
- Then, drag the Fill Handle up to cell E27.
Subsequently, you will have the total number of customers who haven’t purchased after getting the promotional Email in cell E27.
- Next, use the following formula in cell F23.
=SUM(D23:E23)Here, the range of the cells D23:E23 refers to the count of both the customers who have purchased and didn’t purchase after getting the promotional Email from the Midwest Region.
- Following that, hit ENTER.
As a result, you will have the total number of customers in the Midwest region in cell F23.
- Finally, drag the Fill Handle up to cell F27 to obtain the remaining outputs as demonstrated in the following image.
Read More: How to Make a Relative Frequency Table in ExcelÂ
How to Construct a Contingency Table with Percentages in Excel
In this section of the article, we will learn how to construct a Contingency Table with percentages in Excel. Let’s follow the instructions outlined below to do this.
Steps:
- Firstly, follow the steps mentioned in the 1st method to get the following output.
- Now, click on any cell of the Pivot Table. Here, we selected cell C23.
As a result, the PivotTable Fields dialogue box will be available on your worksheet.
- After that, select the Count of Email as marked in the following image.
- Then, choose the Value Field Settings option.
Subsequently, the Value Field Settings dialogue box will open on your worksheet.
- Following that, go to the Show Values As tab in the dialogue box.
- Then, click on the drop-down icon as marked in the image below.
- Now, choose the % of Grand Total option.
- Lastly, click OK.
Consequently, you will have your desired Contingency Table with percentages as demonstrated in the following image.
Read More: How to Make a Categorical Frequency Table in Excel
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.
Download Practice Workbook
Conclusion
So, these are the most common & effective methods you can use anytime while working with your Excel datasheet to make a contingency table in Excel. If you have any questions, suggestions, or feedback related to this article you can comment below.
Related Articles
<< Go Back to Frequency Distribution in Excel | Excel for Statistics |Â Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
