What is Chi-Square Test in Google Sheets?
The Chi-Square Test is a statistical test used to determine whether there is a significant association between categorical variables. It compares the observed frequencies with the expected frequencies. The result is a Chi-Square statistic, which can be used to determine the p-value and evaluate whether the differences are statistically significant.
There are two main types of Chi-Square tests: Chi-Square Test of Independence, which determines if two categorical variables are independent and Chi-Square Test of Goodness of Fit which checks if the observed frequencies fit a particular distribution. We use the formula =CHISQ.DIST.RT(test statistic, deg_freedom) to find the p-value to check for the association.

Key Takeaways
- The Chi-Square Test in Google Sheets is used to determine if there are associated dependencies between categorical variables. It compares observed frequencies with expected frequencies
- Types of Chi-Square Tests and check if the results show significant differences.
- There are two types of Chi-Square test in Google sheets.
Test of Independence to determine if two categorical variables are independent.
Goodness of Fit to find if the observed frequencies fit a particular distribution. - The Chi-Square Test in Google Sheets is widely used in various fields to find the fit between actual data and theoretical models.
- There are many built-in functions in Google sheets such as CHISQ.INV, CHISQ.DIST.RT and CHISQ.TEST that help calculate p-values and Chi-Square statistics easily.
Chi-Square Goodness of Fit Test and its Uses
Let us look at a brief overview of the Chi-Square Goodness of Fit test. It is a hypothesis test that helps conclude the distribution of a population based on a sample.
Here, you can test whether the Goodness of fit is good enough to conclude that the population follows the distribution.
Many tests can be done based on samples drawn, such as an equal proportion of male and female voters, 80% right-handers and 20% left-handers, a Poisson distribution of earthquakes per decade, and so on.
Uses
- The key areas where you can use the test involve:
- When doing market research to check customer preference
- For evaluating voter preferences during elections.
- Analyzing student performance.
- In clinical trials to check if the distribution of responses to a treatment fits the distribution based on previous studies.
Chi-Square Test for Independence and its Uses
A chi-square test of independence tests whether two categorical variables are related to each other. If so, the probability of one variable with a certain value is dependent on the other variable’s value. It is a nonparametric hypothesis test. You use it to test a hypothesis about the relationship between two categorical variables.
Chi-square tests of independence are usually performed on binary or nominal variables.
Uses
- It is used to analyze consumer behavior
- Evaluate student performance based on demography
- Analyzing customer and job satisfaction
How to Perform the Chi-Square Test in Google Sheets?
Let us check how to perform the chi-square test in Google sheets with an interesting example.
A researcher keeps track of the number of visitors to his website in March, as shown below.
- Week 1 – 235
2. Week 2 – 350
3. Week 3 – 524
4. Week 4 – 280
Let us perform a Chi-square test to check if it on par with his expected visitors.
Step 1: Input the data in Google Sheets as shown below. We have the expected value at 350 because, on average, we get around 347 for the observed values, which is the average number expected by the researcher to hit his website each week.

Step 2: Let us calculate the difference between observed and expected values. Use the formula shown below to calculate it for each row:
(O-E)2 / E
Enter =(B2-C2)^2/C2 in cell D2.

Step 3: Get all the differences by dragging the formula to D5 and calculate the p-value.
Let us calculate the Chi-Square test statistic and the corresponding p-value using the formula.
X2 – =SUM(D2:D5)
P value – =CHISQ.DIST.RT(D6, COUNT(D2:D5)-1)
CHISQ.DIST.RT(x, deg_freedom) returns the right-tailed probability of the Chi-Square distribution associated with a test statistic x and a certain degree of freedom. The degrees of freedom = n-1. Hence, we use the COUNT formula – 1.
Enter the above formulae in cell D6 and D7, respectively.

Step 4: As observed, since the p-value is less than 0.05, we cannot reject the null hypothesis. That is, we cannot refute the claim that the true distribution of visitors is any different from the value claimed by the researcher for any week.
Examples
Let us look at some examples of the two types of Chi-square test in Google Sheets and assess the fit between categorical data and theoretical models.
Example #1 – Chi-Square Test for Goodness of Fit
Let us look at a similar example as shown above. We have a company studying the distribution of jellybeans in each of their packets. As known, the expected distribution of the five colors—red, green, yellow, orange, and pink—is 20% each. Upon research, the following values were obtained.
- Red: 35
- Green: 28
- Yellow: 32
- Orange: 24
- Pink: 26
Let us perform the chi-square Goodness of fit test in Google Sheets.
Step 1: Let’s calculate the expected frequencies. Find the total number of candies and divide by 5.
Total jellybeans/5 = 145/5 = expected frequency of a color = 29

Step 2: Let us calculate the Chi-Square Statistic.
Use the following formula for each row.
(O-E)2 / E
Hence, type =(B2-C2)^2/C2 in cell D2 and drag it down to D6. Sum to get the Chi-Square statistic.

