Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Make a Contingency Table in Excel (2 Easy Ways)

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.


Download Practice Workbook


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.


2 Simple Methods to Make a Contingency Table in Excel

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.

how to make a contingency table 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.

Creating PivotTable to Make a Contingency Table in Excel

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.

Editing value field settings dialogue box to Make a Contingency Table in Excel

Consequently, you will have a Contingency Table as demonstrated in the following picture.

Final output of method 1 to Make a Contingency Table in Excel

Read More: Create Table in Excel Using Shortcut (8 Methods)


Similar Readings


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.

Applying Excel Formula to Make a Contingency Table in Excel

  • 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.

Using COUNTIFS function to Make a Contingency Table in Excel

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 has purchased after getting the promotional Email. Then, the SUM function will return the sum of the cells of the selected range.

  • Then, hit ENTER.

Using SUM function to Make a Contingency Table in Excel

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.

Final output of method 2 to Make a Contingency Table in Excel

Read More: How to Create a Table with Existing Data 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:

How to Construct a Contingency Table with Percentages in Excel

  • 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.

Final output of method 3 to Construct a Contingency Table with Percentages in Excel

Read More: How to Create a Table in Excel with Multiple Columns


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

practice section to make a contingency table in excel


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. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy.


Related Articles

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo