How to Calculate R2 Excel

Written by julie revel
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Calculate R2 Excel
The R2 shows the linear relationship between two numbers. (BananaStock/BananaStock/Getty Images)

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.

Skill level:

Things you need

  • Microsoft Excel

Show MoreHide


  1. 1

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

  2. 2

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

  3. 3

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

  4. 4

    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)".

Tips and warnings

  • 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.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.