A to Z of Excel Functions: The RSQ Function
24 February 2025
Welcome back to our regular A to Z of Excel Functions blog. Today we look at the RSQ function.
The RSQ function

Imagine you are attempting to understand the relationship between two variables, which are assumed to exhibit a linear relationship:

The closer the data points are to the line of best fit on a scatter graph, the stronger the correlation. This fit may be measured numerically by a correlation coefficient. There are several coefficients that may be used, but the one we shall consider here is the Pearson's Product Moment Correlation Coefficient (bizarrely denoted r), which measures the strength of the linear correlation between two variables.
Pearson's product moment correlation coefficient
(sometimes known as PPMCC or PCC) is a measure of the linear relationship
between two variables that have been measured on interval or ratio scales. It can only be used to measure the
relationship between two variables which are both normally distributed. It is usually denoted by r, and it can
only take values between -1 and +1. Its
value may be interpreted as follows:


To calculate Pearson's Product Moment Correlation Coefficient, r:
- Plot the scatter diagram for your data; you have to do this first to detect any outliers. If you do not exclude these outliers in your calculation, the correlation coefficient will be misleading
- By being able to see the distribution of your data, you will get a good idea of the strength of correlation of your data before you calculate the correlation coefficient
- Next, check that your data meets all the
calculation criteria. The variables need
to be:
- measured on an interval / ratio scale (like weight in kilograms or distance in kilometres)
- Normally distributed: you can check this by looking at a boxplot of your data. If the boxplot is approximately symmetric, it is likely that the data will be Normally distributed
- linearly correlated: look at a significance test of the null and alternative hypotheses.
- Finally, calculate the correlation coefficient using the following formula:

where:

are the ith pair of array points and and are the means of the two arrays.
Alternatively, you can calculate this metric using the PEARSON function in Excel. It returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets, as explained above.
The r2 or r-squared function is simply the square of the Pearson product moment correlation coefficient, r, a dimensionless index. This ranges from zero [0] to 1.0 inclusive and reflects the extent of a linear relationship between two data sets, as explained above.
The RSQ function in Excel returns this square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's. This r-squared value may be interpreted as the proportion of the variance in y attributable to the variance in x.
The RSQ function has the following syntax:
=RSQ(known_y’s, known_x’s)
It contains two arguments:
- known_y’s which is required and represents a set of dependent values in an array or range of data points
- known_x’s which is also required and represents a set of independent values also in an array or range of data points.
It should be noted that:
- the RSQ function requires the two ranges to be the opposite way round from the PEARSON function
- the arguments must be either numbers or names, array constants or references that contain numbers
- logical values and text representations of numbers that you type directly into the list of arguments are counted
- if an array or reference argument contains text, logical values or empty cells, those values are ignored; however, cells with the value zero [0] are included
- arguments that are error values or text that cannot be translated into numbers cause errors
- if known_y's and known_x's are empty or have a different number of data points, RSQ returns the #N/A error value
- if known_y's and known_x's contain only one [1] data point, RSQ returns the #DIV/0! error value.
Please see my example below:

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.
A full page of the function articles can be found here.