Comparison Plot in Excel With R²: Steps and Meaning

How-to basics · Updated November 19, 2026

To make a comparison plot with R² in Excel, put the shared X values and each dataset’s Y values in separate columns, select the table, and choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter. Add a separate linear trendline to each series and select Display R-squared value on chart. The resulting plot compares the relationships, while each R² describes how well that series’ trendline fits its points.

What a comparison plot shows

A comparison scatter plot is useful when two or more sets of measurements share the same explanatory variable. For example, you might compare the outputs of two forecasting methods against time, or compare measurements from two instruments across a range of known concentrations. Each dataset appears as its own series, using the same horizontal axis so the patterns can be inspected side by side.

R², also called the coefficient of determination, is a statistic associated with a fitted model. In Excel, a linear trendline’s displayed R² indicates how closely the points in that series follow the fitted straight line. It is not a general score for how similar two series are, and it does not by itself establish that one method is accurate or better.

That distinction matters. If the purpose is to compare predicted values with actual values, a scatter plot is still useful, but R² alone can conceal a consistent offset or a scale error. A high R² can occur even when predictions are systematically too high. The chart design and the question being asked should determine how you interpret the statistic.

Arrange the worksheet data

For a straightforward comparison, use one column for the common X values and one column for each dataset. Put descriptive series names in the first row, and keep each observation on the same row across the columns. For example, a small table might have headings X, Method A, and Method B, followed by five paired measurements.

Before charting, check that the X values are numeric and that each Y value belongs to the X value on its row. Avoid blank rows inside the data and avoid mixing text such as n/a into a numeric series. If a measurement is missing, decide whether that observation should be omitted or represented as a gap; do not silently pair a value with the wrong X value.

  • Use a shared X column when both series were measured at the same X values.
  • If the series use different X values, keep a separate X column for each series and add the series individually through the chart’s data settings.
  • Use meaningful units in the column headings or axis titles, such as Time (days) and Concentration (mg/L).

Create the scatter plot in Excel

In Excel for Microsoft 365 on Windows, select the table including its headings, then choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter. Choose the basic scatter chart with markers if you want to compare individual observations. A scatter chart is appropriate when the horizontal values are numeric; a line chart treats the horizontal axis as categories and may space numeric values evenly rather than according to their actual distances.

Excel will usually use the first column as X values and the following columns as separate Y series when the table is arranged as described. Check the result rather than assuming the assignments are right: the legend should identify each series, and the horizontal axis should show the intended X range. If Excel has interpreted the data incorrectly, select the chart and choose Chart Design > Select Data. In the Select Data Source dialog, choose a series to edit, or use Add to specify its series name, X values, and Y values.

To add a trendline, click one series’ markers, right-click, and choose Add Trendline. In the Format Trendline pane, choose Linear if a straight-line relationship is appropriate, then select Display R-squared value on chart. Repeat for each series. You can also select the chart and use Chart Design > Add Chart Element > Trendline for the available trendline options. Make sure the trendline is attached to the intended series; each series needs its own fit and R² label.

Add context with Chart Design > Add Chart Element > Axis Titles and Chart Title. Use a title that identifies what is being compared, and include units on both axes. If the two series are close together, use distinct marker shapes as well as colors. This improves readability for people viewing the chart in grayscale or with color-vision differences.

What the displayed R² means

R² ranges from 0 to 1 for the usual linear trendline calculation in this setting. A value closer to 1 means the points lie more closely around the fitted line, while a value closer to 0 means the line accounts for less of the variation in the Y values. For a simple linear fit with an intercept, R² is the square of the correlation between X and Y.

Consider two series that both rise as X increases. One might have R² = 0.95 and the other R² = 0.70. The first has a tighter linear pattern in this sample, but that does not automatically mean its measurements are more accurate, that the relationship is causal, or that its predictions will work well outside the observed range. R² is affected by the spread and noise in the data, and a few unusual observations can influence a fit.

Also, a high R² does not prove that a linear model is suitable. Curved patterns can be poorly represented by a straight line even when an R² number appears respectable. Inspect the plotted points for curvature, clusters, outliers, and changing spread. If the data have a meaningful nonlinear relationship, a linear trendline and its R² may answer the wrong question.

Compare actual and predicted values carefully

