Open In App

How to Create a Contingency Table in Excel

Last Updated: 21 Jun, 2024

E

Everything tech
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

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.

How-to-Create-a-Contingency-Table-in-Excel

Create a Contingency Table in Excel

What is a Contingency Table in Excel

A contingency table in Excel, also known as a cross-tabulation or crosstab, is a statistical tool used to summarize the relationship between two or more categorical variables. It displays the frequency distribution of the variables in a matrix format, making it easier to analyze the data and identify patterns or correlations.

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

Dataset

 

Step 1: Choose the PivotTable option from the Insert tab. This will open a dialog box. 

Choose-the-PivotTable-option

 

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. 

Choosing-the-range-and-location

 

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.

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

 

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. 

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. Now when we have successfully created the contingency table, let us see how we can read it to derive useful information from it. 

Resultant-Contingency-Table

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.

Conclusion

Contingency tables, also known as crosstabs, are powerful tools in Excel for analyzing relationships between categorical variables. By utilizing the PivotTable function and dragging your data fields, you can quickly create these tables to identify trends and gain insights from your data. Remember, you can also perform chi-square tests using the CHISQ.TEST function to assess the statistical significance of the relationships found in your contingency table.

FAQs – Make Contingency Table in MS Excel

How do I make a contingency table in Excel?

You can create a contingency table using the PivotTable function. Here’s a quick rundown:

  • Select your data range.
  • Go to Insert tab > PivotTable.
  • Choose where to place the table and click OK.
  • Drag and drop fields into the Rows and Columns sections to organize your data.

What are contingency tables known as in Excel?

Contingency tables are also known as crosstab tables in Excel.

How to calculate chi square contingency table in Excel?

To calculate a chi-square test for your contingency table, you’ll need to use the CHISQ.TEST function in Excel. This function requires entering the table range and expected values (often left blank for calculation). For help with the function, you can search for it within Excel or refer to online documentation.



Article Tags :

E

Everything tech
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

N

Nandini Gujral

How to Create Pivot Table in Excel using Java?

A pivot table is needed to quickly analyze data of a table with very little effort (and no formulas) and sometimes not everyone has time to look at the data in the table and see what’s going on and use it to build good-looking reports for large data sets in an Excel worksheet. Let's discuss a step-by-step proper explanation to create a pivot table in an Excel file in Java using Free Spire.XLS for Java API. Requirements to Create Pivot Table in Excel using JavaFirstly, we need to add necessary dependencies to the Maven project including the Free Spire.XLS project before starting to write the code. How to Install Spire.XLS for JavaBy adding the following code to your project's pom.xml file, you can quickly import the JAR file into your program if you use Maven. XML <repositories> <repository> <id>com.e-iceblue</id> <name>e-iceblue</name> <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url> </repository> </repositories> <dependencies> <dependency> <groupId>javax.xml.bind</groupId> <artifactId>jaxb-api</artifactId> <version>2.3.1</version> </dependency> <dep
Read More

N

Nandini Gujral

How to Create Pivot Chart from Pivot Table in Excel using Java?

A Pivot Chart is used to analyze data of a table with very little effort (and no formulas) and it gives you the big picture of your raw data. It allows you to analyze data using various types of graphs and layouts. It is considered to be the best chart during a business presentation that involves huge data. To add a pivot chart to an Excel worksheet, you need to use the "WorksheetChartsCollection.add" method.  Before Creating a Pivot Chart, one first needs to go through how to Create Pivot Table in Excel using Java. Now let's discuss the steps to create a pivot chart in an Excel file in Java using Free Spire.XLS for Java API. How to Create a Pivot Table in Excel using JavaStep 1: Load the Excel fileWorkbook workbook = new Workbook() String workbookName = "Geeks_For_Geeks.xlsx"; workbook.loadFromFile(workbookName); Step 2: Get the first worksheetWorksheet sheet = workbook.getWorksheets().get(0); Step 3: Get the first pivot table in the worksheetIPivotTable pivotTable = sheet.getPivotTables().get(0); Step 4: Add a clustered column chart based on the pivot table to the second worksheetChart chart = workbook.getWorksheets().get(1).getCharts().add(ExcelChartType.ColumnClustered, pivotTa
Read More

