Correlation tells you how closely two sets of numbers move together. Do sales rise when ad spending rises? Do test scores fall as absences increase? In Google Sheets, you can measure this with a single formula, the CORREL function, which returns the correlation coefficient (often written as r). This guide shows how to use it, how to read the result, and how to visualize the relationship with a chart.
What the Correlation Coefficient Means
CORREL returns a number between -1 and 1:
- Close to 1: Strong positive correlation. When one value goes up, the other tends to go up.
- Close to -1: Strong negative correlation. When one goes up, the other tends to go down.
- Close to 0: Little or no linear relationship.
As a rough guide, many people treat values above 0.7 (or below -0.7) as strong and values between 0.3 and 0.7 as moderate, but what counts as “strong” depends on your field. Remember that correlation doesn’t prove that one thing causes the other.
How to Calculate Correlation in Google Sheets
Step 1: Organize your data
Put your two variables in side-by-side columns, with one pair of values per row. For example, put advertising spend in column A and sales in column B, starting in row 2 with headers in row 1. Both columns should cover the same rows.
Step 2: Choose a cell for the result
Click an empty cell where you want the correlation to appear.
Step 3: Enter the CORREL formula
Type:
=CORREL(A2:A21, B2:B21)Replace the ranges with your own. The first range is one variable and the second range is the other. Press Enter.
Step 4: Read the result
The cell shows a value like 0.86, which would indicate a strong positive relationship. Use the Format > Number options or the decrease/increase decimal buttons to control how many decimal places appear.
Related Functions
- PEARSON:
=PEARSON(A2:A21, B2:B21)returns the same Pearson correlation coefficient as CORREL. - RSQ:
=RSQ(B2:B21, A2:A21)returns r-squared, the share of variation in one variable explained by the other. - SLOPE and INTERCEPT: Describe the best-fit line if you want to predict one value from the other.
Visualize Correlation with a Scatter Chart
A chart makes the relationship easy to see:
- Select both columns of data, including headers.
- Go to Insert > Chart.
- In the Chart editor, set Chart type to Scatter chart.
- Open the Customize tab, expand Series, and check Trendline.
- Under Label, choose Use Equation, and check Show R² to display r-squared on the chart.
Points clustered tightly around an upward line show positive correlation; a downward line shows negative correlation; a scattered cloud shows little correlation. For more on charts, see how to make a graph on Google Sheets.
Build a Correlation Matrix
If you have several variables, you can compare every pair. List the variable names across a row and down a column, then fill each cell with a CORREL formula comparing the matching columns. The diagonal will always be 1, since each variable correlates perfectly with itself.
Spearman Rank Correlation
CORREL measures linear correlation. If your data has outliers or a curved but consistent relationship, a rank-based measure can be more appropriate. Google Sheets doesn’t have a dedicated Spearman function, but you can rank each column with RANK.AVG in helper columns, then run CORREL on the two rank columns.
Common Errors
- #DIV/0!: One of the ranges has no variation (all values are the same), or there isn’t enough data.
- Mismatched range error: The two ranges are different sizes. Make sure they cover the same number of rows.
- Unexpected results: Check for text, blank cells, or numbers stored as text in your ranges.
Tips
- Use enough data points. Correlation from just a handful of rows can be misleading.
- Look at the scatter chart, not just the number. Outliers can create or hide a correlation.
- Use absolute references (like
$A$2:$A$21) if you’ll copy the formula.
Frequently Asked Questions
Is CORREL the same as PEARSON?
Yes. Both return the Pearson correlation coefficient, so you can use either one.
Can I calculate correlation in Excel the same way?
Yes. Excel also has CORREL and PEARSON with the same syntax, so formulas carry over if you download the sheet as an Excel file.
Does the order of the ranges matter?
Not for CORREL. Swapping the ranges gives the same result. It does matter for SLOPE and INTERCEPT, where the first range is the value you’re predicting (the y values).
Summary
To find correlation in Google Sheets, put your two variables in side-by-side columns and use =CORREL(range1, range2). Values near 1 or -1 mean a strong relationship, while values near 0 mean little relationship. Add a scatter chart with a trendline and R² to see the pattern visually.
Related: how to average cells in Google Sheets.
Kermit Matthews is a freelance writer based in Philadelphia, Pennsylvania with more than a decade of experience writing technology guides. He has a Bachelor’s and Master’s degree in Computer Science and has spent much of his professional career in IT management.
He specializes in writing content about iPhones, Android devices, Microsoft Office, and many other popular applications and devices.