How to calculate the correlation between two columns
✓ Verified in LibreOffice 25.8.7.3Measure how strongly two variables move together — ad spend vs sales, temperature vs usage — as a single number from -1 to +1.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =CORREL(A2:A100,B2:B100) | Returns Pearson's r: +1 perfect positive, 0 none, -1 perfect negative. The two ranges must be the same size. PEARSON() gives the identical result. |
| Google Sheets | =CORREL(A2:A100,B2:B100) | Identical. |
| LibreOffice Calc | =CORREL(A2:A100,B2:B100) | Identical. |
How it works
CORREL returns the Pearson correlation coefficient r, a single number summarising how tightly two columns track each other: here B is always exactly 2×A, a perfectly straight-line relationship, so r = 1. Values near +1 mean they rise together, near -1 mean one falls as the other rises, and near 0 mean no linear relationship. Two cautions that trip people up: r only detects LINEAR patterns (a strong curved relationship can still show r≈0), and correlation is not causation — a high r doesn't prove one variable drives the other. Square it (r²) for the share of variance explained, and pair with a scatter plot to eyeball the shape. Both ranges must be equal length; blank or text cells in a row cause that pair to be skipped.
Verified, not just documented
We ran =CORREL(A2:A4,B2:B4) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 1 — exactly the expected result. Every formula here is confirmed by actually executing it.
Functions used
CORREL — see full Excel, Google Sheets & LibreOffice compatibility for each.