G

Gautam goel

How to Create a Two-Variable Data Table in Excel?

article_img
Two-Variable Data Table is a very significant tool for what-if data analysis. With the help of two-variable data tables, we can find all possible trends that can arrive by changing different values. For example, if we know the annual sales of a company, its percentage of expenses and growth. So, by keeping annual sales constant we can find the projected sales of a company by varying expenses and growth. Let's learn how to create a two-variable data table. Two-Variable Data Table A data table is a table in which we can find multiple values by adding some fields and formulas. A two-variable data table is a table in which value changes exactly according to two parameters and the rest of the parameters are kept constant. Structure of two-variable data table This could better be understood with an example, given the data set of a line, its x co-ordinate, the slope of a line, and its intercept. Find the different values of y co-ordinate keeping intercept as constant. Following are the steps to create a two-variable data table: Step 1: In cell B7, write the formula for finding the y co-ordinate of a line i.e. y = m*x + c. Step 2: Copy the same formula in cell D3. Step 3: Write X Coord
Read More

J

Jahnavi

Location of Table Mountain | Interesting Facts About Table Mountain

The most recognizable landmark in South Africa is Table Mountain. It is also one of the most photographed locations in the nation, and millions of visitors rode the famed cable car to the top. Travelers from all over the world, as well as people from South Africa, come to view Table Mountain as the most hospitable icon. But this mountain conceals a lot of mysteries that are just waiting to be found. It is much more than just a beautiful backdrop for photos or a spot from which to capture Cape Town in all its glory. On Table Mountain, there are 1470 different flower species and over 2,200 different plant species. Along with the mountain, the national park is home to the Cape of Good Hope, the southernmost point of the African continent and another popular site in South Africa. Table Mountain: Heritage and BiodiversityOne of the oldest mountains in the world, Table Mountain was created by volcanic and glacial activity some 520 million years ago. It is considerably older than the Himalayas and the Alps, at least six times older. Its current name is derived from the Portuguese navigator Antonio de Saldanha's 1503 designation of it as Taboa do Cabo, or Table of the Cape. It was referred
Read More

K

Kamsani Maheshwari

How to Flatten Data in Excel Pivot Table?

article_img
Flattening a pivot table in Excel can make data analysis and extraction much easier. In order to make the format more usable, it's possible to "flatten" the pivot table in Excel. To do this, click anyplace on the turn table to actuate the PivotTable Tools menu. Click Design, then Report Layout, and then, at that point, Show in Tabular Form. This will isolate the line names and make it simpler to investigate information.What Does Flattening Mean in Excel?Flattening a pivot table in Excel refers to transforming the data from a pivot table format, which can be complex and hierarchical, into a simple tabular format. This process separates row labels and creates a structure that is easier to work with for further analysis or export to data warehouses.Flatten is a table function that takes a VARIANT, OBJECT, or ARRAY column and produces a lateral view (for example an inline view that contains a relationship alluding to different tables that go before it in the FROM clause). FLATTEN can convert semi-structured data to a relational representation.Benefits of Flattening Data in ExcelImproved Data Analysis: Flattened data is easier to analyze, as all relevant information is available in a ta
Read More

A

Aditya Upadhyay

How to Add and Use an Excel Pivot Table Calculated Field?

article_img
Excel pivot tables are one of its most helpful features. They are utilized to summarize or aggregate large quantities of data. The data can be summarized using the average, the count, or other statistical approaches. It summarizes a large amount of data into a few rows and columns. They make it simple to explore the data from various views and angles and are excellent for data exploration. When you create a Pivot table, you often need to add more data and calculations to your analysis. If you require a new data point that can be obtained by leveraging existing data points in the Pivot Table, you don't need to go back and add it to the source data. Instead, you can utilize a Pivot Table Calculated Field to accomplish this. Pivot Table Calculated Field The "Pivot Table calculated field" option allows you to insert more data or calculations in the pivot table. For our reports, we frequently need to include our own unique calculations that can relate to other fields in the data collection. Benefits of using a Pivot Table Calculated FieldIt is scalable since it will automatically account for any new data added to your Pivot Table. You can utilize a Calculate Field in your Pivot Table ju
Read More

C

Chetan

How to Hide Zero Values in Pivot Table in Excel?

