How to Use the CORREL and PEARSON Functions in Google Sheets to Calculate Correlation Coefficients

Determining the statistical relationship between two numeric variables is one of the most common analytical tasks in data analysis. Whether evaluating whether increased marketing spend correlates with product sales, or checking whether study hours predict exam scores, understanding the direction and strength of linear relationships is vital.

In Google Sheets, the CORREL and PEARSON functions compute the Pearson product-moment correlation coefficient ($r$) directly from paired cell ranges. In this guide, explore how to apply these functions, interpret correlation coefficients accurately, handle missing values, and combine them with dynamic array formulas to analyse complex spreadsheets.

Understanding Pearson Correlation in Google Sheets

The Pearson correlation coefficient ($r$) quantifies the degree of linear association between two continuous datasets. The resulting coefficient always falls within a rigid mathematical boundary between -1.0 and +1.0:

Correlation Value ($r$)	Statistical Meaning	Real-World Interpretation
+0.7 to +1.0	Strong positive correlation	As variable X increases, variable Y reliably increases.
+0.3 to +0.7	Moderate positive correlation	A discernible upward trend, with noticeable variation.
-0.3 to +0.3	Weak or negligible correlation	No discernible linear relationship between the two variables.
-0.3 to -0.7	Moderate negative correlation	As variable X increases, variable Y tends to decrease.
-0.7 to -1.0	Strong negative correlation	As variable X increases, variable Y consistently falls.

CORREL vs PEARSON: Which Should You Use?

In Google Sheets (as in Microsoft Excel), CORREL and PEARSON are mathematically identical. Both execute the Pearson product-moment correlation formula:

r = Σ((x - x̄)(y - ȳ)) / sqrt(Σ(x - x̄)² * Σ(y - ȳ)²)

The syntax for both functions is completely interchangeable:

=CORREL(data_y, data_x)
=PEARSON(data_y, data_x)

Basic Example: Evaluating Marketing Spend vs Revenue

Consider an enterprise table where Column A lists monthly ad spend (in pounds) across cells A2:A13, and Column B lists monthly revenue across cells B2:B13.

To determine how closely revenue tracks advertising expenditure, enter this formula in an empty cell:

=CORREL(A2:A13, B2:B13)

If the formula returns 0.884, there is a strong positive correlation between advertising expenditure and revenue across the sample period.

Handling Missing Values and Text in Google Sheets

In messy datasets containing empty cells, text entries, or notes, CORREL and PEARSON behave with specific rules:

• Empty cells and text entries: Automatically ignored by Google Sheets. The calculation pairs only rows where both Column A and Column B contain valid numeric numbers.
• Unequal Range Lengths: If range dimensions do not match (e.g. CORREL(A2:A10, B2:B12)), Google Sheets immediately returns a #N/A error (“Arguments must have the same size”).
• Zero Variance (Constant Data): If all values in either range are identical (for instance, every cell in Column A is 100), the standard deviation is zero. Because division by zero is undefined, CORREL returns a #DIV/0! error.

Advanced Application: Calculating a Multi-Variable Correlation Matrix

When analysing survey results or financial portfolios containing several columns (e.g., Stocks, Bonds, Real Estate, Commodities in Columns B2:E50), analysts frequently construct a Correlation Matrix.

Building the Matrix Grid

1. List the variable headers along row 1 (Cells G1:J1: Stocks, Bonds, Real Estate, Commodities).
2. List the same variable headers vertically in column F (Cells F2:F5).
3. In cell G2, construct an absolute-referenced formula that can be dragged across the grid:

=CORREL(INDEX($B$2:$E$50, 0, MATCH($F2, $B$1:$E$1, 0)), INDEX($B$2:$E$50, 0, MATCH(G$1, $B$1:$E$1, 0)))

Copy this formula across the grid. The diagonal entries (where a variable correlates with itself) will return 1.000, while the remaining cells provide instantaneous pairwise correlation coefficients.

Combining CORREL with FILTER for Segmented Analysis

Often, you need to calculate correlation for a specific subset of data without re-sorting or manually copying rows. You can nest the FILTER function directly inside CORREL.

For example, to calculate the correlation between ad spend (A2:A100) and revenue (B2:B100) only for the “Retail” department (where Column C equals “Retail”):

=CORREL(FILTER(A2:A100, C2:C100 = "Retail"), FILTER(B2:B100, C2:C100 = "Retail"))

Google Sheets filters both arrays synchronously before passing the matching paired vectors to CORREL.

Crucial Data Pitfalls: Correlation vs Causation

While CORREL accurately measures linear alignment, remember key statistical limitations when reporting findings to stakeholders:

Analytical Pitfall	Why It Occurs	Recommended Safeguard
Confounding Third Variables	Two variables move together due to an unmeasured external driver (e.g. ice cream sales and sunburns driven by temperature).	Investigate underlying business drivers before declaring causality.
Non-Linear Relationships	Variables have an inverted-U or exponential curve; CORREL may report $r \approx 0$ despite strong non-linear relationships.	Plot data using a Scatter Chart (Insert > Chart > Scatter) to visually check distribution shapes.
Outlier Sensitivity	A single severe outlier can artificially inflate or deflate the coefficient.	Filter out extreme data points or audit data using trimmed ranges.

Enhancing Quantitative Insight in Spreadsheets

By leveraging the CORREL and PEARSON functions alongside scatter plots and trendlines, you can rapidly audit business metrics, validate predictive hypotheses, and identify collinear variables in Google Sheets without needing specialised statistical software.