Understanding the Pearson Correlation Coefficient in Google Sheets

August 7th, 2026

When two numeric columns move together — ad spend and signups, temperature and ice cream sales, study hours and scores — you often want a single number for the strength of that linear relationship. The Pearson correlation coefficient does exactly that. In Google Sheets, PEARSON and CORREL compute it; covariance functions measure related but different scale-dependent association. This guide covers the formulas, a simple two-column example, how correlation differs from covariance, and the caveats that keep you from over-reading a high r.

What Pearson’s r tells you

Pearson’s correlation coefficient r is always between -1 and 1:

| r | Rough reading | | --- | --- | | near 1 | Strong positive linear relationship | | near 0 | Little or no linear relationship | | near -1 | Strong negative linear relationship |

Positive means: as X goes up, Y tends to go up. Negative means: as X goes up, Y tends to go down. The coefficient is scale-free (unitless): correlating height in cm with weight in kg gives the same r as height in inches with weight in pounds, as long as the pairing is the same.

Important: Pearson captures linear association. A perfect U-shaped curve can produce r near 0 even though X and Y are tightly related in a nonlinear way. Always plot when you can.

PEARSON and CORREL — same job

PEARSON and CORREL are equivalent for practical purposes: both return Pearson’s r for two ranges of equal length.

=PEARSON(A2:A30, B2:B30)
=CORREL(A2:A30, B2:B30)

Use whichever name your team recognizes. Finance templates often say CORREL; statistics-minded sheets often say PEARSON. Results should match for the same inputs.

Alignment rules

• Both ranges must have the same number of rows (paired observations).
• Pairs with blank or non-numeric cells are skipped in a pair-wise fashion (a missing X drops that row’s contribution).
• Constant data (zero variance in X or Y) makes correlation undefined → error.

A simple two-column example

Suppose weekly ad spend (USD) is in A2:A9 and signups in B2:B9:

| Week | Spend (A) | Signups (B) | | --- | --- | --- | | 1 | 100 | 12 | | 2 | 150 | 18 | | 3 | 200 | 22 | | 4 | 120 | 14 | | 5 | 300 | 35 | | 6 | 250 | 28 | | 7 | 180 | 20 | | 8 | 220 | 25 |

=PEARSON(A2:A9, B2:B9)

You should get a high positive r (well above 0.9 for this toy pattern) — spend and signups rise together roughly linearly. Change one week to a huge spend with almost no signups and r drops: correlation is sensitive to outliers.

Format the result with 2–3 decimal places (0.00 or 0.000); more digits rarely add meaning.

Covariance vs correlation

COVAR (and related covariance functions such as COVARIANCE.P / COVARIANCE.S depending on product dialect) measure how two variables co-vary in the units of X×Y. Double every value of Y and covariance scales up; correlation does not.

=COVAR(A2:A9, B2:B9)
=PEARSON(A2:A9, B2:B9)

Rules of thumb:

• Use correlation to answer “how strongly do they move together on a -1…1 scale?”
• Use covariance inside further math (portfolio variance, some multivariate formulas) where units and scale are intentional.
• For exploration and dashboards, prefer PEARSON/CORREL — stakeholders interpret -1…1 far more easily than raw covariance.

Population vs sample covariance variants differ by a scaling factor (n vs n−1). That choice matters for unbiased estimators in statistics classes; it does not change the fact that covariance is still scale-dependent while Pearson’s r standardizes by the standard deviations of X and Y.

Interpreting r without overclaiming

Correlation is not causation

A high r between ice cream sales and drowning incidents does not mean ice cream causes drowning. A third factor (hot weather) can drive both. Sheets will happily compute r either way — judgment is on you.

Strength is context-dependent

In physics lab data, r = 0.7 might look weak. In noisy social data, r = 0.3 might be notable. Do not paste arbitrary cutoffs (“above 0.5 = strong”) into every domain.

Outliers dominate

One bad pair can inflate or destroy r. Spot-check with a scatter chart (Insert → Chart → Scatter) and consider robust checks or winsorizing when a single point runs the show.

Nonlinear relationships

Quadratic or exponential links can hide from Pearson. If the scatter looks curved, consider transforming a column (e.g. LOG of size) or using a model that allows nonlinearity — do not trust r alone.

Sample size

r = 0.95 on four points is fragile. r = 0.4 on four thousand points may be real but small. Report n next to r:

=COUNT(A2:A9)
=PEARSON(A2:A9, B2:B9)

Practical patterns

Correlation matrix stub (three metrics)

With metrics in columns B, C, D:

=PEARSON(B2:B100, C2:C100)
=PEARSON(B2:B100, D2:D100)
=PEARSON(C2:C100, D2:D100)

Build a small grid of these for a quick multicollinearity check before regression-style work.

Rolling correlation (recent relationship)

If dates are ordered, correlate only the last 12 rows:

=PEARSON(OFFSET(A2, COUNTA(A2:A)-12, 0, 12), OFFSET(B2, COUNTA(B2:B)-12, 0, 12))

(Adjust if your ranges include headers or blanks; FILTER with a date window is often clearer.)

Flag weak drivers

=IF(ABS(PEARSON(A2:A50, B2:B50))<0.2, "Weak linear link", "Check further")

Useful as a gate before spending time on charts — not as automatic truth.

Common mistakes

• Mismatched range sizes. PEARSON(A2:A10, B2:B12) errors or mis-pairs — keep lengths equal.
• Including headers or labels. Text in the range can cause errors; start at the first numeric row.
• Correlating a column with itself as a “sanity check” without noticing. That always yields 1 (if variance is nonzero) — fine for tests, useless as analysis.
• Reading r = 0 as “independent.” It means no linear correlation, not independence in a general sense.
• Using covariance when you meant correlation in a slide deck. Audiences will misread units; convert to PEARSON for communication.

Which function should you pick?

| Goal | Function | | --- | --- | | Linear association, -1…1 | PEARSON or CORREL | | Co-movement in raw units | COVAR / covariance variants | | Visual check | Scatter chart + optional trendline | | Nonlinear suspicion | Chart first; consider transforms |

Going further

Pearson’s r is a fast, standard measure of linear co-movement — perfect for exploratory analysis in Google Sheets via PEARSON or CORREL. Pair it with scatter plots, report sample size, and remember that correlation is not causation and not a full model of dependence. For covariance when you need it, see COVAR. For a broader stats overview, read Understanding Statistical Functions in Google Sheets and Statistical Distributions and Hypothesis Testing in Google Sheets.