How to Calculate R2 Excel

Updated July 20, 2017

The R2 is a statistical calculation used with linear regressions when analysing a collected set of X-Y data points. The R2, also called the coefficient of determination, shows how good one term is at predicting another. The closer the R2 value is to 1.0, the better the statistical chance that the input X will correctly produce term Y. As the R2 value approaches 0.0, the correlation between the two numbers is lost. Many scientific assays use an R2 value to validate calibration curves.

Collect a data set with an input X and output Y.

Label cell A1 with "input" and cell B1 with "output."

Enter a complete data set for X inputs starting at cell A2, and for Y outputs starting at cell B2.

Use an Excel formula to calculate R2. In a blank cell enter "=RSQ(B2:Bx,A2:Ax)" where "x" is the row location of the data. For example, if the data set has five inputs and outputs, the final formula is "=RSQ(B2:B6,A2:A6)".


To create a graph of the data, highlight the data set and click on the "Insert" ribbon. Select "Scatter with straight lines and markers" to plot the data. In the Design tab, select "Output nine" to show a graphical representation of R2.

Things You'll Need

  • Microsoft Excel
Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Julie Revel, a former neurobiologist in pharmaceuticals, began writing professionally in 2009 with a focus on health and disease prevention. Based in New Jersey, she works as a medical writer in the healthcare industry. Revel graduated from Drew University with a B.A. in neuroscience and is currently pursuing her Doctor of Medical Humanities.