From data to report in one minute or less. Ajelix BI is launched! 🚀 Get started for free.

# How To Make a Contingency Table In Excel

• Author:
• Published on:
April 11, 2023
• Category:
• Last updated:
April 16, 2024
• Tags:

Contingency tables are a crucial tool in data analysis, used to organize and summarize the relationship between two categorical variables. These tables provide a visual representation of the data, making it easier to draw conclusions and make informed decisions.

During this article, we will provide a step-by-step guide on how to create a contingency table in Excel.

## What Is a Contingency Table in Excel?

Excel contingency table is a grid that summarizes the relationship between two categorical variables by showing how often each combination of categories occurs in your data.

Contingency table example from Excel, image by author

## How To Create a Contingency Table in Excel?

Time needed: 10 minutes

Here’s a step-by-step guide on creating a contingency table in Excel

Contingency tables analyze relationships between categorical variables. Ensure your data is organized into columns where each column represents a category (e.g., gender, age group, product type).

2. Insert the PivotTable

Navigate to the worksheet containing your data. Click the Insert tab. In the Tables group, click PivotTable.

3. Create the Table

A dialog box will appear. Here’s what to do:
1. Select the data source: In the Table/Range field, enter the cell range of your data table (e.g., A1:C10).
2. Choose the output location: Existing worksheet: Select this option if you want the contingency table on the current sheet. Choose a cell where you want the table to appear.
3. New worksheet: This creates a new sheet for the table.

4. Build the Contingency Table

The PivotTable Fields pane will appear on the right side of your screen. This is where you drag and drop fields to build your table.
Drag the field representing the row category (e.g., gender) to the Rows area of the PivotTable Fields pane.
Drag the field representing the column category (e.g., product purchased) to the Columns area of the pane.

5. Calculate Values (Optional)

By default, the table shows counts (number of occurrences) in each cell. You can change this:
Click on any value in the table.
In the PivotTable Analyze tab, click Summarize Values By.
Choose a different summary function like SumAverage, or Percentage.

Formatting: You can format the table like any other table in Excel. Change fonts, borders, and cell colors to enhance readability.
Grand Totals: Right-click on any row or column header and select Show Values > Show All to display grand totals.
Tip: You can drag additional fields to the Filters area of the PivotTable Fields pane to filter the data and analyze specific subsets.

## How To Interpret the Contingency Table?

Interpreting a contingency table in Excel involves analyzing the counts in each cell to understand the relationships between the categorical variables. Here’s a breakdown of how to make sense of your contingency table:

### 1. Focus on Row and Column Percentages

While raw counts can be informative, often it’s more insightful to look at percentages. You can usually calculate these using the Value Field Settings option by right-clicking on a value in the table. Percentages within each row will show what proportion of customers in that age group purchased each product category.

Screenshot from Excel on how to change calculation type for pivot table, image by author

Similarly, column percentages can reveal what portion of customers who bought a specific product category belong to each age group.

### 2. Identify Patterns

Look for trends or unexpected results in the percentages. For example, a high percentage of young customers buying hats compared to other age groups suggests a strong preference for hats among younger demographics.

P.S. This is where Ajelix BI can help you identify trends faster.

### 3. Compare Expected vs. Observed

If you had a hypothesis about the relationship between the variables, compare the observed percentages in the table to your expectation. For instance, if you expected similar purchase patterns across age groups, large deviations from that in the percentages might suggest a significant relationship.

### 4. Look for Statistical Significance (Optional)

While contingency tables provide initial insights, for more robust conclusions, you might consider statistical tests like chi-square to assess whether the observed relationships are statistically significant (i.e., not due to chance).

• Small Sample Size: Be cautious when interpreting results from tables based on very small datasets. Trends might not be reliable with limited data.
• Consider External Factors: Contingency tables only show relationships within your data. Other factors not included in the table might also influence purchase decisions (e.g., seasonal trends, marketing campaigns).

