Create a Contingency Table in ExcelCreate a Contingency table in Excel with the Insert PivotTable tool

Written By:
Patrick Louie
Reviewed By:
FundsNet Staff

Excel is a great app you can use whenever you’re working with data.

You can perform various mathematical, scientific, or even statistical equations with it.

Excel has tons of formulas, functions, and features that can help you with that.

One of the more complex things you can do with Excel is data analysis.

Excel has many tools that can assist its users in analyzing data.

Among these is the creation of pivot tables.

These nifty tables/charts help with summarizing and reorganizing selected columns and rows.

As a result, you’ll obtain reports about the selected data (e.g. frequency of a particular variable, a list of unique values, etc.).

Note that pivot tables don’t change the dataset itself. Rather, they only show a different perspective of the selected data.

The Contingency Table

With the create pivot table tool in Excel, you can create a Contingency table (a.k.a. crosstab).

It is usually used to show the relationship between two categorical variables.

Generally, a Contingency table displays the frequency of particular variables.

And it does so in a table or matrix format, which makes it easier to read and digest for the user.

You can even further derive results from a Contingency table using chi-square tests.

Because of the insight that a Contingency table provides, it is widely used in research sectors such as scientific research, statistics, survey research, etc.

Create a Contingency Table in Excel

In this article, I’ll be showing you how you can create a Contingency table in Excel.

By the end of the article, you should be able to create a Contingency table whenever you need to.

Let’s get started.

Use the Insert PivotTable Button to Create a Contingency Table in Excel

The more popular method of creating a Contingency table in Excel involves inserting a pivot table in Excel.

To do so, you’ll have to click on the Insert PivotTable button.

This button can be accessed by opening the Insert tab. On the very left side of the ribbon, you’ll see the PivotTable button (or as how I would call it, the Insert PivotTable button).

Clicking on the dropdown arrow below the button will allow you to create a pivot table from one of three sources: (1) From Table/Range (default option), (2) From External Data Source, and (3) From Data Model.

Clicking on the button itself will create a Pivot table based on the selected Table or Range of Cells.

Now, let’s proceed to how we can use this button to create a contingency table in Excel.

How to Create a Contingency Table

To create a Contingency table, we must first have a dataset to base on.

And so, we’ll be using the following dataset for illustration:

From this data set, we want to get the following data: (1) the number of teams, (2) the total number of players per team, (3) the number of male and female players per team, (4) the total number of male and female players, and (5) the total number of players.

To do so, we’ll create a Contingency table.

Insert PivotTable

  • Select the table or range of cells which you want to derive the Contingency table from. Make sure to include the column headers in the selection. In our illustration, we’ll be selecting cells A1:C16.

  • Click on the Insert PivotTable button. To do so, open the Insert tab. On the very left side of the ribbon, you’ll see the PivotTable button. Click on it.

  • Since we’ve already selected the data from which we’ll derive the Contingency table, the textbox next to Table/Range should already be filled up.

  • Next, we’ll have to select where we want our Contingency table to appear: (1) on a new worksheet, or (2) on the same worksheet as the source data. For our illustration, we’ll be selecting the 2nd option which is on the same worksheet as the source data.

  • Lastly, we’ll have to select where on the worksheet should the Contingency table appear. Click on the textbox next to Location. Then, select the first cell of the Contingency table (an easy way to do this is to click on the cell). For our illustration, this will be cell A18.

  • (Optional) To analyze multiple tables, check the box next to “Add this data to the Data Model”.
  • Click the OK button. This should open the PivotTable Fields dialog box.

Creating the Contingency Table

We’ll now have to set the pivot table to make it into a contingency table. To do so, we’ll have to drag the variable to their respective areas.

  • For our illustration, we’ll be dragging the variable to these areas: Team -> Rows, Gender -> Columns, and Player ID –> Values.

  • Next, we’ll have to change the Sum of Player ID in the values into the Count of Player ID. To do so, click on Sum of Player ID. This will show you a list of options. Select Value Field Settings from among them.

  • In the box below “Summarize value field by”, select count. This should summarize the variably by count (i.e. frequency). Click the OK button after doing so.

  • And there we have it. We have successfully created a contingency table.

Interpreting the Contingency Table

Now that we have our Contingency Table, let’s see if we can finally obtain our desired data

The number of teams

We can get this data by counting the number of rows which is 3. These three teams are Blue, Red, and Yellow.

The total number of players per team

We can get this by looking at the grand total per row (team). Each team (Blue, Red, and Yellow) has 5 players.

The number of male and female players per team

We can get this by taking a look at where the Team and Gender Columns meet. Blue team has 4 female members and 1 male member. Red team has 0 female members and 5 male members. Yellow team has 3 female member and 2 male members.

The total number of male and female players

We can get this by looking at the total per column. There are 7 female players and 8 male players.

The total number of players

We can get this by looking at the grand total, which is 15.

Conclusion

And that’s how you create a Contingency table in Excel.

While it can be intimidating at first, it’s not really that hard once you get to know how to use the Insert PivotTable tool to create a Contingency table.

I hope that you’re able to learn something from this article.