How to Calculate R-Squared in Excel to Measure How Well Your Data Fits

What R-Squared Tells You and How to Find It in Excel

R-squared is a number between 0 and 1 that shows how well a trend line matches your actual data. If you plot sales over time and draw a line through the points, R-squared tells you whether that line is a good fit (close to 1) or a poor fit (close to 0). Excel calculates it for you in two ways: by adding a trend line to a chart, or by using the RSQ function in a cell.

The quickest method is to create a scatter plot of your data, add a trend line, and check the box that says "Display R-squared value on chart." The number appears right on the chart. If you need the value in a cell instead—to use it in other calculations or to keep it separate from the chart—use the RSQ function with your actual values and predicted values as inputs.

Key Takeaways

R-squared ranges from 0 to 1, where 1 means the trend line explains all variation in your data and 0 means it explains none.
The fastest way to see R-squared is to create a scatter chart, add a trend line, and enable the "Display R-squared value on chart" option.
The RSQ function calculates R-squared directly in a cell if you have both your actual data and the predicted values from your trend line.
A "good" R-squared value depends on your field—0.7 or higher is often acceptable, but some fields expect 0.9 or higher.

Adding a Trend Line to Your Chart and Displaying R-Squared

Start by selecting your data—the X values in one column and the Y values in another. Include headers if you have them. Go to the Insert tab and click Chart. Choose XY (Scatter) from the chart type list, then click OK. Excel creates a basic scatter plot of your points.

Right-click on any data point in the chart. A menu appears. Click "Add Trendline." A panel opens on the right side. Under "Trendline Options," choose the type that matches your data—Linear is the most common, but you can also choose Exponential, Power, or Polynomial if your data curves. At the bottom of the panel, check the box next to "Display Equation on chart" and "Display R-squared value on chart." Click Close. The R-squared value now appears on your chart, usually in the upper right corner.

Using the RSQ Function to Calculate R-Squared in a Cell

If you want the R-squared value in a cell rather than on a chart, use the RSQ function. This method requires two sets of numbers: your actual Y values and the predicted Y values from your trend line. If you do not yet have predicted values, you can calculate them using the FORECAST or TREND function first.

In an empty cell, type =RSQ(actual_range, predicted_range). Replace actual_range with the cells containing your real data points and predicted_range with the cells containing the values your trend line predicts. For example, if your actual values are in B2:B10 and your predicted values are in C2:C10, type =RSQ(B2:B10,C2:C10) and press Enter. Excel returns the R-squared value as a decimal.

Generating Predicted Values Using FORECAST or TREND

Before you can use RSQ, you need predicted values. The FORECAST function calculates a single predicted Y value based on an X value and your existing data. The TREND function does the same thing but for multiple values at once, which is faster if you have many data points.

To use TREND, select a range of empty cells equal in size to your actual data. Type =TREND(known_y_values, known_x_values) and press Ctrl+Shift+Enter (not just Enter). Excel fills all the selected cells with predicted values. For example, if your actual Y values are in B2:B10 and your X values are in A2:A10, select C2:C10, type =TREND(B2:B10,A2:A10), and press Ctrl+Shift+Enter. Now you have predicted values in column C that you can use with RSQ.

Understanding What Your R-Squared Number Means

An R-squared of 0.85 means your trend line explains 85 percent of the variation in your data. The remaining 15 percent is due to other factors or random noise. An R-squared of 0.5 means the line explains only half the variation—it is a weak fit. An R-squared of 0.95 or higher means the line is an excellent fit and your data follows the trend very closely.

What counts as "good" depends on your field. In physics or chemistry, you might expect 0.95 or higher. In business or social science, 0.7 or 0.8 is often acceptable. If your R-squared is very low (below 0.5), your data may not follow a straight line at all, or you may be missing important variables that explain the variation.

Checking Your Work: Common Mistakes to Avoid

The most common error is including headers in your data range when using RSQ. If your first row contains text like "Sales" or "Month," Excel cannot do math with it and returns an error. Always select only the cells with numbers, not the header row.

Another mistake is using RSQ with only actual values and no predicted values. RSQ requires two separate ranges. If you try to use it with just one range, it returns an error. Make sure you have calculated or obtained predicted values first using TREND, FORECAST, or a trend line from a chart.

If your R-squared value seems wrong, double-check that your predicted values actually come from a trend line based on your actual data. If you manually typed predicted numbers or used unrelated data, the R-squared will not be meaningful.

When to Use Each Method: Chart Trend Line vs. RSQ Function

Use the chart trend line method when you want to see your data visually and quickly spot whether the fit is good. The R-squared value appears right on the chart, and you can experiment with different trend line types (linear, exponential, polynomial) to see which one fits best. This method is best for presentations or reports where the chart itself is the main point.

Use the RSQ function when you need the R-squared value in a spreadsheet for further calculations, comparisons, or record-keeping. This method is also better if you are testing multiple data sets and want to compare their R-squared values side by side in a table. You can also use RSQ if you already have predicted values from another source and just need to measure the fit.

Frequently Asked Questions

Can I calculate R-squared for a curved line instead of a straight line?

Yes. When you add a trendline to your chart, choose Polynomial (for a curve) or Exponential instead of Linear. Excel calculates R-squared for the curve you select. You can also use RSQ with predicted values from a polynomial or exponential formula, though you will need to calculate those predicted values yourself using a formula.

What does an R-squared of exactly 1.0 mean?

An R-squared of 1.0 means your trend line passes through every single data point with no error. This rarely happens in real-world data. If you see it, check whether you have only a few data points or whether your predicted values are identical to your actual values.

Why is my R-squared negative?

A negative R-squared can occur when you use RSQ with predicted values that come from a line that does not fit your data at all—worse than a horizontal line through the average. This usually means your predicted values are wrong or come from a different data set. Recalculate your predicted values using TREND or FORECAST based on your actual data.

Can I compare R-squared values from different data sets?

Yes, but only if the data sets are measured in the same units and represent similar types of relationships. R-squared from one data set is not directly comparable to R-squared from a completely different context. It is most useful for comparing different trend lines (linear vs. polynomial) on the same data set.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.