EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Statistical Functions in Excel Excel Chi Square Test
Secondary Sidebar
Excel Functions
  • Statistical Functions in Excel
    • COUNTIF Formula in Excel
    • COUNTIF Multiple Conditions
    • COUNTIFS with Multiple Criteria
    • Statistics in Excel
    • Confidence Interval in Excel
    • Interpolate in Excel
    • Linear Programming in Excel
    • COUNTIFS in Excel
    • Excel Variance
    • Normal Distribution Formula in Excel
    • Count Cells with Text in Excel
    • Excel Formula For Rank
    • Linear Regression in Excel
    • COUNTA Function in Excel
    • MAX Formula in Excel
    • Excel NORMSINV
    • Count Names in Excel
    • FORECAST Formula in Excel
    • NPER in Excel
    • Excel Median Formula
    • Lognormal Distribution in Excel
    • Excel Chi Square Test
    • Count Formula in Excel
    • COUNTIF Examples in Excel
    • Excel P-Value
    • COUNTIF Not Blank in Excel
    • Excel Standard Deviation Formula
    • Excel GROWTH Formula
    • Excel Percentile Formula
    • Excel Frequency Formula
    • Excel Average Formula
    • Excel Correlation Matrix
    • Excel Z Score
    • Excel MAX IF Function
    • Z TEST in Excel
    • Excel Trendline
    • Excel F-Test
    • Excel STDEV Function
    • Excel Frequency Distribution
    • DCOUNT Function in Excel
    • Excel MIN Function
    • Excel Forecast Function
    • FREQUENCY Excel Function
    • COUNTIF with Multiple Criteria
    • Standard Deviation in Excel
    • MAX Excel Function
    • Excel QUARTILE Function
    • Excel T.Test Function
    • Excel PERCENTILE Function
    • MODE Excel Function
    • SLOPE Excel Function
    • Excel Median Function
    • Excel TREND Function
    • Excel Count Function
    • Excel LARGE Function
    • SMALL Excel Function
    • COUNTIF Excel Function
    • Excel AVERAGE Function
    • Excel CORREL Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training Certification
  • Online Excel for Marketing Course

Excel Chi Square Test

By Madhuri ThakurMadhuri Thakur

Chi Square Test in Excel

Table of Contents ( Chi-Square Test in Excel )

  • Chi Square Test in Excel
  • How to do Chi Square Test in Excel?

Chi Square Test in Excel

Chi Square Test in Excel is one such statistical function used to calculate the expected value from a dataset with observed values. Excel is a versatile tool to analyze data visually as well as statistically. It is one of the few spreadsheet tools around which supports advanced statistical functions. Using these functions, we can gain insights from a dataset that may not be possible by just visually analyzing them. In this article, we will learn how to calculate the Chi Square from a database using excel. Before going into detail with the Chi Square Test, let us go through a few examples.

Chi Square Test is a test of the validity of a hypothesis. The Chi Square P Value tells us if our observed results are statistically significant or not. A statistically significant result means that we reject the null hypothesis (the null hypothesis in statistics is a statement or hypothesis which is likely to be incorrect). A Chi-Square P-Value is a number between 0 and 1. A Chi-Square P-Value less than 0.05 usually lead to rejection of the null hypothesis.

How to do Chi Square Test in Excel?

Let’s understand how to do the Chi Square Test in Excel with some examples.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,725 ratings)
You can download this Chi Square Test Excel Template here – Chi Square Test Excel Template

Example #1

Chi Square test can tell us whether the proportion of a given number of items is in one place based on a random sample are statistically independent of each other or not.

Suppose your company has 10000 pieces of furniture. About one by a tenth of them are distributed over four halls. We can find out what proportion of the total furniture is in one hall as shown below:

Chi Square Test Example 1-1

Observe that we have about 250 pieces of furniture in each hall. If we want to get the expected number of furniture by type, we will calculate it as follows:

Expected Value =Category Column Total X (Category Row Total/Total Sample Size)

Using this formula, we get an expected number of about 92 Chairs in Hall A as follows:

Expected Number of Chairs in Hall A = 250 X (362/984)

We will check the difference using the following formula:

((Observed Value-Expected Value)ⁿ)/expected value

In the case of the Chi Square test, n = 2

Which would give us the value 0.713928183. Similarly, we will find the values for each quantity and the sum of these values is the test statistic. This statistic has an approximate Chi-Squared distribution if each quantity is independent of the other. The following formula would determine the degree of freedom for each quantity:

(number of rows – 1)(number of columns – 1)

Which, in this case, is 6.

We find the Chi-Square P-value for the first value that is the number of chairs. The null hypothesis is that the location of the furniture is independent of the type of furniture.

The Chi Square P-value for the chairs would be calculated as:

The sum of all the Chi Square P values should be 1 if the null hypothesis is true.

If the test statistic is too large in the present dataset, then we reject the null hypothesis.

As is clear from the above example, calculating Chi Square and testing for significance of hypothesized data in statistics is a painstaking process and demands high accuracy. The above example can be analyzed using the CHISQ.TEST Function to get the Chi-Square value directly and check if our assumption that the location of the furniture is independent of the type of furniture is correct r not. In this case:

Chi Square Test Example 1-2

The Chi Square value is approximately 0.03. From our earlier discussion, we now know that this rejects the null hypothesis.

Example #2

To calculate the p-value in excel, we need to perform the following steps:

  • Calculate your expected value. The expected value in chi-square is normally a simple average or mean for normally distributed datasets. For more complicated data, please check the example above.

Chi Square Test Example 2-1

  • Type your data into columns and click a blank cell where you want to show the results on the worksheet and then click the “Insert Function” button on the toolbar, a pop up would appear. Type chi in the Search for a Function box and then click “Go”.then select “CHITEST” from the list and then click “OK.”

 Example 2-2

  • Select the observed and expected ranges and click “OK”.

 Example 2-3

We will get the result as follows.

 Example 2-4

Things to Remember 

  1. The CHISQ.TEST is not the only Chi Square function available in excel. All the variations of Chi-Square are available and can be used depending on your proficiency in statistics.
  2. The CHISQ functions can also be directly typed into a cell-like any other function. This would save time if you already know the data ranges you are working with.
  3. The CHISQ function’s reliability is directly dependent on the structure and distribution of the data and the clarity of the hypotheses being tested. Particular care should be used while employing a Chi Square Test to check significance.

Recommended Articles

This has been a guide to Chi Square Testin excel. Here we discuss How to do the Chi Square Test in excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. VLOOKUP Function in Excel
  2. Excel STDEV Function
  3. Substring in Excel
  4. Excel Square Root Function
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for Marketing Training (8 Courses, 13+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Chi Square Test Excel Template

EDUCBA

Download Chi Square Test Excel Template

EDUCBA

डाउनलोड Chi Square Test Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more