Step 3: Here, the degrees of freedom is 5-1 = 4.
Let us calculate the p-value using the function
=CHISQ.DIST.RT(Chi-Square Statistic, Degrees of Freedom)
Apply the following formula in cell D8.
=CHISQ.DIST.RT(D7, 4)

Our p-value is used to determine whether the observed and expected values are widely deviated. If the p-value is below a significance level of 0.05, we reject the null hypothesis and conclude that the distribution does not fit the expected values.
Here, since it is above 0.05(0.5989), we do not reject the null hypothesis and can claim that there is a broader deviation between the observed and expected values.
Thus, the Chi-Square Test of Goodness of Fit compares observed data against expected distributions across various domains.
Example #2 – Chi-Square Test for Independence
We have two different functions for performing the Chi-Square Test of Independence.
- The TEST Function and
- The INV.RT Function.
CHISQ.TEST Function
The CHISQ.TEST Function returns the p-value of the set of data
If the p-value is less than the significant level, we conclude there is an association between the variables in the dataset.
=CHISQ.TEST(actual range, expected range)
In this example, we try to find whether people in a particular region favor a particular soft drink. Let us take a sample of 900 residents and ask for their cool drink preferences. Then, let us enter their answers in Google Sheets.

Step 1: Let us perform the Chi-Square test of independence with the hypothesis as
- Region and brand are independent.
- Region and brand are dependent.
Let us calculate the expected values. For that, we need another table to enter the formula.
Expected value = (row sum * column sum) / table sum.
Apply this formula, as shown below, to each cell independently.

Step 2: Now, let us calculate (O-E)2 / E for each cell.
For this, the formula will be as follows:
For example, Texas-cocacola
=(B2-B9)^2/B9.
Likewise, apply the formula to other cells.

Step 3: Let’s calculate the test statistic X2 and the corresponding p-value using the following formula.
Here, X2 = =SUM(B16:D18)
P value = =CHISQ.DIST.RT(H8, 2)

Since the p-value is less than 0.05, we reject the null hypothesis. We can say that there is an association between region and soft drink brand preference.
Example #3
In another simple example for Chi-Square test in Google Sheets, we have some observed and expected values. Let’s use the Goodness of fit test to determine whether the sample data matches the theoretical distribution.

Step 1: Let us compute the Chi-Square Statistic by first computing the differences.
Enter the formula =(A2-B2)^2/B2 in cell C2 and drag it till C4.

Then, find their sum for the Chi-square statistic.

Step 2: Calculate the degrees of freedom. Here, it is 3 – 1 =2. Now, apply the following
formula to calculate the p – value in C6.
=CHISQ.DIST.RT(Chi-Square Statistic, Degrees of Freedom)
=CHISQ.DIST.RT(C4, 2)

Here, since it is above 0.05(0.9459), we do not reject the null hypothesis and can claim that there is a wider deviation between the observed and expected values.
Important Things to Note
- The Chi-square test in Google Sheets does not require any assumptions about the data distribution.
- The basic idea behind both tests is to compare the actual data values with expected values if the null hypothesis is true.
- The Chi-square test in Google Sheets helps identify whether a change in one variable causes a change in another. It is very helpful in market research, education, healthcare, retail, and employment.
Frequently Asked Questions (FAQs)
To decide which test to use, look at the following scenarios.
For a single measurement variable, we use the Chi-square goodness of fit test. We chose this test to see if one variable is likely to appear from a given distribution. For example, as shown in Example 1, we use this test to decide whether bags of jellybeans have the same number of pieces of each color or not.
If there are two measurement variables, the Chi-square test of independence is used. This test checks whether two variables might be related or not. As shown in example 2, we use such a test to find if the region decides brand preferences, if a particular gender favors a particular political party, and so on.
The steps for both the Chi-square test in Google Sheets, Goodness of fit test, and test of independence are the same, as listed below.
1) First, define your null and alternative hypotheses before data collection.
2) Choose the alpha value, and decide on the alpha value. For example, suppose you choose α=0.1 when testing; you have decided on a 10% risk of concluding the two variables are independent even when they are not if you are performing the test of independence.
3) Check the data to see if any errors exist and for any assumptions.
4) Finally, proceed with the required test, as stated in the examples above, to draw your conclusions.
Pros:
• It is very easy to use and does not require any add-ons. Google Sheets has built-in functions like CHISQ.DIST.RT and CHISQ.TEST, which simplifies calculations and reduces the need for manual calculations.
• It can be done with a nominal amount of data, and there are no assumptions about the data distribution.
• You can create graphs to represent the data visually.
Cons:
• The data must be frequency data and manual entry is needed, which is prone to errors. Here, the sample should be selected randomly.
• For massive data sets, this can lead to performance issues, unlike manual calculations.
• It is sensitive to sample size.
Download Template
This article helps understand the Chi-Square Test in Google Sheets features through examples. You can download the template and use it instantly.
Recommended Articles
Guide to What is the Chi-Square Test in Google Sheets. We explain how to perform Chi-Square Test in Google sheets along with its uses, examples, and points to note. You can learn more from the following articles –
Leave a Reply