Calculating r in Excel: Formula and Examples

Formulas & functions · Updated November 18, 2026

To calculate Pearson’s correlation coefficient, or r, in Excel, use =CORREL(A2:A6,B2:B6) when the paired x values are in A2:A6 and their corresponding y values are in B2:B6. The result ranges from -1 to 1 and describes the direction and strength of a linear relationship between the two sets of numbers.

What r measures

Pearson’s r summarizes how two numeric variables move together in a straight-line pattern. A positive r means larger values of one variable tend to occur with larger values of the other; a negative r means larger values of one tend to accompany smaller values of the other. A value close to zero indicates little linear association in the data, though the variables could still have a curved or otherwise non-linear relationship.

The endpoints describe perfect linear relationships: r = 1 means all observations fall exactly on an upward-sloping straight line, while r = -1 means they fall exactly on a downward-sloping one. In real data, values are usually between those endpoints. There is no universal cutoff that makes a correlation “strong” or “weak.” The context, measurement quality, sample size, and practical importance all matter.

Correlation is not proof that one variable causes the other to change. A third factor might influence both, the direction of influence may be unclear, or the relationship may be coincidental. Treat r as a concise description of an observed pattern, not an explanation of why it exists.

Calculate r with CORREL

Put each observation’s paired measurements on the same row. For example, enter five x values in A2:A6 and the corresponding y values in B2:B6. In another cell, enter =CORREL(A2:A6,B2:B6). With x values 1, 2, 3, 4, 5 in column A and corresponding y values 2, 3, 5, 4, 6 in column B, Excel returns 0.9. This indicates a strong positive linear association in this small example.

To calculate that result, Excel compares how far each value is from its column’s mean. When the two variables tend to be above their means at the same time, or below their means at the same time, the result is positive. When one tends to be above its mean while the other is below, the result is negative. The calculation standardizes the relationship, so r has no units and can be compared across measurements made on different scales.

In Microsoft 365 for Windows, type the formula into a blank cell and press Enter. Excel may show several decimal places; to make the result easier to read, select the result cell and use Home > Number to choose a number format or adjust the displayed decimal places. Formatting changes how the result looks, not the underlying value. Excel for Mac uses the same CORREL formula; its ribbon layout may differ slightly by version. Excel for the web also supports the function.

Prepare and check the data

The formula is straightforward, but the result depends on matching the observations correctly. Each row should represent one paired case: for instance, one person’s height and weight, or one month’s advertising spend and sales. Do not sort one column independently of the other, because that breaks the pairings and can produce a meaningless result.

  • Keep the two variables in separate columns and place paired observations on the same rows.
  • Use numeric values for both measurements. Check for numbers stored as text, stray spaces, and inconsistent units.
  • Decide how to handle missing observations before calculating. A missing value should not be replaced with zero unless zero is genuinely the measured value.
  • Check unusual values against the source data. A real outlier can substantially affect r, but deleting it solely because it changes the answer is not a sound correction.

When using cell references, CORREL ignores empty cells and text in the referenced ranges, while numeric zero is treated as a real observation. A pair with a blank or text value may therefore be excluded rather than interpreted as a zero. For a trustworthy analysis, it is usually better to identify incomplete pairs and make a deliberate, documented decision about them than to rely on an accidental mix of blanks and text.

The two ranges must contain the same number of positions. If the ranges are different lengths, Excel returns #N/A. If either variable has no variation—for example, every value in one column is 5—the correlation is undefined and Excel returns #DIV/0!. A result of zero is not the same as an error: zero is a valid calculation indicating no measured linear association in the included data.

Interpret the result in context

The sign tells you the direction, and the distance from zero gives a sense of the strength of the linear pattern. For example, r = -0.82 indicates a strong negative linear association, while r = 0.12 indicates a weak positive linear association. These descriptions are general, not universal rules; in a scientific or business setting, the field’s conventions and the consequences of the relationship should guide interpretation.

Look at the sample size as well as the coefficient. An r calculated from five pairs can be strongly affected by one observation, whereas a larger, well-collected sample may give a more stable estimate. Even a large absolute value does not by itself tell you whether the relationship is statistically significant or practically important. Statistical inference requires additional information and methods beyond simply reading CORREL’s output.

