Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Use the Chi Square Test Excel Function? 4 Easy Steps

How to Use the Chi Square Test Excel Function? 4 Easy Steps

Note: This tutorial on the Chi Square test Excel function is suitable for Excel versions 2010 and later, including Office 365. 

The CHISQ.TEST is a statistical function in Excel that calculates the chi-square statistic of two variables in a dataset.

Usually, these two variables are categorical in nature and represent the frequency of occurrence of any human behaviour or natural phenomenon. 

The Chi-square test of independence helps us understand whether there is any kind of significant relationship between these two variables. 

Hence, it is an excellent tool to test the independence of two categorical variables.   

In this guide, I’ll show you how to use the Chi Square test Excel function properly and how to interpret its results correctly. 

Related:

The Excel SMALL Function – 3 Best Examples

How to Make An Excel Forecast Sheet in 3 Steps – The Best Guide

How to Use Excel COUNTIFS: The Best Guide

You’ll learn:

  • When to Use the Chi Square Test of Independence?
  • Chi-Square Test Distribution Formula  
  • Chi Square Test Excel Function – Syntax
  • How to Use the CHISQ.TEST Function in Excel?
  • Things to Keep in Mind About the CHISQ.TEST Formula

You can download the practice Excel sheet and follow along with this tutorial.

download-button-green-

When to Use the Chi Square Test of Independence?

Use it to determine the independence of two categorical variables. 

Some common use cases are: 

  • Sales Performance (Good and Bad) vs Sales Training (Trained and Untrained)
  • Age of Borrowers (Young, Middle Age and Old) vs Credit Risk (High Risk and Low Risk)
  • Stock Performance (Good, Bad and Neutral) vs Industry Performance (Good and Bad)

In this example, I’ll test the relationship between gender (male and female) and voting preference (left, right and centre).  

Chi-Square Test Distribution Formula  

The simplest version of the chi-square distribution formula is given below: 

Where,

  •  ‘r’ is the number of rows. 
  • ‘c’ is the number of columns.
  • Oij is the observed frequency in row ‘i’ and column ‘j’. 
  • Eij is the expected frequency in row ‘i’ and column ‘j’.

Don’t get intimidated by this formula. You can bypass most of these calculations using the CHISQ.TEST function. All you need to do is to understand the basics of how and where to use this function and how to interpret the results. 

Please note that the Chi square test Excel function (CHISQ.TEST) is not available in versions earlier than Excel 2010. 

It is an updated version of the CHITEST function. 

If the Chi square test Excel function is not available in your Excel version, the Chi-Square statistic has to be manually calculated using the above formula. Then, you have to compare it with the chi-square distribution chart to test its significance. 

Chi Square Test Excel Function – Syntax

In Excel versions 2010 and later, there is a built-in function called CHISQ.TEST to quickly calculate the chi-square statistic value. 

It has the following syntax:

= CHISQ.TEST(actual_range,expected_range)

Where actual_range is the data range of the cells that contain the observed frequencies. 

Expected_range is the data range of the cells that contain the expected frequencies. 

Also Read:

How to Add Error Bars in Excel? 7 Best Methods

How to Extract an Excel Substring? – 6 Best Methods

How to Make a Line Graph in Excel? 4 Best Line Graph Examples

How to Use the CHISQ.TEST Function in Excel?

In this guide, I am going to test whether the voting preference of a person is independent of his/her gender.

It is very important to clearly state the null and alternative hypotheses before solving this problem.

For this example,

Hₒ (Null Hypothesis): Gender and Voting preferences are independent.

H₁ (Alternative Hypothesis): Gender and Voting preferences are not independent.

You can follow these simple steps to easily use the Chi square test Excel function and test the independence of two variables.

  1. Before we begin, arrange your dataset like this: 
Chi Square Test Excel - Cross Tab
Arrange the dataset where the two categorical variables are listed in respective rows and columns in a cross tabular fashion

