Correlation Between Two Columns

Excel Formulas › Statistics

All versionsCORREL

Correlation measures how strongly two things move together — ad spend vs sales, temperature vs ice-cream orders. CORREL returns a single number from −1 to +1 that captures the relationship.

Quick formula: for two columns of paired values:
=CORREL(B2:B100, C2:C100)
+1 = perfect positive, 0 = no linear relationship, −1 = perfect negative.

Functions used (tap for the full reference guide):

CORRELRSQ

The example

Ad spend vs sales — do they rise together?

AB
1Ad spendSales
2$10$120
3$20$170
4$30$210
5$40$280
6Correlation:0.99

The formula

The correlation between spend and sales:

=CORREL(A2:A5, B2:B5) // ≈ 0.99 → strong positive

How it works

One number summarizes the relationship:

  1. CORREL compares how the two columns vary together, scaled to a value between −1 and +1.
  2. Here spend and sales climb together almost perfectly — 0.99, a strong positive correlation.
  3. A value near 0 means no linear relationship; near −1 means as one rises the other falls.
  4. Square it for R² (=RSQ()) — the share of variation in one column explained by the other.

Correlation is not causation. A high CORREL means the two move together — not that one causes the other. A hidden third factor (or coincidence) can drive both.

Try it: interactive demo

Live demo

Adjust the last sales value; watch the correlation change.

Correlation:

Variations

R-squared

Share of variance explained:

=RSQ(B2:B100, A2:A100)

Correlation matrix

Pairwise CORREL across several columns builds a matrix (or use the Data Analysis ToolPak).

Slope of the relationship

How much Y changes per unit of X:

=SLOPE(B2:B100, A2:A100)

Pitfalls & errors

Correlation captures only linear relationships. A strong curved (U-shaped) link can show a CORREL near 0. Always eyeball a scatter plot too.

Ranges must be the same size and paired row-by-row. Mismatched lengths return #N/A.

Outliers distort it. A single extreme pair can pull the correlation up or down dramatically.

Practice workbook

📊
Download the free Correlation Between Two Columns practice workbook
Paired ad-spend/sales data with live CORREL, RSQ, and SLOPE, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate correlation in Excel?
Use =CORREL(range1, range2). It returns a value from -1 to +1: near +1 means the two move up together, near -1 means they move in opposite directions, and near 0 means no linear relationship.
How do I get R-squared?
Square the correlation or use =RSQ(known_y, known_x), which gives the proportion of variation in one variable explained by the other.
Does a high correlation prove causation?
No. Correlation only shows the two variables move together. A third factor or coincidence can cause the relationship, so don't infer causation from CORREL alone.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Linear forecast · Standard deviation · Percentile & quartile

Function references: CORREL · RSQ