A scatter plot is a useful companion to the calculation. Select the two numeric columns, then use Insert > Insert Scatter (X, Y) or Bubble Chart in Excel for Microsoft 365 on Windows. A plot can reveal a curved pattern, separate clusters, or a single influential point that one coefficient conceals. If the points follow a curve, r may be near zero even though the variables are clearly related in a non-linear way.

Use PEARSON or the Analysis ToolPak

Excel also provides PEARSON, which calculates the Pearson product-moment correlation coefficient. With paired x values in A2:A6 and paired y values in B2:B6, enter =PEARSON(A2:A6,B2:B6). For ordinary numeric data, it gives the same coefficient as CORREL. CORREL is a clear choice when the goal is simply to calculate correlation; PEARSON is useful if you prefer the statistical name of the measure.

If you need correlations among several variables, Excel’s Analysis ToolPak can produce a matrix rather than requiring you to enter a separate formula for every pair. On Windows, enable it through File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins, choose Go, select Analysis ToolPak, and choose OK. Then open Data > Data Analysis > Correlation, select the input range, specify whether the variables are grouped by columns or rows, and indicate whether the first row contains labels.

The output is a table of pairwise correlations. Each variable’s correlation with itself is 1, and matching pairs appear symmetrically on either side of the diagonal. The ToolPak is useful for a compact overview, but it does not replace checking the data or inspecting plots. On Mac, the ToolPak may need to be enabled using Tools > Excel Add-ins; the availability and exact controls can vary by Excel release. If Data Analysis is not shown on the Data tab, check that the add-in is installed and enabled.

Correlation is not R-squared

It is easy to confuse r with R-squared. In a simple linear setting, R-squared is the square of Pearson’s correlation, so it cannot show whether the association is positive or negative. For example, r = -0.9 corresponds to an R-squared of 0.81, just as r = 0.9 does. The squared value is sometimes used to describe the proportion of variation accounted for by a fitted linear model, but it should not be read as a causal explanation or as a complete measure of model quality.

Excel’s RSQ function returns the square of the Pearson correlation coefficient. If x values are in A2:A6 and y values are in B2:B6, =RSQ(A2:A6,B2:B6) gives the squared result, not signed r. Use CORREL or PEARSON when you need the direction as well as the strength. For regression questions—such as estimating a response from a predictor—correlation alone is also not enough; regression examines a fitted equation and additional model details.

Common mistakes and troubleshooting

  • Misaligned rows: Make sure the first value in one range belongs with the first value in the other. Equal-length ranges can still be wrong if their observations are out of order.
  • Different ranges: Include the same number of cells for both variables. A header in one range but not the other can cause a size mismatch or confuse the analysis.
  • Zero variation: If one column contains the same number in every included row, r cannot be calculated. Confirm that the range is correct and that the variable genuinely varies.
  • Unexpectedly low r: Plot the points and look for curvature, subgroups, or outliers. A weak linear coefficient does not rule out every kind of relationship.
  • Regional formula separators: The formulas shown use commas, the standard syntax in many Excel installations. Depending on regional settings, Excel may expect semicolons between arguments; use the separator recognized by your installation if the formula is rejected.

Also confirm that both columns measure the same concept consistently. A column mixing dollars and cents, or temperatures recorded in different units, can distort the pattern. Unlike a unit conversion applied consistently to every value, inconsistent measurement practices can change the relationships between pairs and therefore change r.

Frequently Asked Questions

What is the Excel formula for Pearson’s r?

Use CORREL with the two equal-length ranges of paired numbers. For example, if the x values are in A2:A20 and the matching y values are in B2:B20, enter =CORREL(A2:A20,B2:B20). PEARSON is an alternative function for the same coefficient.

Can r be greater than 1 or less than -1?

No. Pearson’s correlation coefficient is between -1 and 1, inclusive. If a displayed value seems outside that range, check whether you are looking at a different statistic, a copied value, or a formula error.

Does a correlation of zero mean there is no relationship?

It means the data show no linear association according to Pearson’s r, not that every possible relationship is absent. A strong curved pattern, for example, can produce a correlation near zero. Inspect a scatter plot to see the shape.

Why does CORREL return #DIV/0!?

This commonly occurs when there are not enough usable numeric pairs or when one of the variables has no variation. Check for constant values, blanks, text, and an incorrectly selected range.

Is correlation the same as causation?

No. Correlation describes how two variables vary together; it does not establish that one causes the other. A third variable, selection effects, or chance may help explain the observed association.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.