By following these steps and keeping these considerations in mind, you can effectively interpret the information revealed by your contingency table in Excel, gaining valuable insights into the relationships between your categorical variables.

## Conclusion

Contingency tables are useful in analyzing datatext analysis that can reveal important insights into the relationship between quantitative variables. Excel provides a user-friendly interface and robust capabilities for creating contingency tables.

Users can create contingency tables in Excel and make informed decisions by following the steps in this article. Remember to sort and organize the data, choose the appropriate variables, and interpret the table to draw meaningful conclusions.

How do I choose rows and columns for my contingency table?

To choose rows and columns for your contingency table. Select the fields you want to include from the PivotTable Fields pane and drag them into the Rows and Columns boxes.

Can I calculate percentages and totals in my contingency table?

Yes, you can calculate percentages and totals in your contingency table. To do so, click on the “Value Field Settings” option and choose either “Count,” “Sum,” or “Average” for your data. Select “Show Values As” to display the types of data analysis as a percentage of the row, column, or total.

Can I create charts and graphs from my contingency table?

Yes, you can create charts and graphs from your contingency table. After creating your Pivot Table, select the “Insert” tab and choose the type of chart or graph you want to create. You can then customize your chart or graph as desired.

## Similar posts

### How To Analyze Data In Excel Spreadsheet

April 4, 2024
Excel is the workhorse of data analysis in countless businesses. It can unlock valuable insights from spreadsheets, but for many, its pow…

### How To Add Drop Down List in Excel

November 2, 2023
Let’s take a look at how to add drop down list to make your spreadsheets more professional and user-friendly. We’ll take a lo…

October 31, 2023

## Online Calculators

### Budget vs Actual Dashboard Example: Variance Analysis

September 19, 2024
A robust budgeting and reporting system is essential to track and analyze financial health. One crucial tool that can help you do that is…

### Online Profit And Loss Dashboard Example: P&L Template

September 17, 2024
A well-designed online profit and loss dashboard can be a game-changer for businesses. By visualizing key metrics, you can quickly assess…

### IT Dashboard Examples For IT Department Managers

September 12, 2024
IT dashboards have become indispensable tools for organizations seeking to make informed decisions and optimize IT departments. With the …

### SQL Dashboard Examples: Create A Real-Time Dashboard

September 10, 2024
SQL dashboards give executives and business founders a powerful tool to monitor key metrics and identify trends to make informed strategi…

### CMO Dashboard Examples For Marketing Chief Officers

August 20, 2024
As the Chief Marketing Officer, your role drives business growth. A well-designed CMO dashboard serves as your compass, providing real-ti…

### Online HR Recruitment Dashboard Examples For HR Experts

August 15, 2024
A digital HR recruitment dashboard is a centralized, interactive platform visually representing key metrics and data related to the recru…

### 24 IT KPIs & Metrics For IT Department With Examples

August 6, 2024
IT KPIs are quantifiable metrics that help IT departments assess their performance and align with overall business objectives. They provi…

### 26 Insurance KPIs & Metrics For Insurance Agencies

August 1, 2024
Insurance KPIs are metrics insurance companies use to monitor their performance and efficiency. They provide insights into various aspect…

July 30, 2024
Administrative KPIs are key performance indicators that measure the efficiency and effectiveness of admin functions within an organizatio…

### 10 Equipment Rental KPIs For Rental Business

July 25, 2024
Equipment rental KPIs are measurable values that track how well your rental business is using its equipment to generate profit and satisf…

### 20 Financial KPIs For The Finance Department

July 23, 2024
Financial KPIs are quantifiable metrics that businesses use to track their financial health and performance. They provide valuable insigh…

### 15 Ecommerce KPIs Any Online Business Should Track

July 18, 2024
Ecommerce KPIs track how well your online store is performing. These metrics provide insights into different areas of your business, like…

#### Business Intelligence platform meets AI

Trusted by 150'000 customers around the globe since 2018.

© 2018 - 2024 AJELIX, SIA