Find the Correlation Between Two Columns in Excel

To find the correlation between two columns in Excel, use =CORREL(range1, range2). It returns one number from -1 to +1 that tells you how closely the two columns move together in a straight line. This guide is for analysts, students and job seekers who need to answer "are these two things related?" quickly, and it shows how to calculate correlation in Excel, read the result, and check it properly.

What correlation means#

Correlation measures how strongly two sets of numbers move together. Excel's CORREL function gives the Pearson correlation coefficient, usually written as r.

The bands in the middle are a common rule of thumb, not a fixed law. A value of 0.5 might be strong in one field and weak in another.

How to use CORREL in Excel#

CORREL takes two ranges of the same size and compares them row by row. The syntax is:

=CORREL(array1, array2)

Here is six months of ad spend (in thousands) and sales (in thousands) in A1:C7:

1. Click an empty cell, such as E2.
2. Type =CORREL(B2:B7,C2:C7) and press Enter.
3. Format the cell to two decimal places if you like.

The result is about 0.88. That is a strong positive correlation: months with more ad spend tended to have higher sales. The order of the two ranges doesn't matter, so =CORREL(C2:C7,B2:B7) gives the same answer.

CORREL works in every current version of Excel, on Windows and Mac. The older PEARSON function returns exactly the same number.

How to read a negative correlation#

A negative result means that as one column goes up, the other goes down. Take a product tested at six prices:

With prices in B2:B7 and units in C2:C7, =CORREL(B2:B7,C2:C7) returns about -0.99. The minus sign gives the direction (higher price, fewer units) and 0.99 gives the strength (very close to a perfect straight line).

Strength is about the size of the number only. A correlation of -0.8 is just as strong as +0.8; it simply runs the other way.

How Excel calculates correlation#

Correlation compares how far each value sits from its own column's average. If both values in a row are above their averages, or both are below, the row pushes r up. If one is above and the other below, it pushes r down. The result is then scaled so it always lands between -1 and +1.

You can build the same number with SUMPRODUCT, which is a good way to see what CORREL does:

=SUMPRODUCT((B2:B7-AVERAGE(B2:B7))*(C2:C7-AVERAGE(C2:C7)))/SQRT(SUMPRODUCT((B2:B7-AVERAGE(B2:B7))^2)*SUMPRODUCT((C2:C7-AVERAGE(C2:C7))^2))

On the ad data this also gives 0.88. The top half adds up the paired distances from the average. The bottom half divides by the spread of each column, which is closely related to the standard deviation. If averages are new to you, start with AVERAGE, MIN and MAX and when to use average vs median.

Check the pattern with a scatter chart#

Correlation only measures straight-line patterns, so always look at the data before you trust the number. A scatter chart is the quickest check:

1. Select both columns of numbers, such as B1:C7.
2. Go to Insert > Charts > Scatter and pick the first scatter style.
3. Click a point, then add a Linear trendline (right-click a point and choose Add Trendline).
4. In the trendline options, tick Display R-squared value on chart.

R-squared is r multiplied by itself. For the ad data, 0.88 × 0.88 is about 0.77, and =RSQ(C2:C7,B2:B7) returns the same value. You can read it as "about 77% of the variation in sales lines up with the variation in ad spend."

Use a scatter chart for this, not a line chart. A line chart is built for values over time, and it spaces its points evenly instead of by their real numbers.

Common mistakes and limits#

Most wrong correlations come from the data, not the formula. Watch for these:

• Rows out of line. CORREL pairs B2 with C2, B3 with C3, and so on. If one column is sorted and the other isn't, the result is meaningless.
• Ranges of different sizes. =CORREL(B2:B7,C2:C8) returns #N/A. Both ranges need the same number of cells.
• No variation. If every value in one column is the same, CORREL returns #DIV/0!.
• Text and blanks. CORREL ignores text, TRUE/FALSE and empty cells, but zeros count. A missing value typed as 0 changes the answer.
• Outliers. One extreme row can push r a long way. Check the scatter chart and the median against the average when a value looks odd.
• Curved patterns. Data that rises and then falls can have an r near 0 even though the link is strong. The scatter chart shows this; CORREL does not.
• Causation. Ice-cream sales and sunburn cases rise together because of sunny weather, not because one causes the other.

Correlate many columns at once#

To compare more than two columns, build a small table of CORREL formulas, or use the Analysis ToolPak. Turn it on under File > Options > Add-ins (Windows) or Tools > Excel Add-ins (Mac), tick Analysis ToolPak, then choose Data > Data Analysis > Correlation. It writes a grid that shows the correlation of every pair of columns. The grid holds fixed values, so run it again if the data changes.

Correlation is a standard interview topic for analyst roles. The guide to Excel skills for data analysts shows where it fits next to lookups, pivots and summary statistics.

Practise it#

Correlation is built on averages, so the fastest way to get comfortable is to practise the basics. Practise AVERAGE, MIN and MAX in a live spreadsheet, then compare MEDIAN and MODE in the next lesson: you type the formulas and the sheet checks them instantly. Then try the exercise on this page and build the 0.88 correlation yourself.