How to Calculate Pearson’s r in Google Sheets with CORREL

How to Calculate Pearson's r in Google Sheets with CORREL

To calculate Pearson’s correlation coefficient, r, in Google Sheets, put one set of measurements in A2:A6 and the paired measurements in B2:B6, then enter =CORREL(A2:A6,B2:B6) in an empty cell. The result describes the direction and strength of a linear relationship between the two variables. Before starting, make sure each value in one column is paired with the correct value in the same row of the other column.

What You’ll Need

A Google account and a spreadsheet open in Google Sheets in a web browser.
Two sets of numeric observations that belong in pairs, such as a person’s study hours and test score. You need at least two pairs to get a result, although more observations usually give a more useful estimate.
A clear understanding of which variable is in each column, plus an empty cell where you can display the result.

You do not need to install an add-on or turn on a special analysis feature. CORREL is a built-in Google Sheets function. It calculates Pearson’s r, which measures linear association; it does not show that one variable causes the other, and it may not describe a curved or otherwise nonlinear relationship well.

Step-by-Step

Arrange your observations in two columns. Put the first variable in column A and the second variable in column B. Use one row for each matched pair, with the first observation in row 2 if row 1 contains headings. For example, enter “Study hours” in A1 and “Test score” in B1, then enter the five pairs shown below in rows 2 through 6: (1, 2), (2, 3), (3, 5), (4, 4), and (5, 6). The row pairing matters: the value in A2 must belong with the value in B2, and so on. Do not sort one column independently of the other.
Choose an output cell. Click an empty cell outside the data, such as D2, and optionally label the cell beside it “Pearson r.” Keeping the result separate from the observations makes it easier to read the table and avoids accidentally including the result in the calculation range.
Enter the CORREL function. With the first variable in A2:A6 and its paired second variable in B2:B6, type =CORREL(A2:A6,B2:B6) in D2 and press Enter. In Google Sheets, a function begins with an equals sign. CORREL takes the first data range and the second data range as its two arguments; the ranges must contain the same number of observations. If your observations are in different columns or rows, replace the two ranges with your actual cell ranges while keeping the correct pairing.
Check the returned value. For the example pairs (1, 2), (2, 3), (3, 5), (4, 4), and (5, 6), CORREL returns 0.9. This is a strong positive linear correlation in this small example: higher study-hour values tend to occur alongside higher test scores. The result is not a percentage, and 0.9 does not mean that study hours caused 90% of the scores.
Confirm the ranges and data if the result looks surprising. Verify that the top and bottom cells of each range cover the same observations and that both ranges include only the intended paired data. A simple check is to compare the two columns row by row with the original source. A plausible-looking number can still be wrong if the pairs were shifted or sorted separately.
Format the display if you want a shorter result. The underlying value can have more decimal places than Sheets shows. Select the result cell and use the toolbar’s increase or decrease decimal places controls, or choose Format > Number to adjust number formatting. Formatting changes how the number appears, not the calculated correlation. Keep enough decimal places for the purpose of your analysis rather than rounding so heavily that distinct results look identical.
Optionally make a scatter chart to inspect the pattern. Select the paired data, including the two numeric columns, then choose Insert > Chart. In the Chart editor, use the Setup tab and select a scatter chart if Sheets has not chosen one. The dots can help reveal outliers, clusters, or a curved pattern that a single correlation value will not explain. A chart is a useful check, but it does not replace the calculation.

Google Sheets also provides PEARSON for the same Pearson product-moment correlation calculation. With the first variable in A2:A6 and the paired second variable in B2:B6, =PEARSON(A2:A6,B2:B6) returns the same 0.9 for the example above. CORREL is a clear, commonly used choice for this task, so there is usually no need to enter both functions. In Excel, CORREL is also available and works in the same general way; this guide’s menu directions refer to Google Sheets in a browser.

Common Mistakes

Misaligned pairs: This is one of the most consequential errors. If column A contains people in one order and column B contains their measurements in another, the calculation no longer represents the intended pairs. When sorting, select the complete table so that every row stays together. If data has been copied from separate sources, check a few records against the source before trusting r.

Different range lengths: CORREL needs equally sized ranges. A range that begins at row 2 and ends at row 20 cannot be paired with one that begins at row 2 and ends at row 19. Include the same rows in both arguments, and avoid including headings in only one range. A mismatch can produce an error rather than a useful coefficient.

Mixing labels or text with measurements: Headings are helpful, but keep them outside the ranges used in the function. Text, blank cells, and numbers stored as text can lead to unexpected treatment or an error, depending on the data. Check cells that look numeric but were imported with apostrophes, spaces, or other characters. Convert or clean them so the data consists of comparable numeric observations.