article_img
One of Microsoft Excel's most important features is the pivot table. You might be aware of this if you have worked with it. It provides us with a thorough view and insight into the dataset. You can do a lot with it. The pivot table might include zero values. In this lesson, you will learn how to hide zero values in the pivot table using relevant examples and visuals. Hide the rows in a pivot table that have zero values. After concealing the zero-value rows, your pivot table will seem neater and the data will be suppressed. How to Hide Zero Values in Pivot Table in Excel? ImplementationLet's take a dataset as an example to understand How to Hide Zero Values in a Pivot Table in Excel. This is the dataset that we are going to use in this scenario. The Excel table has some zero values, as you can see. First, you must transform an excel table into a pivot table. You may need to hide them at times to have a clear view of the pivot table. Methods for Hiding Zero-ValuesThere are three different methods for hiding zero values in an Excel pivot table. Table of Content How to Create a Filter Field to Hide Rows with Zero ValuesHow to Hide Pivot Table Zero Values Using Excel's Format Cells Comm
Read More

P

Prinkal

How to Troubleshoot and Fix Excel Pivot Table Errors?

article_img
Power Pivot is an Excel add-in, a data modeling technology that helps the user to create data models, establish relationships, and create calculations. However, it is possible to encounter a few errors during the process. here in this article, we will discuss some of the Excel Pivot Table Errors that may occur and how we can fix them. Usually, Excel Pivot works smoothly and efficiently, however, it is inevitable to encounter some errors while working with the software. Here are some of the common errors that users might encounter during working with Excel PowerPivot. "Unable to load the Data Model" Error Sometimes, A simple data change, for example, adding the pivot table data to the Data Model may cause a pivot table refresh error. It’s not that common, however, sometimes users do face such issues. As we can see in the image below: Here, in the image, as we can see above the message is showing that the “PowerPivot is unable to load the Data Model”. Solution: The change from the UPPER case to the PROPER case or other similar changes in the source data headings may be the primary cause of this error resulting in the data model having a second instance of the field. As shown in t
Read More

L

lokesh Singh

How to Remove Table Formatting in Excel

article_img
Struggling with unwanted table formatting in Excel? Whether you're working on a crucial report or just cleaning up your spreadsheet, knowing how to remove table formatting in Excel can save you time and frustration. Excel tables come with predefined styles that can sometimes get in the way of your preferred presentation or data analysis methods.In this article, we'll show you the quickest and easiest ways to remove table formatting in Excel while keeping your data intact. Say goodbye to distracting table styles in Excel and hello to clean, organized spreadsheets. Let's dive into the best methods for transforming your tables into plain data, giving you full control over your worksheet's appearance and functionality.How to Remove Table Formatting in ExcelTable of ContentHow to Remove Table Formatting in ExcelHow to Clear All Tables in ExcelHow to Remove Excel Table Formatting (while keeping the Table)How to Change the Formatting of the Excel TableHow to Remove Format as Table in Excel (Convert Table to Range)How to Remove Table Formatting in ExcelStep 1: Launch the Excel spreadsheetTo remove your Excel table formatting you have to open your Excel spreadsheet from which you want to re
Read More

L

lokesh Singh

Excel Table Styles and Formatting: How to Apply, Change and Remove

article_img
In Excel, you can format tables using various styles, colors, fonts, and other visual elements to make your data more organized and visually appealing. However, there may be situations where you want to remove all the formatting from a table and revert it to its default appearance. Have you ever felt stuck in the maze of Excel tables? Let’s break free together! Discover the straightforward way to remove table formatting in Excel. No more complications—just a quick and easy guide to make your data dance without the formatting fuss. Ready to embark on this hassle-free journey? Let’s get started! Excel Table StylesIn Microsoft Excel, table styles are predefined formatting sets that allow you to quickly and easily apply a consistent and professional look to your data tables. These styles include various formatting elements such as bold headers, alternating row colors, and distinct column formatting. Here's a short note on Excel table styles: How to Choose a Table Style When Creating a TableStep 1: Launch Your Excel SpreadsheetTo Apply Excel table formatting you have to open your Excel spreadsheet from which you want to remove table formatting. Step 2: Select the Data Select the data fo
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox