Home>Blogs>Predictive Analytics>Correlation Coefficient in Excel: CORREL, PEARSON and ToolPak
Correlation Coefficient in Excel: CORREL, PEARSON and ToolPak
Predictive Analytics

Correlation Coefficient in Excel: CORREL, PEARSON and ToolPak

The correlation coefficient in Excel tells you, in a single number between -1 and +1, how closely two sets of values move together in a straight line. In this tutorial you calculate it for the height and weight of 15 people with the CORREL and PEARSON functions, see the relationship on a scatter chart, and then build a full correlation matrix for eight sales variables in one click with the Analysis ToolPak. You will also learn how to read the result correctly, including the big rule: correlation is not causation.

Correlation coefficient in Excel: CORREL and PEARSON formulas both return 0.846312379 for height and weight data
CORREL and PEARSON return the same coefficient, 0.846, for the height and weight of 15 people.

⬇️  Download the Correlation Practice File
Three-sheet workbook · .xlsx (no macros) · height and weight data, sales data and a correlation matrix

Watch the Video Tutorial

In the video PK checks the height and weight relationship on a scatter chart, calculates it with CORREL and PEARSON, builds a multi-variable correlation table with the Data Analysis tool and uses SLOPE and INTERCEPT to predict weight.

What You Will Build

  • A correlation coefficient for height and weight with CORREL and PEARSON.
  • Two scatter charts with linear trendlines: one positive and one negative relationship.
  • A correlation matrix for eight sales variables, made with the Analysis ToolPak.
  • A simple prediction of weight from height with SLOPE and INTERCEPT.

What Is the Correlation Coefficient?

The CORREL and PEARSON functions both return Pearson’s correlation coefficient, usually written r. It measures the strength and direction of the linear relationship between two variables:

  • +1 – a perfect positive relationship: when one value goes up, the other goes up along a straight line.
  • -1 – a perfect negative relationship: when one goes up, the other goes down along a straight line.
  • 0 – no linear relationship.

Strength depends on how far r is from zero, in either direction. A value of -0.9 is just as strong as +0.9; only the direction is different. As a rough guide, values beyond ±0.7 are often called strong and values between about -0.3 and +0.3 weak, but what counts as strong depends on your field and data.

Correlation is not causation. A high r only says the two values move together. It does not prove that one causes the other: a third factor may drive both, or the link may be a coincidence, especially with small samples.

Part 1 – Correlation Coefficient with Excel Formulas

Step 1: Set up the data

The Height and Weight sheet holds 15 people: serial number in column A, height in cm in B2:B16 and weight in kg in C2:C16. Heights range from 110 to 174 cm.

Step 2: See the relationship on a scatter chart

Select B1:C16, go to Insert → Charts → Scatter, then press the + (Chart Elements) button and tick Trendline (Linear). The dots and the line rise from left to right: taller people in this sample tend to be heavier.

To see the opposite, PK types a small test set in B20:C24 (5, 4, 3, 2, 1 against 10, 12, 15, 20, 25) and charts it. The trendline slopes down: as one value rises, the other falls. That is a negative correlation.

Step 3: Calculate it with CORREL

CORREL(array1, array2)
G3  =CORREL(B2:B16,C2:C16)

The result is 0.846, a strong positive correlation. For the small test set, =CORREL(B20:B24,C20:C24) returns -0.984, a very strong negative correlation.

Step 4: Or use PEARSON

G4  =PEARSON(B2:B16,C2:C16)

PEARSON takes the same two arrays and returns exactly the same 0.846. Both functions calculate Pearson’s r, so use whichever name you prefer.

How the formula works

  • CORREL compares how far each height is from the average height with how far the matching weight is from the average weight.
  • When above-average heights pair with above-average weights (and below with below), the products of those differences are mostly positive, so r is positive.
  • The result is divided by the spread (standard deviation) of each variable, which keeps r between -1 and +1 whatever the units: cm, kg, dollars or percentages.
  • The two arrays must be the same size. Their order does not matter.

Part 2 – Correlation Matrix with the Analysis ToolPak

When you have many variables, the Analysis ToolPak correlates every pair in one go.

Step 5: Turn on the Analysis ToolPak

  • Go to File → Options → Add-ins, choose Excel Add-ins in the Manage box and click Go (or use Developer → Excel Add-ins, as in the video).
  • Tick Analysis ToolPak and click OK.
  • A Data Analysis button appears in the Analysis group at the right end of the Data tab.

Step 6: Run the Correlation tool

The Sales sheet has 30 days of data with eight numeric columns: Sale Price, Discount, Unit Sold, Gross Sale, COGS, Net Sales, Profit and Profit%. Click Data Analysis, choose Correlation and click OK.

Excel Data Analysis dialog with the Correlation tool selected
Pick Correlation from the Data Analysis tools.
  • Input Range: $B$1:$I$31 – all eight columns including the header row (leave out the Date column).
  • Grouped By: Columns.
  • Tick Labels in First Row, so the headers become the matrix labels.
  • Output options: New Worksheet Ply (or an output range on the same sheet). Click OK.
Correlation dialog with input range B1:I31, grouped by columns and Labels in First Row ticked
The Correlation dialog: input range with headers, Labels in First Row and a new worksheet for the output.

Step 7: Read the correlation matrix

Correlation matrix in Excel created by the Analysis ToolPak for eight sales variables
The correlation matrix: each cell is r for the row variable and the column variable.

