How Do You Calculate the Correlation Coefficient in Excel? (2026 Guide)

You have two columns of numbers and a suspicion that they move together. Ad spend and signups. Page speed and bounce rate. Temperature and sales. The question is whether that suspicion survives contact with the data, and the correlation coefficient is the fastest honest answer Excel can give you.

It is also one of the few statistics Excel makes genuinely easy. There is a single function that does the whole job, it takes two arguments, and it works the same way in every version of Excel released in the last decade. No add-in, no setup, no pivot table.

The complications are not in the calculation. They are in knowing which of Excel's four overlapping correlation tools to reach for, and in reading the number you get back without drawing a conclusion the data does not support. This guide covers both.

How Do You Calculate the Correlation Coefficient in Excel?

Use the CORREL function. Type `=CORREL(array1, array2)` into any empty cell, where array1 and array2 are your two ranges of numbers, and press Enter. If your data sits in A2:A50 and B2:B50, the formula is `=CORREL(A2:A50, B2:B50)`.

Excel returns a single number between -1 and 1. That number is the Pearson correlation coefficient, usually written as r. It tells you how closely the two columns move in a straight line together, and in which direction.

That is the whole procedure for one pair of variables. It works in Excel for Windows, Excel for Mac, Excel for the web and Excel on mobile, with no add-ins enabled.

If you need to compare more than two variables at once, CORREL becomes tedious quickly, and you want the Data Analysis ToolPak's Correlation tool instead. That produces a matrix showing every pairing in one table. Both routes are covered below.

Not sure which pages are actually driving your traffic? Get a free SEO audit and see the numbers behind your rankings.

What the Correlation Coefficient Actually Measures

Before the mechanics, it is worth being precise about what the number represents, because this is where most spreadsheet analysis goes wrong.

The Pearson correlation coefficient measures the strength and direction of a linear relationship between two variables. It answers one narrow question: if you plotted these two columns against each other, how close would the points sit to a single straight line?

Reading the Number

The coefficient always falls between -1 and 1, and the magnitude is what matters for strength.

• 1.0 is a perfect positive relationship. Every increase in one variable comes with a proportional increase in the other.
• 0.7 to 0.9 is a strong relationship. The pattern is obvious on a scatter plot.
• 0.4 to 0.6 is moderate. There is a real pattern, but plenty of scatter around it.
• 0.1 to 0.3 is weak. You would struggle to see it by eye.
• 0 means no linear relationship at all.

Negative values mirror this exactly. An r of -0.8 is just as strong as an r of 0.8, it simply runs the other way: as one variable rises, the other falls.

Positive, Negative and Zero

A positive coefficient means the two variables move in the same direction. More ad spend, more signups. A negative coefficient means they move in opposite directions. More page load time, fewer conversions.

A coefficient near zero is the one people misread most often. It does not mean the variables are unrelated. It means they are not related in a straight line. A variable that rises and then falls, a classic inverted U, can produce a correlation of almost exactly zero while having a perfectly clear relationship.

Correlation Is Not Causation

This is repeated so often it has become background noise, but it matters in a spreadsheet more than anywhere, because Excel will happily return a confident-looking 0.91 for two columns that have nothing to do with each other.

Ice cream sales and drowning incidents correlate strongly. Neither causes the other; summer causes both. Excel cannot see the third variable. Only you can.

Want to know which of your pages AI search engines are citing? Run a free audit and find out.

Method 1: The CORREL Function, Step by Step

This is the method you will use most of the time.

1. Put your two variables in two columns, one observation per row. The ranges must be the same length.
2. Click an empty cell where you want the result.
3. Type `=CORREL(` and select the first range with your mouse, or type it.
4. Type a comma, then select the second range.
5. Close the bracket and press Enter.

The result appears immediately. To format it sensibly, select the cell and increase the decimal places to two or three, since correlation coefficients are conventionally reported to two or three decimals.

A worked example: if column A holds monthly ad spend in B2:B13 and column C holds monthly signups in C2:C13, then `=CORREL(B2:B13, C2:C13)` returning 0.84 tells you that months with higher ad spend have reliably had higher signups, with a strong linear pattern.

One detail worth knowing: CORREL ignores rows where either cell is empty, contains text or holds a logical value. It does not treat blanks as zeros. Rows containing a zero, however, are included, which is the correct behaviour but occasionally surprises people who used a zero as a placeholder for missing data.

Method 2: PEARSON, RSQ and the Chart Trendline

Excel gives you three more routes to the same territory, and knowing what each adds saves time.

PEARSON

The PEARSON function takes the same arguments as CORREL and returns the same value. `=PEARSON(A2:A50, B2:B50)` and `=CORREL(A2:A50, B2:B50)` are interchangeable in practice.

Microsoft maintains both for backward compatibility. CORREL is the one to use in new work, partly because it is the name most people recognise and partly because older builds of Excel had a rounding bug in PEARSON that CORREL did not share.

RSQ and R-Squared

RSQ returns the coefficient of determination, written R squared. It is simply the correlation coefficient squared, so `=RSQ(A2:A50, B2:B50)` gives the same result as `=CORREL(A2:A50, B2:B50)^2`.

R squared is easier to explain to a non-technical audience because it has a direct interpretation: it is the proportion of variation in one variable that the other variable accounts for. An r of 0.8 gives an R squared of 0.64, which you can report as "ad spend accounts for about 64% of the month-to-month variation in signups."

