Get the R-Squared Practice Workbook
Want the file without building it from scratch? Download the ready-to-use workbook and follow along with every method in this tutorial.
- Practice all 4 ways to calculate R-squared in Excel
- Test RSQ, LINEST, chart trendlines, and the Data Analysis ToolPak
- Includes regression output and chart examples already set up
Enter your email below for instant access.
What is R-Squared?
R-Squared, also called the coefficient of determination, shows how well the data fit a regression model. It tells us what percentage of the variation in the dependent variable (what you’re trying to predict) is explained by the independent variable(s) (the predictors).
Why is R-Squared Important?
R-Squared values range from 0 to 1:
- 0 means the model explains none of the variability.
- 1 means the model explains all the variability.
A higher R Squared indicates a better fit, but it’s not always perfect. Sometimes, a high R Squared can mislead if the model is overfitting the data.
R-Squared is often used alongside growth metrics. For year-over-year trend analysis, see our YOY growth guide.
How to Interpret R-Squared
- High R-Squared: Your model fits the data well. For example, an R-Squared of 0.85 means 85% of the variance in your data is explained by the model.
- Low R-Squared: Your model doesn’t fit well. However, don’t rely solely on R Squared. Context and other diagnostic measures are crucial.
R-Squared Formula
Here’s the formula for R Squared:
- SS_{regression}: Sum of squares due to regression (explained variance).
- SS_{total}: Total sum of squares (total variance).
How to Calculate R-Squared in Excel
Let’s use an example to understand how to find R Squared in Excel.
Suppose we have data on car prices based on their mileage and age. Here’s the sample dataset:
In this example, we want to calculate the R-Squared value to see how well the mileage explains the car price variation.
Understanding X and Y Variables
In regression analysis, the variables are typically categorized as:
- Dependent Variable (Y): This is the variable you are trying to predict or explain. For example, in our car price example, the car price is the dependent variable.
- Independent Variable (X): This is the variable you use to predict the dependent variable. In our example, car mileage is the independent variable.
Using the RSQ Function
The RSQ function in Excel is straightforward and easy to use. It provides the proportion of variance in the dependent variable that can be explained by the independent variable.
Syntax:
=RSQ(known_y's, known_x's)- known_y’s: The Y values (dependent variable, Car Price) are in cells C2:C11
- known_x’s: The X values (independent variable, Car Mileage) are in cells A2:A11
Therefore the formula is:
=RSQ(C2:C11, A2:A11)
For the given example, the RSQ function returns a R-Squared value of 0.8357. It means that about 84% of the variability in car prices is explained by car mileage, suggesting that mileage is a good predictor of price.
Using the LINEST Function
The LINEST function in Excel not only gives the R-Squared value but also other regression statistics like coefficients and standard errors. This makes it useful for a more in-depth analysis.
Syntax:
=LINEST(known_y's, known_x's, const, stats)- known_y’s: The Y values (dependent variable, Car Price) are in cells C2:C11
- known_x’s: The X values (independent variable, Car Mileage) are in cells A2:A11
- const: A logical value (TRUE or FALSE). TRUE (or omitted) means the intercept should be calculated, and FALSE means the intercept is set to zero.
- stats: A logical value (TRUE or FALSE). TRUE means that additional regression statistics should be returned, and FALSE means only the slope is returned.
Therefore the Excel formula is:
=LINEST(C2:C11, A2:A11, TRUE, TRUE)The LINEST function will provide a range of outputs, including the R-Squared value of 0.8357.
Additional Results Provided by LINEST
In addition to R squared, the LINEST function also provides:
- Slope: The rate of change in the dependent variable per unit change in the independent variable.
- Intercept: The value of the dependent variable when the independent variable is zero.
- Standard Errors: Indicating the precision of the slope and intercept estimates.
- F-Statistic: Used to determine if the relationship between the dependent and independent variables is statistically significant.
- Degrees of Freedom: The number of independent pieces of information in the data.
- Regression Sum of Squares: The explained variation in the data.
- Residual Sum of Squares: The unexplained variation in the data.
💡 LINEST is an array formula. For Office 365 and Excel 2021, simply pressing Enter will display the results. For older versions of Excel, you need to press Ctrl + Shift + Enter to get the results.
Featured Course
Business Charts in Excel
Using a Chart Trendline
An easy way to calculate R Squared in Excel is by adding a linear trendline to a chart.
Insert a Scatter Plot:
- Highlight the data in column A (Car Mileage), hold down Ctrl, and highlight the data in column C (Car Price).
- Go to the Insert tab and select Scatter (X, Y) Chart
Add a Trendline:
- Click on any data point in the scatter plot to select the data series.
- Right-click and choose Add Trendline.
- In the Format Trendline panel, check the box for Display R-squared value on chart.
- The R-Squared value will be displayed on the chart, showing how well the data points fit the regression line.
For more on creating and customizing charts in Excel, see our guide on how to make a line chart.
Using Data Analysis ToolPak
The Data Analysis ToolPak is an Excel add-in that provides data analysis tools for statistical and engineering analysis. When enabled, it offers a range of tools to perform complex data analysis tasks such as regression, ANOVA, descriptive statistics, and more.
Enable Data Analysis ToolPak:
- Go to File > Options > Add-Ins.
- In the drop down next to “Manage:” select Excel Add-ins and click on Go.
- In the Add-Ins window, check Analysis ToolPak and click OK.
Perform Regression Analysis:
- Go to the Data tab and click Data Analysis.
- Select Regression from the list and click OK.
- Set the Input Y Range (dependent variable, Car Price) to your data (C1:C11).
- Set the Input X Range (independent variable, Car Mileage) to your data (A1:A11).
- Check Labels to include headers.
- Choose an Output Range to display the results and click on OK.
- The regression line calculator in Excel will display the regression analysis output which includes the R squared value.
When to Use Adjusted R-Squared Instead
R-Squared always increases when you add more independent variables, even if they don’t improve the model. Adjusted R-Squared corrects for this by penalizing unnecessary variables.
When to use Adjusted R-Squared: When your model has more than one independent variable (multiple regression).
If you’re comparing models with different numbers of predictors, Adjusted R-Squared is the better metric.
How to get it in Excel: The Data Analysis ToolPak regression output includes Adjusted R-Squared automatically (labeled “Adjusted R Square” in the output).
You can also calculate it manually:
=1 - (1-R2) * (n-1) / (n-k-1) Where R2 is R-Squared, n is the number of data points, and k is the number of independent variables.
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the coefficient of determination (R Squared) calculation in Excel with hands-on examples.
Download the workbook here and start applying what you’ve learned directly in Excel.
For a deeper dive into financial modeling and statistical analysis in Excel, check out our Fundamentals of Financial Analysis course.
Featured Course
Fundamentals of Financial Analysis
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