A common related task is checking predictions against observed results. Put actual values on the horizontal axis and predicted values on the vertical axis, then add a reference line where predicted equals actual. Points on that line are exact matches; points above it are overpredictions, and points below it are underpredictions. The equality line is not the same as a fitted trendline: it has a fixed slope of 1 and intercept of 0.

To draw the reference line, add a small helper series with two points: the minimum and maximum values used on the axes, with each Y value equal to its corresponding X value. Format that series as a line without markers. Set the horizontal and vertical axis limits to the same minimum and maximum if a 45-degree visual comparison is important; otherwise Excel may display the equality line at a different apparent angle because the axes have different scales.

Do not treat the R² of a fitted line through predicted-versus-actual points as a complete accuracy measure. For instance, predictions can track changes very consistently but all be 10 units too high. The fitted trendline may still have a high R², even though the points sit away from the equality line. Examine the offset from that line and the size of the prediction errors as well.

Calculate and check R² with a worksheet formula

For paired numeric data, Excel’s RSQ function returns the square of the Pearson correlation coefficient. Suppose A2:A6 contains X values 1, 2, 3, 4, and 5, while B2:B6 contains the first series’ Y values 2, 4, 5, 4, and 5. With that layout, enter =RSQ(B2:B6,A2:A6) in an empty cell. It returns 0.6, or 60% when formatted as a percentage.

If C2:C6 contains a second series with Y values 1, 3, 3, 5, and 4, use the same X range and that series’ Y range. With X in A2:A6 and the second series in C2:C6, enter =RSQ(C2:C6,A2:A6). The result is approximately 0.7273, or 72.73%. These values provide a check against a linear relationship, but the chart’s trendline label and RSQ should only be expected to agree when they use the same observations and the same kind of linear fit.

RSQ ignores text, logical values, and empty cells in referenced ranges, but the two ranges must contain corresponding paired observations and have the same number of data points. Inspect the data if the formula returns an error or a result that does not match expectations. A missing value in just one range can break the intended pairing even if Excel returns a number.

Choose the right comparison and avoid common mistakes

Use one shared-axis scatter plot when the series have comparable X values and you want to compare their patterns. If the goal is to compare several categories rather than numeric relationships, a column or line chart may communicate the data more clearly, but an R² trendline may not be meaningful. If you are comparing model performance, define what performance means first: fit to a line, agreement with actual values, average error, or another criterion.

  • Wrong chart type: A line chart may space X values as categories. Use an XY scatter chart when the spacing between numeric X values matters.
  • Wrong series assignment: Check the X and Y ranges in Chart Design > Select Data, particularly when series have different X values.
  • Overlapping labels: Move R² labels by dragging them, or position them in separate parts of the plot so readers can tell which series each belongs to.
  • Misleading comparisons: Keep axes consistent when comparing series across separate charts. Different axis limits can make similar patterns look very different.
  • Too much confidence in one number: Inspect the points and relevant errors alongside R². A statistic summarizes aspects of the data; it does not replace understanding the measurement process.

For readability, use a concise legend, restrained gridlines, and a useful scale. If the chart contains many points, small markers with some transparency can reduce overplotting. Avoid adding a trendline simply because Excel offers one: if the relationship is not reasonably linear or the chart is descriptive rather than model-based, label the plot clearly and explain what the fitted line represents.

Frequently Asked Questions

Can I show an R² value for each series?
Yes. Add a separate trendline to each plotted series and select Display R-squared value on chart for each one. The value belongs to that series’ trendline, not to the chart as a whole.

Why is the R² missing from my chart?
Check that a trendline has been added to a data series and that Display R-squared value on chart is selected in the Format Trendline pane. Confirm that the series contains enough valid numeric paired observations for the fit.

Is a higher R² always better?
No. It indicates a closer fit to the specified trendline in the data shown, not necessarily greater accuracy, causation, or better performance on new data. For predictions, also compare errors and inspect agreement with the actual values.

Can Excel calculate R² without a chart?
Yes. Use the RSQ worksheet function with the Y-value range first and the paired X-value range second. The two ranges must have the same number of observations and correspond row by row.

Should I use a line chart or a scatter plot?
Use a scatter plot when X values are numeric and the distance between them matters. Use a line chart when the horizontal axis represents categories or an ordered sequence where equal spacing is intended.

Does a high R² mean predictions match actual values?
Not necessarily. A high R² can describe a strong linear association even when predictions are consistently offset or scaled incorrectly. Plot predicted against actual values with an equality reference line to assess agreement visually.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.