How to Calculate R-Squared in Excel

In this tutorial, you will learn how to calculate R-Squared in Excel.

R-squared (R2) is a statistical measure that shows how much of a dependent variable’s variance is explained by one or more independent variables in a regression model. R-squared measures how well the variation of one variable accounts for the variance of the second, as opposed to correlation, which describes the strength of the relationship between independent and dependent variables. Therefore, if a model’s R2 is 0.50, its inputs can account for around half of the observed variation.

Using Excel’s RSQ() tool, we can determine the r2 for these data.

Once ready, we’ll get started by utilizing real-world examples to show you how to calculate R-Squared in Excel.

Anatomy of RSQ Functions

RSQ Function

=RSQ(known_ys, known_xs)

When comparing two data arrays, the RSQ function returns the square of the Pearson Product-Moment Correlation Coefficient.

Calculate R-Squared

Before we begin we will need a group of data to be used to calculate R-Squared in Excel.

Step 1

First, you need to have a clean and tidy group of data to work with.

Step 2

To calculate the R-Square, we can simply insert this formula =RSQ(B2:B7,A2:A7). Then we will change the cell formatting to show as a percentage.

Step 3

Once you are done, your Excel will look like this. In this case, the amount of calories intake amounts for 95% of the range in weight.