A contingency table is a table that is used to summarize the relationships between two categorical variables. The table lists the number of times each combination of the two variables occurs.
Excel is a spreadsheet application that is widely used for data analysis. Excel has a built-in feature that allows you to create contingency tables. This article will show you how to work a contingency table in Excel.
How to Create a Contingency Table in Excel? 6 Steps
A contingency table is a powerful tool for analyzing data. Excel provides a built-in function for creating contingency tables, which you can use to calculate various statistics, such as chi-square and p-values.
To create a contingency table in Excel, follow these steps:
Step 1. For example, we share a sheet with the information of PO #Number, Region, and Color. Here we want to track the product cells by those columns with a contingency table. So, Open the Excel file containing the data we want to analyze.
Step 2. Select the data we want to include in the table. Here we select A1 to C8 range of data.
Step 3. Click the Insert tab, and then click the Contingency Table button.
Step 4. In the Contingency Table dialog box, specify the options we want and click OK. So choose the data that we want to analyze. Here we select the table range of A1:C8. Now we have to choose where to place the table. Select existing worksheet Table/Rang A10. Then click ok.
Step 5. We insert the contingency table into our Excel worksheet.
Step 6: On the pivot table fields, we have to move Po #Number, Region, and Color into the Values box, Row box, and column box. By default, it will Sum. Otherwise, you can also click on the icon on the i and click on the count instead.
So as followed by simple steps, you’ve created a contingency table in Excel with just a few clicks. At a glance, you can see how many products were sold by color and region.
Plus, the PivotTable automatically calculates totals for both colors and regions. This is a great way to perform a visual statistical analysis of your data quickly.
