Chi Square in Excel

A chi square test helps you compare observed values with expected values. The result, called a p-value, measures the strength of the relationship between the two value sets, if any. Fortunately, this type of analysis is easy to perform in a spreadsheet. Let’s learn how to find a chi square in Microsoft Excel.

How to Find a Chi Square in Excel

Before diving into individual functions, it’s helpful to understand exactly what a chi square does, and what it means. Finding the p-value with a chi square test tells you the significance of the results you found in hypothesis testing. That leads us to a key concept: that of the null hypothesis.

The null hypothesis is best described as a baseline, accepted idea. The challenge in statistical analysis is to either prove or disprove – termed “reject” – the null hypothesis.

In statistical analysis, the null hypothesis represents your expected values. Observed values will either prove or reject the null hypothesis. For them to do so, you need to find the p-value, and that’s what a chi square test delivers.

Without exception, p-values fall between 0 and 1. Large p-values, above 0.05 and closer to 1, indicate that the null hypothesis should not be rejected. Smaller p-values, generally those less than or equal to 0.05, indicate that your observed data disproves the null. In these instances, you should reject the null hypothesis.

Since the p-value is a measure of strength, they’re one of the ultimate tools in hypothesis testing. Often, you’ll see the analysis performed with a scientific calculator, special software, or other similar tools. But Microsoft Excel has a chi square function built in that will return p-values for you with no complex calculations required. It only takes a few simple inputs.

Your first step is to gather your data. Remember, you’re searching for observed values and expected values. The p-value will be calculated based upon these two ranges.

Expected values, again, are your null hypothesis. Let’s think of a real-world example. Imagine that you run a travel agency, and you’re working on a targeted marketing campaign. You have a book of 120 clients in total. You believe that 40 will choose to vacation in the mountains, 40 in cities, and 40 at the beach. These numbers form your null hypothesis.

When you go to observe data, you’re really trying to determine whether to reject the null hypothesis. In other words, will the observed – actual – values that you find differ meaningfully from the null?

In Excel, place two columns: one labeled Observed and one labeled Expected. In the Expected column, enter the three null values: 40, 40, and 40. In the Observed column, place your actual data. Let’s say that, when you check with your clients, 55 choose the mountains, 30 choose cities, and 35 choose the beach. Therefore, type 55, 30, and 35 into the Observed column.

Now, it’s time to find the p-value itself with a chi square test. Again, Excel has a built-in function to find this value, without the need for tough and time-consuming calculations.

In an empty cell, type an = sign. This tells Excel that you’re beginning a formula. Then, you’ll need to add the chi square test function, written as CHISQ.TEST(. Your formula reads:

=CHISQ.TEST(

From here, Excel asks for only two inputs: the actual(observed) range and the expected range. Click and drag to select the values in your Observed column, A2:A4. Then type a comma. Repeat this step with the expected range, and close with ). Your formula is:

=CHISQ.TEST(A2:A4,B2:B4)

Hit Enter on your keyboard, and Excel returns the chi square p-value: 0.0125881. This is well below 0.05, so you should reject the null hypothesis.

As you can see, it’s easy to find a chi square p-value, thanks to Microsoft Excel.