Assuming any relationship is linear: Pearson’s r summarizes how closely values follow a straight-line trend. A strong U-shaped pattern, for instance, can have a correlation near zero despite a clear relationship. Look at a scatter chart before drawing a broad conclusion, especially when the data may have curves, separate groups, or unusual values.

Treating correlation as causation: A positive or negative r does not establish why two variables move together. A third factor could affect both, the apparent association could be coincidental, or the direction of influence could be unclear. Describe the result as an association unless the study design and evidence support a stronger causal claim.

Confusing r with r²: The CORREL result is r and falls between -1 and 1. Squaring r produces a different statistic, often called the coefficient of determination in a simple linear-regression context. Do not square the output unless you specifically need that separate measure and understand what it means for your analysis.

Tips

Use descriptive headings and keep the source data unchanged. If you want to try excluding a questionable observation, make a copy of the data or calculate the alternative result in another cell; do not quietly delete a point just because it changes the answer. Document why any observation was excluded, and follow the rules of your project or analysis.

Interpret the sign first: a positive value means the variables tend to increase together, while a negative value means one tends to be lower when the other is higher. Then consider the distance from zero as a measure of linear association strength. There is no universal cutoff that makes a coefficient “strong” in every subject. Context, measurement quality, sample size, and the pattern of points all matter.

Keep the number of valid pairs in mind. A coefficient based on only a few observations may change considerably when one more pair is added. If you share the result, report the number of pairs and enough context for readers to understand what was measured. For example, say that r = 0.90 was calculated from five paired observations, rather than presenting 0.90 without explanation.

For a quick reasonableness check, inspect a scatter chart and compare the displayed range endpoints with the first and last data rows. If the data has a header row, start both ranges below the headers. If the table will grow, you can expand both ranges consistently when adding observations, or use a deliberately chosen larger range and verify how Sheets handles empty cells in your dataset. Avoid selecting entire columns without a reason when a specific, auditable data range is clearer.

Troubleshooting

Sheets shows an error instead of a number: First check that both arguments refer to valid ranges with the same number of rows or columns. Make sure the selected cells contain usable numeric observations and that the formula has both ranges separated by a comma in the standard English-language formula syntax. Spreadsheet locale settings can affect separators; if Sheets flags the comma, check the locale under File > Settings and use the separator expected by that locale.

The result is blank or not what you expected: Click the cell and inspect the formula bar to confirm that the function points to the intended ranges. Check whether the ranges accidentally include blank rows, extra records, or only part of the table. Then verify that the values are actually numbers: a value imported as text may look like a number but not behave like one. Re-enter or clean problematic values, and recheck that the two columns still describe paired observations.

The result is exactly 1 or -1: That indicates a perfectly positive or negative linear relationship among the included pairs, but it can also be a clue that the ranges are too short or duplicate values have been selected. Confirm that you included all intended rows and did not accidentally use the same data for both arguments. If the observations truly lie on a straight line, a perfect coefficient can be correct.

The result is close to zero despite an obvious pattern: Plot the points and look for a curve, multiple clusters, or an outlier. Pearson’s r can be near zero when a strong relationship is nonlinear or when positive and negative trends in separate groups cancel out. Consider whether Pearson’s r answers the question you actually have; for other kinds of association, consult an appropriate statistical method rather than trying to force a linear interpretation.

The number changes after editing the sheet: If a referenced value changes, the formula recalculates automatically. Confirm that a new row was included in both ranges and that sorting or filtering did not lead you to use mismatched values. If you pasted new data over an old range, check for leftover records below it that may still be included.

Frequently Asked Questions

What does r mean in Google Sheets?
In this context, r is Pearson’s correlation coefficient. It summarizes the direction and strength of the linear association between two sets of paired numeric observations, with possible values from -1 to 1.

Is CORREL the same as PEARSON in Google Sheets?
Yes. Both functions calculate Pearson’s product-moment correlation for two data ranges. You can use CORREL for a straightforward, widely recognized formula.

Can r be greater than 1 or less than -1?
No. A valid Pearson correlation lies between -1 and 1, inclusive. If a displayed value seems outside that range, inspect whether you are looking at the correct cell, whether formatting is confusing the display, or whether the formula is actually a different calculation.

Does a negative r mean the data is wrong?
No. A negative coefficient indicates that higher values in one variable tend to occur with lower values in the other. Whether that direction is expected depends on what the variables measure and how the observations are paired.

Does a high r prove that one variable causes the other?
No. Correlation alone cannot establish causation. Other variables, selection effects, or coincidence may explain an association, so interpret r alongside the study design and subject-matter context.

Can I calculate r with only two pairs?
Sheets can calculate a value when there are at least two usable pairs, but two points provide very little evidence about a broader pattern. Use enough observations for the question, check for influential outliers, and be cautious about drawing conclusions from a very small dataset.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.