In regression analysis, the R-squared value (R2) is the proportion of the variance in the response variable that can be explained by the predictor variable.
Often you may want to display this R-squared value on a chart in Excel, similar to the chart below:
The following step-by-step example shows how to do so.
Related: What is Considered a Good R-squared Value?
Step 1: Enter the Data
First, let’s enter the following values for a predictor variable (x) and a response variable (y) in Excel:
Step 2: Create a Scatter Plot
Next, highlight the cell range A2:B15.
Then click the Insert tab along the top ribbon, then click the Insert Scatter icon in the Charts group and choose the first option to insert a scatter plot:
The following scatter plot will appear:
Step 3: Add R-Squared Value to Scatter Plot
Next, click anywhere on the scatterplot. Then click the plus (+) sign in the top right corner of the plot, then click the dropdown arrow next to Trendline, then click More Options:
In the Format Trendline panel that appears, check the boxes at the very bottom next to Display Equation on chart and Display R-squared value on chart:
The regression equation and the R-squared value will both now be shown in the scatter plot:
We can see that the R-squared value for this particular regression equation is 0.9302, which tells us that 93.02% of the variation in the response variable can be explained by the predictor variable.
Step 4: Customize the Chart (Optional)
Lastly, if you’d like to make the linear regression equation and the R-squared value easier to read, then you can make the font bold and remove the gridlines from the plot.
The final plot will look like this:
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Calculate R-Squared in Excel
How to Calculate Adjusted R-Squared in Excel
How to Perform Simple Linear Regression in Excel
How to Perform Multiple Linear Regression in Excel
