Correlation measures how strongly two sets of numbers move together. If sales rise when advertising rises, the two are positively correlated. If the number of umbrellas sold falls when temperature rises, they are negatively correlated. Excel gives you the correlation coefficient, also called r, with a single function named CORREL. The value always lies between minus 1 and plus 1, which makes it easy to read. This guide explains what the coefficient means, shows a worked example with five pairs of numbers that you can check by hand, covers PEARSON, R-squared, covariance, slope and intercept, shows how to draw a scatter plot with a trendline, explains how to interpret the strength of a relationship, and lists the common mistakes, including the warning that correlation is not causation.
Updated November 2026
In this guide
- What is correlation?
- The CORREL function
- Worked example
- R-squared
- Covariance
- Slope, intercept and prediction
- How to read the strength
- Draw a scatter plot with a trendline
- Correlation matrix for many variables
- Correlation is not causation
- Real-world examples
- Tips for reliable results
- Common mistakes
- Practice problems
- Quick recap
What is correlation?
The correlation coefficient describes the direction and strength of a straight line relationship between two variables. A value of 1 means a perfect positive line, minus 1 means a perfect negative line, and 0 means no straight line relationship. Values in between show a weaker link. The most common version is the Pearson coefficient, and it is the one that Excel calculates.
The CORREL function
The syntax is =CORREL(array1, array2). Both ranges must have the same number of values, and they must be in matching order, so that the first value of one range is paired with the first value of the other. Cells that contain text or are empty are ignored in a pair, and a mismatch in size returns an error. The function PEARSON gives the same result, and you can use either of them.
Worked example
Let us use five pairs. The values of X are 1, 2, 3, 4 and 5 (hours studied). The values of Y are 2, 4, 5, 4 and 6 (test score divided by ten). The mean of X is 3 and the mean of Y is 4.2. The deviations of X are minus 2, minus 1, 0, 1 and 2. The deviations of Y are minus 2.2, minus 0.2, 0.8, minus 0.2 and 1.8. The products of the deviations are 4.4, 0.2, 0, minus 0.2 and 3.6, and their sum is 8. The sum of the squared deviations of X is 10, and for Y it is 8.8. The coefficient is 8 divided by the square root of 10 times 8.8, which is 8 divided by 9.3808, so 0.8528.
| X | Y |
|---|---|
| 1 | 2 |
| 2 | 4 |
| 3 | 5 |
| 4 | 4 |
| 5 | 6 |
In Excel, with X in A2:A6 and Y in B2:B6, the formula =CORREL(A2:A6,B2:B6) returns 0.8528, a strong positive correlation.
R-squared
Squaring r gives R-squared, which is the share of the variation in Y that is explained by the straight line relationship with X. For our data, 0.8528 squared is 0.7273, so about 72.7 percent of the variation in Y is explained by X. In Excel use =RSQ(B2:B6,A2:A6) or =CORREL(A2:A6,B2:B6)^2. Note that RSQ takes the Y values first.
Covariance
Covariance shows the direction of the relationship but not the strength, as it depends on the units. For our data the sum of the products is 8. The sample covariance is 8 divided by 4, which is 2, and in Excel it is =COVARIANCE.S(A2:A6,B2:B6). The population covariance is 8 divided by 5, which is 1.6, and it is =COVARIANCE.P(A2:A6,B2:B6). The correlation is the covariance divided by the product of the two standard deviations, which are 1.5811 and 1.4832 for the sample, so 2 divided by 2.3452 is 0.8528.
Slope, intercept and prediction
The line of best fit has a slope equal to the sum of the products divided by the sum of squared deviations of X, which is 8 divided by 10, so 0.8. The intercept is the mean of Y minus the slope times the mean of X, which is 4.2 minus 2.4, so 1.8. In Excel, =SLOPE(B2:B6,A2:A6) returns 0.8 and =INTERCEPT(B2:B6,A2:A6) returns 1.8. To predict the score for 6 hours, use 1.8 plus 0.8 times 6, which is 6.6, or =FORECAST.LINEAR(6,B2:B6,A2:A6). Use predictions inside the range of your data, since a line may not hold far outside it.
How to read the strength
There is no single rule, but this table is a common guide.
| Absolute value of r | Strength |
|---|---|
| 0.00 to 0.19 | Very weak |
| 0.20 to 0.39 | Weak |
| 0.40 to 0.59 | Moderate |
| 0.60 to 0.79 | Strong |
| 0.80 to 1.00 | Very strong |
The sign shows the direction. Our value of 0.8528 is a very strong positive relationship, though with only five pairs it is not reliable. A small sample can give a high value by chance, so use more data for real decisions.
Draw a scatter plot with a trendline
A chart shows what the number cannot. Select both columns, go to Insert, choose Scatter, and pick the first option. Click the plus icon next to the chart and tick Trendline. In the trendline options tick Display Equation on chart and Display R-squared value on chart. For our data the chart shows y equal to 0.8x plus 1.8 and R squared equal to 0.7273. If the points form a curve, or one outlier pulls the line, the plot shows it, so always look at the chart before you trust r.
Correlation matrix for many variables
With more than two columns, the Data Analysis ToolPak can calculate all the pairs at once. Enable the ToolPak under Options and Add-ins, then go to Data, Data Analysis, Correlation, choose the input range, tick Labels in first row and click OK. The result is a table with ones on the diagonal and the coefficient for each pair. You can colour it with conditional formatting to find the strongest links quickly.
Correlation is not causation
A high coefficient does not prove that one variable causes the other. Ice cream sales and sunburn are correlated, but both are caused by hot weather. A third factor, a coincidence or a reverse cause may explain the link. Use correlation to find relationships worth studying, and use experiments or strong reasoning to claim a cause. When you report the result, say that the variables are associated and not that one produces the other.
Real-world examples
A shop owner can correlate the monthly advertising spend with sales to see if it is worth continuing. A teacher can correlate the hours of study with the exam score. A freelancer can correlate the number of proposals sent with the number of orders won. A farmer can correlate rainfall with yield. In each case, the coefficient gives a quick view, and the scatter plot and common sense complete the picture. For related measures, see our standard deviation guide and variance guide.
Tips for reliable results
Clean the data before you calculate. Remove duplicate rows, fix typing errors and make sure that every pair belongs to the same record, such as the same month or the same customer. Check for outliers by sorting the columns or by looking at the scatter plot, and decide whether an extreme point is a real value or a mistake. Report the number of pairs with the coefficient, for example r equal to 0.85 with n equal to 5, so the reader can judge how much trust to place in it. If the data is not roughly linear, consider the rank based method or a transformation, and always describe what the variables mean in plain words.
Common mistakes
The first mistake is to use ranges of different size. The second is to mix up the order in RSQ, SLOPE and INTERCEPT, which take the Y values first. The third is to read a high value as proof of cause. The fourth is to use correlation for a curved relationship, where r can be near zero even when the link is strong. The fifth is to ignore outliers, which can change r a lot. The sixth is to calculate it from a tiny sample. The seventh is to forget that text in a cell breaks the pairing.
Practice problems
Try these. Problem 1: what is the correlation of X equal to 1, 2, 3 and Y equal to 2, 4, 6? It is exactly 1, because the points lie on a line. Problem 2: what is it for Y equal to 6, 4, 2? It is minus 1. Problem 3: if r equals 0.5, what is R-squared? It is 0.25. Problem 4: with a slope of 0.8 and an intercept of 1.8, what is the prediction for X equal to 10? It is 9.8. Problem 5: build a scatter plot for your own data and compare the trendline with CORREL.
Quick recap
Use CORREL to get the correlation coefficient, which ranges from minus 1 to 1. Square it for R-squared, or use RSQ. Use SLOPE, INTERCEPT and FORECAST.LINEAR to build a prediction line, and a scatter plot with a trendline to check the shape. Read the strength with a guide, keep the sample large enough, and remember that correlation does not prove causation. You can also see how far each value lies from the average with our z-score guide.
Frequently asked questions
How do I calculate correlation in Excel?
Use =CORREL(range1, range2).
What is the difference between CORREL and PEARSON?
They give the same result.
What does R-squared mean?
It is the share of the variation in one variable that is explained by the other.
What is a good correlation value?
Above 0.6 or below minus 0.6 is usually considered strong, but it depends on the field.
Does correlation mean causation?
No, a link does not prove a cause.


