Excel’s RSQ function returns the square of the Pearson correlation coefficient for two sets of values.
In a simple linear regression with an intercept, this describes the proportion of variation in the dependent values explained by the fitted line.
The result measures fit, not the direction of the relationship or proof that one variable causes the other to change.
In this article, I’ll show you how to calculate R-squared, interpret it alongside negative correlation, and analyze a filtered subset.
RSQ Function Syntax in Excel
The RSQ function takes a set of known y-values followed by its matching set of known x-values.
=RSQ(known_y's,known_x's)
known_y'sis the required array or range containing the dependent values.known_x'sis the required array or range containing the independent values.
RSQ returns the square of the Pearson correlation coefficient. You can read the result as the proportion of variation in y associated with its linear relationship to x.
When to Use RSQ Function
- Measure how closely two variables follow a straight-line relationship.
- Compare the fit of the same outcome against different possible predictors.
- Store an R-squared result in a cell for use in reports or other formulas.
- Check the fit for a selected subset of rows with a dynamic array formula.
Example 1: Calculate R-Squared in Excel
Let’s start with a straightforward calculation.
Below is the dataset with machine runtime, lubricant used, and an empty result cell for the R-squared value.
I want to measure how closely lubricant use follows machine runtime.
Here is the formula:
=RSQ(B2:B8,A2:A8)
The formula returns 0.9977. In this dataset, 99.77% of the variation in lubricant use is associated with its linear relationship to machine runtime.
The y-values come first, so the lubricant range is the first argument. The machine-runtime range is the second argument.
Pro Tip: An R-squared value close to 1 indicates a close linear fit, but it does not prove that one variable causes the other.
Example 2: RSQ With Negative Correlation
Here’s a case where the direction of the relationship matters.
Below is the dataset with queue wait times, customer ratings, and three empty result cells for the comparison.
I want to compare the correlation coefficient with RSQ and the squared correlation.
First, here is the CORREL formula:
=CORREL(B2:B8,A2:A8)
The formula returns -0.9989. The negative sign tells us that customer ratings tend to fall as queue times increase.
Now here is the RSQ formula:
=RSQ(B2:B8,A2:A8)
RSQ returns 0.9979. Squaring the correlation removes its sign, so the R-squared value is positive even when the relationship slopes downward.
You can confirm that relationship by squaring the CORREL result in cell E2:
=E2^2
This formula also returns 0.9979. Use CORREL when you need the direction of the relationship, and RSQ when you need its squared linear fit.
Pro Tip: RSQ cannot tell you whether a relationship is positive or negative. Check CORREL or a scatter plot when the direction matters.
Example 3: RSQ With Text and Zero
This example shows how RSQ treats nonnumeric cells in referenced ranges.
Below is the dataset with maintenance visits, sensor reliability percentages, a pending row, and an empty result cell.
I want to calculate R-squared while keeping the valid zero-visit observation.
Here is the formula:
=RSQ(B2:B8,A2:A8)
The formula returns 0.9949. Excel ignores the paired Pending text values in row 5 but includes the zero in cell A2.
Zero is a numeric observation, so Excel keeps it in the calculation. An empty cell or text placeholder is treated differently.
Pro Tip: Keep the x-value and y-value for each observation on the same row. Mismatched or unequal ranges can give an error or pair the wrong values.
Example 4: RSQ for Filtered Data
Let’s calculate the fit for one service tier.
Below is the dataset with service tiers, training hours, resolved cases, a selected tier, and an empty R-squared result cell.
I want to calculate R-squared for the Priority rows selected in cell E2.
Here is the formula:
=RSQ(FILTER(C2:C11,A2:A11=E2),FILTER(B2:B11,A2:A11=E2))
Each FILTER function returns the rows whose service tier matches cell E2. RSQ then compares the filtered resolved-case values with the matching training-hour values.
The result is 0.9994 for Priority. Change E2 to Standard, and the same formula recalculates for that tier.
FILTER is available in Excel 2021 and later. In Excel 2019 or earlier, use helper columns to isolate each group before applying RSQ.
Pro Tip: If no rows match the value in E2, FILTER returns an error because this formula does not supply its optional if-empty argument.
Tips & Common Mistakes
- Put the known y-values first and the known x-values second. Reversing them does not change RSQ itself, but using the documented order keeps the formula easier to understand.
- Use ranges with the same number of data points. Empty or unequal data sets return
#N/A, while a single paired observation returns#DIV/0!. - Remember that text, logical values, and empty cells inside referenced ranges are ignored. Zero values are included.
- Use CORREL when you need to know whether the relationship is positive or negative.
- Use LINEST or the desktop Analysis ToolPak when you need coefficients or a regression with more than one predictor.
I covered a basic RSQ calculation, a negative correlation, a pending text row, and a selected group built with FILTER.
I hope you found this article helpful.
Other Excel articles you may also like:
