How to Calculate R2 Excel

When you perform regression analysis, the coefficient of determination, noted R^2, tells how well the regression line predicts the actual data points. Excel 2013 provides a way to compute the R^2 value as well as display it on an XY chart.

...
R2
credit: Image courtesy of Microsoft

Compute R2

Step

Collect 12 months worth of marketing expense and sales data for your company and enter it into an Excel worksheet. You want to understand if your marketing efforts, represented by marketing expense, impact sales. In a formal regression analysis, you refer to the marketing expense as the independent variable, noted X, and the sales data as the dependent variable, noted Y.

...
Data for Analysis
credit: Image courtesy of Microsoft
...
R2 Formula
credit: Image courtesy of Microsoft

Step

Use Excel's RSQ function to compute R^2. The RSQ function has two arguments, the first represents the Y values and the second represents the X values. Assuming you store the sales values in range C9:C20 and the marketing expense values in range B9:B20, write the following formula in C5: =RSQ(C9:C20, B9:B20)

...
R2 Formula Result
credit: Image courtesy of Microsoft

Step

Press the Enter key on your keyboard and confirm the formula returns the R^2 value.

Charting

Step

To chart the data, begin by selecting the marketing expense and sales data. Select the Insert > Scatter (X,Y) chart icon and pick the Scatter style.

...
Insert > Scatter XY Chart
credit: Image courtesy of Microsoft
...
More Trendline Options
credit: Image courtesy of Microsoft

Step

Excel inserts the chart into the worksheet. To display the R^2 value on the chart, select the Chart Tools > Design > Add Chart Element > Trendline > More Trendline Options icon.

...
Display R-squared
credit: Image courtesy of Microsoft

Step

In the resulting Format Trendline panel, check the Display R-squared value on chart check box. Excel displays the R^2 value, and you can confirm it is the same value computed by the RSQ function.