If you want to measure how closely two sets of numbers move together in a linear pattern, PEARSON returns a correlation coefficient from -1 to 1.
In this article, I’ll show you how to read positive, negative, and weak correlations, then calculate one for a selected group.
PEARSON returns a single value, but it can work inside dynamic array formulas such as =PEARSON(FILTER(...),FILTER(...)).
PEARSON Function Syntax in Excel
The PEARSON function returns the Pearson product-moment correlation coefficient for two sets of paired values.
=PEARSON(array1,array2)
- array1 (required): The set of independent values.
- array2 (required): The set of dependent values paired with
array1.
The correlation coefficient is dimensionless and ranges from -1 through 1.
Its sign shows direction, while its distance from zero shows the strength of the linear relationship.
When to Use PEARSON Function
- Measure the direction and strength of a linear relationship between two numeric variables.
- Compare positive, negative, and weak correlations across paired observations.
- Calculate a coefficient for a selected group without copying its rows elsewhere.
- Check whether a visible pattern is strong enough to investigate further with a chart or model.
Example 1: Find a Positive Correlation
The first dataset has two measures that rise together.
Below is the dataset. Columns A and B contain daily high temperatures and cold brew units sold across eight days.
We want to measure the linear relationship between temperature and cold brew sales.
Here is the formula to enter in E2:
=PEARSON(A2:A9,B2:B9)
The formula returns 0.9993. That value is very close to 1, showing a strong positive linear relationship in these eight observations.
As the daily high increases, cold brew sales also increase in this dataset. PEARSON measures how consistently those paired values follow a straight-line pattern.
Pro Tip: A strong correlation does not prove that one variable causes the other. Treat it as a pattern to investigate, not evidence of cause.
Example 2: Find a Negative Correlation
The next dataset moves in the opposite direction.
Below is the dataset. Column A lists delivery distances, while column B contains the customer rating for each delivery.
We want to measure how delivery distance and customer rating move together.
Here is the formula to enter in E2:
=PEARSON(A2:A9,B2:B9)
The formula returns -0.9985. Its negative sign shows that the two measures move in opposite directions.
The coefficient is close to -1, so the negative linear relationship is strong in this sample. Longer distances are paired with lower ratings.
The sign describes direction. It does not mean the relationship is weaker than the positive result in Example 1.
Pro Tip: Compare the absolute values when you only care about strength. Here, 0.9985 is nearly as strong as 0.9993, even though its direction is negative.
Example 3: Interpret a Weak Correlation
A coefficient near zero needs a different reading.
Below is the dataset. Columns A and B contain newsletter opens and webinar registrations for nine marketing events.
We want to check whether higher newsletter opens line up with more webinar registrations.
Here is the formula to enter in E2:
=PEARSON(A2:A10,B2:B10)
The formula returns -0.3235. This indicates a weak negative linear relationship in the nine events shown.
Newsletter opens and registrations do not follow a tight straight-line pattern here. A larger sample could also produce a different coefficient.
A value near zero only describes the linear relationship. It does not rule out a curved pattern or a relationship influenced by other variables.
Pro Tip: Plot the paired values in a scatter chart before interpreting the coefficient. Outliers and curved patterns can be hard to spot from PEARSON alone.
Example 4: Calculate Correlation by Region
This final example calculates the coefficient for one selected group.
Below is the dataset. Columns A:C contain region, coaching hours, and renewal scores. Cell E2 holds the region to analyze.
We want the correlation between coaching hours and renewal scores for the West region only.
Here is the formula to enter in F2:
=PEARSON(FILTER(B2:B11,A2:A11=E2),FILTER(C2:C11,A2:A11=E2))
Each FILTER call returns values from the rows where column A matches E2. PEARSON then reduces those two filtered arrays to one coefficient.
With West in E2, the formula returns 0.9988. Coaching hours and renewal scores have a strong positive linear relationship within those five West rows.
Changing the region in E2 makes both FILTER calls select the corresponding rows before PEARSON recalculates.
Pro Tip: FILTER is available in Excel 2021, Excel 2024, Microsoft 365, and Excel for the web. Keep the selector valid because no matching rows return an error here.
Tips & Common Mistakes
- Keep observations paired. The first value in
array1must belong with the first value inarray2, and the same pairing must continue down both ranges. - Use equal-sized arrays. If the arrays contain different numbers of data points, PEARSON returns
#N/A. - Keep some variation in both arrays. If every numeric value in either array is identical, the standard deviation is zero and PEARSON returns #DIV/0!.
- Know what Excel ignores. Text, logical values, and empty cells in reference arguments are ignored. Cells containing zero remain part of the calculation.
- Do not treat correlation as causation. A high coefficient measures a linear association in the supplied data. It does not establish why the variables move together.
- Check the shape. A coefficient near zero can hide a curved relationship. A scatter chart also helps you spot outliers that distort the result.
- Use CORREL when you prefer its name. CORREL and PEARSON calculate the same correlation coefficient in current Excel versions. Neither is a newer replacement for the other.
I covered how to calculate and interpret positive, negative, weak, and group-specific Pearson correlation coefficients.
I hope you found this article helpful.
Other Excel articles you may also like:
