How to Use the CORREL Function in Google Sheets to Measure Statistical Correlation Between Datasets

Understanding the mathematical relationship between two variables is essential in data analysis, marketing optimisation, and financial modelling. Does increasing advertising spend directly drive higher conversion volumes? Does website page loading time negatively correlate with customer checkout completion? In Google Sheets, the CORREL function calculates the Pearson correlation coefficient (r), providing a quantitative metric that defines the strength and direction of the linear relationship between two datasets.

Syntax and Mathematical Foundations of CORREL

The syntax for the CORREL function requires two datasets of equal length:

=CORREL(data_y, data_x)

• data_y: The first range or array of numeric values (e.g., advertising expenditure in A2:A50).
• data_x: The second range or array of numeric values (e.g., revenue generated in B2:B50).

Because the Pearson correlation formula is mathematically symmetric, swapping data_y and data_x returns the exact same result.

Interpreting Correlation Coefficient Values

The CORREL function always produces a value ranging between -1.0 and +1.0:

• +1.0 (Perfect Positive Correlation): As variable X increases, variable Y increases in exact linear proportion.
• +0.7 to +0.9 (Strong Positive Correlation): High values of X strongly correspond with high values of Y.
• 0.0 (No Linear Correlation): No discernible linear relationship exists between the two datasets.
• -0.7 to -0.9 (Strong Negative Correlation): High values of X strongly correspond with low values of Y.
• -1.0 (Perfect Negative Correlation): As variable X increases, variable Y decreases in exact inverse proportion.

Step-by-Step Example: Evaluating Marketing Spend vs Sales

Suppose you have 10 months of campaign metrics recorded in your sheet:

• Column A (cells A2:A11): Monthly digital ad spend (ranging from $2,000 to $10,000).
• Column B (cells B2:B11): Total units sold (ranging from 150 to 950).

To measure the strength of the relationship, enter this formula in cell C2:

=CORREL(A2:A11, B2:B11)

If the formula returns 0.88, you have statistical evidence of a strong positive linear correlation, confirming that increases in marketing budgets correspond reliably with higher product sales volume.

Handling Missing Values, Text, and Errors

When computing correlations with real-world spreadsheets, keep the following data hygiene rules in mind:

• Mismatched Dimensions: If data_y and data_x have different row counts, CORREL returns a #N/A error.
• Text Ignored: Any text strings or blank cells within the target ranges are automatically ignored.
• Zero Variance: If all numbers in one range are identical (e.g., every value is 100), the dataset has zero variance, causing the formula to return a #DIV/0! error.
• Correlation Does Not Equal Causation: A strong correlation coefficient indicates a statistical relationship, not necessarily a direct causal mechanism. Always evaluate external confounding factors.