Note that R squared is always positive, so it loses the direction of the relationship. Report it alongside r, not instead of it.

The Chart Trendline

If you want to see the relationship rather than just measure it, build a scatter plot.

1. Select both columns.
2. Go to Insert, then Charts, then the scatter chart icon, and pick the plain markers-only option.
3. Right-click any data point and choose Add Trendline.
4. In the pane that opens, tick Display R-squared value on chart.

This is the single most useful diagnostic step in the whole process, because it shows you the shape of the relationship. A curved cloud of points with a low r is a completely different finding from a random scatter with a low r, and only the chart distinguishes them.

Method 3: The Data Analysis ToolPak Correlation Matrix

When you have five or ten variables and want every pairing at once, CORREL means writing dozens of formulas. The ToolPak does it in one dialog.

The Analysis ToolPak ships with Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016 on both Windows and Mac, but it is switched off by default and it is not available in Excel for the web.

To enable it on Windows, go to File, then Options, then Add-ins. In the Manage box at the bottom select Excel Add-ins, click Go, tick Analysis ToolPak and click OK. On Mac, go to Tools, then Excel Add-ins, tick Analysis ToolPak and click OK. A Data Analysis button then appears at the right-hand end of the Data tab.

Once it is enabled:

1. Arrange your variables in adjacent columns, each with a header row.
2. Click Data, then Data Analysis, then Correlation, then OK.
3. In Input Range, select all the columns including the headers.
4. Set Grouped By to Columns.
5. Tick Labels in first row so the output is readable.
6. Choose an output location and click OK.

Excel produces a lower-triangular matrix with your variable names down the side and across the top. The diagonal is all 1s, because every variable correlates perfectly with itself. Every other cell is the correlation between that row and that column.

Read it by scanning for the extreme values. Anything above 0.7 or below -0.7 is worth investigating; the rest is usually noise.

Curious how your site compares to competitors? Get a free SEO audit and see where the gaps are.

When to Use Each Method

The choice is mostly about how many variables you have and what you plan to do with the answer.

Use CORREL for a single pair of variables, or when the result needs to live in a dashboard and update automatically as new data arrives. Because it is a formula rather than a static output, it recalculates the moment the underlying range changes.

Use RSQ when you are reporting to people who find percentages easier than coefficients, and pair it with r so the direction is not lost.

Use the scatter plot with a trendline whenever you are seeing the data for the first time. It is the only method that shows you the shape rather than a summary of it.

Use the ToolPak matrix for exploratory work across many variables, or when you are hunting for which pairs are worth a closer look. Its one real drawback is that the output is static: change your data and the matrix does not update, so you have to run it again.

Limitations and Common Mistakes

Outliers Distort Everything

A single extreme point can drag the coefficient dramatically. Ten well-behaved observations with an r of 0.2 can become an r of 0.8 with one outlier added at the far corner of the plot. This is the strongest argument for always drawing the scatter plot before trusting the number.

It Only Sees Straight Lines

A relationship that is real but curved will show a low correlation coefficient. Exponential growth, diminishing returns and threshold effects all fall into this trap. If the chart shows an obvious curve and the coefficient says nothing is there, believe the chart.

Blank Cells and Text Are Silently Skipped

CORREL drops any row where either value is missing or non-numeric. It does not warn you. If you intended those rows to count as zeros, you will get a different answer than you expected, and the only way to catch it is to check the row count yourself.

Small Samples Produce Big Coefficients

With five data points you can get an r above 0.8 from pure chance. Correlation coefficients calculated on tiny samples are close to meaningless, and Excel gives no indication of statistical significance. As a rough working rule, be sceptical of anything below about 20 observations and treat anything below 10 as decorative.

Range Restriction

If your data only covers a narrow slice of the possible values, the correlation will look weaker than it is across the full range. Measuring the relationship between ad spend and signups using only your three highest-spend months will understate it.

Excel vs the Alternatives

For a single coefficient, Excel is the right tool and nothing else is faster. CORREL is one function, it is already installed, and the answer is on screen in about four seconds.

Google Sheets implements CORREL, PEARSON and RSQ identically, so formulas port across without modification. Sheets has no equivalent of the ToolPak's correlation matrix, though, so multi-variable work means either writing the formulas out or using an add-on.

For serious statistical work, Excel starts to run out of road. It gives you no p-value, no confidence interval and no significance test alongside the coefficient, which means you cannot tell from Excel alone whether an r of 0.4 is a real finding or sampling noise. R, Python's pandas and SPSS all return those by default.

Where Excel stays ahead is in the last mile. The result sits in a cell next to the data it came from, it updates live, and anyone receiving the file can see exactly how it was calculated. For most business reporting that transparency is worth more than the extra statistics.

Want the same clarity about your search performance? Get a free SEO audit and see exactly which pages are pulling their weight.

Final Thoughts

The calculation itself is a single function, and if you take only one thing from this guide, make it `=CORREL(range1, range2)`.

The judgement is the harder part. Draw the scatter plot before you trust the number. Check how many rows actually went into the calculation. Ask what third variable might be driving both columns before you write a conclusion. A correlation coefficient is a starting point for a question, not the answer to one.

If the ToolPak route sounds useful and the Data Analysis button is not showing up on your Data tab, our guide to getting the Data Analysis ToolPak in Excel walks through enabling it on both Windows and Mac, along with what else the add-in unlocks once it is on.