Notice that the rows contain frequencies of one categorical variable (gender) and the columns contain frequencies of another categorical variable (voting preference). Also, calculate the row and column totals as shown above. 

  1. Then, in a different location calculate the expected frequencies of the same dataset. It is very simple and defined by the formula = (row total x column total) / grand total.
Chi Square Test Excel Expected frequency
Calculate the corresponding expected frequencies in a separate location
  1. Using the CHISQ.TEST function is very simple. All you have to do is just enter the ranges of the observed and expected frequencies inside the formula. 

In this example, I use the formula =CHISQ.TEST(B3:D4,B10:D11)

That’s all, you will directly get the p-value of the chi-square distribution directly as the result of the above formula. 

Use the CHISQ.TEST function to calculate the Chi-square statistic p-value
Use the Chi Square test Excel function to calculate the Chi-square statistic p-value

This p-value represents the probability that the difference between the observed and expected frequencies are caused by mere chance. 

Usually, a p-value of less than 0.05 (Alpha – the confidence interval) is considered significant. That is, if the p-value is 0.0125, then there is a 1.25% probability that the differences are due to mere chance. The smaller this value, the higher the significance. 

A higher significance value implies that the null hypothesis (Hₒ) is rejected.

Hence, in this case, Hₒ is rejected. This means that gender and voting preferences are significantly dependent on each other. 

  1. You can use an IF formula to compare the p-value and alpha to test the significance and return the accepted hypothesis. 
Chi Square Test Excel Accept or Reject Hypothesis
Compare the Chi Square Test Excel function’s resulting p-value with the confidence interval (alpha) to accept or reject the Null hypothesis

In this example, I use =IF(H7<H6,H3,H2), to determine and return the accepted hypothesis. 

Things to Keep in Mind About the CHISQ.TEST Formula

  1. The #N/A error usually occurs when the observed and expected frequencies are of different dimensions. They should always match and there should be at least two categories for each variable. 
  1. The #DIV/0! error occurs when any of the input values are zero. 
  1. The #NUM! error occurs when any of the input values are negative. 

Suggested Reads:

How to Shade Every Other Row in Excel? (5 Best Methods)

How to Superscript in Excel? (9 Best Methods)

How to Split Cells in Excel? 3 Easy Methods

FAQs

What is the Chi square goodness of fit test?

It is similar to the Chi square test of independence. But, unlike the test of independence, we compare the observed frequencies in a sample vs the observed frequencies in the population of the same variable. This test is used to determine if there is any sampling error in an experiment.

What does the p-value mean in the Chi-square test?

The p-value represents the probability that the deviation of values between the variables occurred due to mere chance. A higher p-value means that the deviation in the values can be explained by mere chance and the relationship between the variables is insignificant.

Closing Thoughts

In this tutorial, I have covered all the important details about CHISQ.TEST function. We saw how to use the Chi Square test Excel function appropriately and also how to interpret the results. You can download the attached sample Excel worksheet and experiment with the details to get a better understanding of the concept. 

If you need more high-quality guides on Excel visit our free Excel resources centre. 

Ready to dive deep into Excel? Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. 

Click here for advanced Excel courses with in-depth training modules.

Adam Lacey

Adam Lacey is an Excel enthusiast and online learning expert. He combines these two passions at Simon Sez IT where he wears a number of different hats. When Adam isn't fretting about site traffic or Pivot Tables, you'll find him on the tennis court or in the kitchen cooking up a storm.

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

Semi-Automatic Creation of an Org Chart in Visio 2010

How to Make a Sankey Diagram Excel Dashboard? A Step-by-Step Guide

How to Group Worksheets in Excel? (In 3 Simple Steps)

How to Navigate the Microsoft Project 2016 Workspace

Shape Alignment and Spacing in Visio 2010 – Part 2

Jira Ticket – How to Create, View and Delete [2022]

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)