Each cell shows r for the variable in its row and the variable in its column. The diagonal is always 1, because every variable correlates perfectly with itself, and the upper half is left blank because it would mirror the lower half. Some readings from this data:

  • Sale Price and Unit Sold: -0.823 – strong negative. On days with a higher price, fewer units were sold.
  • Sale Price and Discount: -0.468 – a weak-to-moderate negative relationship.
  • Discount and Unit Sold: 0.755 – strong positive. Bigger discounts went with more units sold.
  • Unit Sold and COGS: exactly 1 – because COGS is calculated as units × 5 in this file. A perfect correlation often means one column is derived from the other, not that you found a discovery.

Keep the causation rule in mind: the data shows that discounts and units sold rise together, but on its own it cannot prove the discount caused the extra sales.

Part 3 – From Correlation to Prediction

A strong correlation is what makes a straight-line prediction worthwhile. The practice file predicts weight from height with the linear regression functions:

G1  =INTERCEPT(C2:C16,B2:B16)    returns -82.485
G2  =SLOPE(C2:C16,B2:B16)        returns 1.0333
G8  =G2*G6+G1

Type a height in G6 and G8 returns the predicted weight: 150 cm gives 72.5 kg, 176 cm gives 99.4 kg and 180 cm gives 103.5 kg. Note the argument order: weight (the value you predict) comes first, height second. You can get the same line from the chart: right-click the trendline, choose Format Trendline and tick Display Equation on chart to see y = 1.0333x – 82.485. The full method is covered in forecasting in Excel using linear regression.

With only 15 people, treat the result as an estimate. The line was fitted to heights of 110 to 174 cm, so the 176 cm and 180 cm predictions are already small extrapolations.

Beyond the Basics (addition, not in the video)

  • r and R-squared: for a simple linear trendline, R-squared is r squared. Here 0.846² = 0.716, so height accounts for about 72% of the variation in weight in this sample. =RSQ(C2:C16,B2:B16) returns the same value.
  • Curved relationships: r only captures straight-line patterns. A clear U-shape can give r close to 0, which is why the scatter chart comes first.
  • Outliers: one extreme point can push r up or down a lot in a small sample. Check the chart for points far from the rest.
  • Rank (Spearman) correlation: Excel has no Spearman function, but you can rank each column in helper columns with RANK.AVG and run CORREL on the two rank columns. Use it for ordinal data or when you have outliers.
  • Is it significant? With a small sample, check whether r could be chance. Calculate t = r × √(n-2) / √(1-r²) and the two-tailed p-value with =T.DIST.2T(t,n-2). For r = 0.846 and n = 15, t is about 5.73 and p is well below 0.001.

Common Errors and Fixes

#DIV/0! from CORREL or PEARSON

One of the arrays is empty or all its values are the same, so there is no variation to compare. In the practice file, cells F16 and G16 show #DIV/0! because the small test data in B20:C24 used in the video is no longer on the sheet; type it back in to see -0.984.

#N/A from CORREL or PEARSON

The two arrays are different sizes, for example B2:B16 and C2:C15. Select ranges with the same number of cells.

Data Analysis button is missing

The Analysis ToolPak is not loaded. Turn it on in File → Options → Add-ins as shown in Step 5.

The correlation matrix did not update

The ToolPak writes plain numbers, not formulas, so the table does not change when the data does. Run the tool again after editing the data, or use CORREL formulas if you need live results.

Tips

  • Chart first, calculate second. A scatter chart shows curves, clusters and outliers that a single number hides.
  • Report r with the sample size, for example r = 0.85, n = 15. A strong r from five points means much less than the same r from 500.
  • Microsoft’s CORREL function reference has the full definition and formula.

Want It Ready-Made?

To take the sales data one step further, the Profit Optimizer in Excel using Multiple Regression on NextGenTemplates.com builds a ready model on the same idea, or you can browse all Excel analysis templates. To master CORREL, SLOPE, INTERCEPT and the rest of Excel’s essential formulas step by step, join the video course Excel + AI Mastery: 100 Essential Formulas and Real-World Projects.

Related Tutorials

Frequently Asked Questions

How do I calculate the correlation coefficient in Excel?

Use CORREL with the two ranges you want to compare, for example =CORREL(B2:B16,C2:C16). The result is Pearson’s r, a number between -1 and +1.

What is the difference between CORREL and PEARSON?

There is no difference in the result. Both functions calculate Pearson’s correlation coefficient with the same two arguments and return the same value.

What is a good correlation coefficient?

The closer r is to +1 or -1, the stronger the linear relationship. As a rough guide, values beyond plus or minus 0.7 are often called strong, but the right threshold depends on your field and sample size.

Does a high correlation mean one variable causes the other?

No. Correlation shows that two variables move together, not why. A third factor, the way the data was collected or pure chance can create a strong correlation without any cause and effect.

How do I create a correlation matrix in Excel?

Load the Analysis ToolPak, click Data Analysis on the Data tab, choose Correlation, select all the columns with their headers, tick Labels in First Row and choose where to place the output.

About the Author

PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, Power Query and Power BI since 2016 on his YouTube channel.

Conclusion

The correlation coefficient in Excel takes one formula: CORREL or PEARSON returns Pearson’s r for any two columns, and the Analysis ToolPak turns a whole table into a correlation matrix in seconds. Check the scatter chart first, read both the sign and the size of r, and remember that a strong correlation is a reason to investigate, not proof of cause. Download the practice file and try it on your own data.

PK
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
https://www.pk-anexcelexpert.com