To run a correlation analysis in Excel, place two comparable numeric variables in separate columns and use =CORREL(A2:A6,B2:B6), changing the ranges to match your data. Excel returns a coefficient from -1 to 1 that describes the direction and strength of a linear relationship; it does not show that one variable causes the other.
What correlation analysis measures
Correlation analysis summarizes how two variables move together. A positive coefficient means higher values of one variable tend to accompany higher values of the other. A negative coefficient means higher values of one tend to accompany lower values of the other. A value near zero means little or no linear association in the observations being analyzed.
The most familiar measure is the Pearson correlation coefficient, usually written as r. Its possible values run from -1 through 0 to +1. A result of +1 indicates a perfectly consistent upward straight-line relationship, while -1 indicates a perfectly consistent downward straight-line relationship. A result of 0 indicates no linear relationship, but the variables could still have a curved or otherwise non-linear association.
For example, a positive correlation between hours of practice and a performance score would mean that people with more practice hours tend to have higher scores in the data. It would not establish that practice alone caused the higher scores: experience, instruction, or other factors could also be involved.
Prepare the data before calculating
Each row should represent one observation, and the two columns should contain paired measurements for that same observation. If you are comparing monthly advertising spending with monthly sales, row 2 might contain January spending and January sales, row 3 February values, and so on. Sorting one column independently would break the pairing and make the result meaningless.
- Use numeric values measured on a comparable basis, such as one row per person, store, day, or product.
- Keep headers in the first row and select only the data rows in the calculation.
- Check that both variables use the same observations and the same order.
- Review missing entries, text accidentally stored as numbers, duplicate rows, and unusually large or small values.
A zero is a numeric observation, not a missing value. If a blank means that a measurement was not collected, do not replace it with zero unless zero genuinely represents the observed value. Excel’s correlation functions ignore empty cells and text in referenced ranges, but the two columns still need to correspond correctly row by row. For a careful analysis, identify incomplete pairs explicitly and decide how to handle them rather than letting the data-cleaning choice go unnoticed.
Calculate correlation with CORREL
For a single pair of variables, CORREL is usually the quickest option. In Microsoft Excel for Microsoft 365 on Windows, click an empty cell, enter the formula using the two data ranges, and press Enter. The result updates when the referenced values change.
With variable X in A2:A6 and variable Y in B2:B6, enter =CORREL(A2:A6,B2:B6). For the illustrative values X = 1, 2, 3, 4, 5 and Y = 2, 3, 5, 4, 6, the result is 0.9. The calculation uses the paired differences from each column’s mean; in this example, the sum of the paired products is 9 and each variable’s sum of squared differences is 10, giving 9 divided by the square root of 10 times 10, or 0.9.
The function name PEARSON is also available for calculating Pearson correlation. In ordinary cases, CORREL and PEARSON return the same coefficient. When using either function, confirm that both ranges cover the same number of rows and that each row refers to the same observation. If Excel returns an error, check for incompatible range dimensions or a range without enough usable paired numeric data.
Build a correlation matrix with the Analysis ToolPak
When you want correlations among several variables at once, the Analysis ToolPak can create a correlation matrix. A matrix is a table showing the coefficient for every variable pair. It is useful for an initial overview of relationships across, for example, sales, price, advertising, and customer visits.
- Arrange the numeric variables in adjacent columns, with a descriptive label at the top of each column.
- On Windows, choose File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins, select Go, check Analysis ToolPak, and choose OK if it is not already enabled.
- Choose Data > Data Analysis, select Correlation, and choose OK.
- Set the Input Range to include the data columns and, if included, their headers. Choose Grouped By Columns and check Labels in First Row when the first row contains names.
- Choose an Output Range or a new worksheet, then select OK.
The output has 1s along its diagonal because each variable is perfectly correlated with itself. The values on either side of the diagonal are mirrored: the correlation of X with Y is the same as the correlation of Y with X. On Mac, the Analysis ToolPak is enabled through Tools > Excel Add-ins in supported desktop versions; available menus can differ by release. Excel for the web may not offer the ToolPak workflow, but formulas such as CORREL are a practical alternative.
Interpret the coefficient with context
The sign tells you the direction of the linear association, while the absolute size indicates how closely observations follow a straight-line pattern. As a broad reading guide, coefficients near zero suggest a weak linear association, and coefficients nearer to -1 or +1 suggest a stronger one. There is no universal cutoff that makes a correlation meaningful or unimportant. The appropriate interpretation depends on the subject, data quality, sample size, and consequences of being wrong.
A correlation of 0.9 is strong positive linear association in the example, but it does not mean that one variable rises by 90 percent when the other rises. It is not a percentage, and it does not give the slope or units of change. Likewise, a coefficient of -0.8 is not evidence of a negative causal effect. Correlation summarizes co-movement; it does not identify what would happen if someone deliberately changed one variable.
Always pair the coefficient with a scatter chart. Select the two columns and use Insert > Insert Scatter (X, Y) or Bubble Chart to see whether the points form a roughly straight pattern, contain clusters, or include influential outliers. A single coefficient can conceal these features. A curved pattern, for instance, can have a low Pearson coefficient even when the variables are clearly related.
Correlation compared with covariance and regression
Covariance also describes whether two variables tend to move together, but its value depends on the variables’ units and scales. A covariance measured using dollars and visits cannot be compared as directly with one measured using dollars and thousands of visits. Correlation standardizes the relationship, so its unit-free coefficient always lies between -1 and +1.
Regression answers a different question. A simple linear regression estimates a line that describes how an outcome changes with a predictor, typically providing a slope, intercept, and other model statistics. Correlation is symmetric: swapping X and Y leaves the coefficient unchanged. Regression treats one variable as the outcome and another as the predictor, so swapping them generally changes the fitted model. A high correlation may be a reason to investigate a relationship, but it is not by itself a complete predictive model.
Common problems and how to check them
- Misaligned records: If a value in one column belongs to a different person or date than the value on the same row in the other column, the calculated coefficient does not represent the intended pairs. Restore a shared identifier or date before calculating.
- Outliers: One unusual observation can substantially change Pearson correlation, especially in a small dataset. Check the source and the scatter chart; correct errors, but do not remove a valid observation merely because it weakens the result.
- Curved relationships: Pearson correlation captures linear association. If the scatter chart bends, consider whether a different analysis is needed rather than treating a small coefficient as proof of no relationship.
- Restricted range: When all sampled values are similar, a genuine relationship can appear weaker than it would across a wider range. Explain who or what was included in the sample.
- Too many comparisons: A large matrix can show some apparently strong values by chance. Treat exploratory results as leads to investigate, not automatic confirmation of a hypothesis.
- Confusing association with cause: A third factor may influence both variables, or the direction of influence may be unclear. A correlation alone cannot resolve those possibilities.
Do not interpret a blank or zero result without checking the inputs. A zero coefficient can be a valid outcome, but it may also reflect a non-linear pattern or a data-preparation mistake. A displayed result rounded to 0.00 may not be exactly zero; increase the displayed decimal places to inspect it.
Other approaches for different questions
If the relationship is monotonic but not well described by a straight line, a rank-based measure such as Spearman’s rank correlation may be more appropriate. It compares the order of observations rather than their raw distances. Excel does not provide a worksheet function named SPEARMAN; users can rank paired values with ranking functions and calculate Pearson correlation on those ranks, taking care with ties and missing pairs. For more complex analysis, a statistical package may be better suited.
If the purpose is to explore a possible predictor and outcome, use a scatter chart and consider a regression analysis rather than relying on correlation alone. For a quick Excel regression, the Analysis ToolPak includes a Regression option in Data > Data Analysis when the add-in is available. The choice depends on the question: use correlation to summarize linear association, and use a method designed for prediction or causal questions when those are the aims.
Frequently Asked Questions
How do I calculate correlation between two columns in Excel?
Put paired numeric observations in two columns, then use CORREL with the data ranges. With values in A2:A20 and B2:B20, enter =CORREL(A2:A20,B2:B20) in an empty cell. Confirm that each row in both ranges refers to the same observation.
What does a correlation of 0.7 mean in Excel?
It indicates a positive linear association: larger values of one variable tend to occur with larger values of the other. Whether 0.7 is strong or useful depends on the context and data. It does not mean a 70 percent increase, and it does not prove causation.
Can Excel calculate correlation for more than two variables?
Yes. Arrange variables in separate columns and use the Analysis ToolPak’s Correlation command to create a matrix. You can also calculate selected pairs individually with CORREL. Review the labels and input range to ensure the output corresponds to the intended variables.
Why does CORREL return an error or an unexpected value?
Check that both ranges have matching dimensions, contain enough usable paired numeric observations, and are aligned by row. Look for text, empty cells, imported numbers stored as text, and zero values that may have been used to stand in for missing data. A scatter chart can also reveal outliers or a curved relationship.
Does correlation prove that one variable causes another?
No. Correlation describes association in the observed data. It cannot rule out confounding variables, reverse direction, selection effects, or coincidence. Establishing causation requires an appropriate study design and further evidence beyond an Excel coefficient.
Related
- Row Grouping in Excel: Collapse and Expand Rows
- Scatter Plot Equation in Excel: Find, Read, and Use It
- Resolving Circular References in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Laundry List Template in Excel: Build and Use OneYes. A laundry list template in Excel can track clothes or household laundry tasks by date, load, person,…
- Excel Workbook Structure: Sheets, Cells, and FilesAn Excel workbook is the complete spreadsheet file: it contains one or more sheets, and each worksheet organizes…
- Out of Office in Outlook for Mac: Set Automatic RepliesTo set an out-of-office reply in Outlook for Mac, open the automatic-replies settings for your email account, switch…
- Excel Circular Reference Warning: Causes and FixesAn Excel circular reference warning means a formula refers to its own cell, either directly or through a…
- To-Do List Excel Spreadsheet: Setup, Columns and TipsA to-do list Excel spreadsheet is a worksheet for recording tasks, due dates, priorities, and progress in one…
- PowerPoint Present with Notes Visible? Presenter ViewYes. In PowerPoint for Microsoft 365, turn on Presenter View to see your speaker notes on your own…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.