How to Get R Value on Excel Graph

Finding the relationship between two sets of data is easier than you think, and Excel has a built-in feature to do just that right on your chart. This guide will walk you through how to display the R-squared value on an Excel graph to quickly see how well your data fits a trend. We'll also cover what this value actually means and other ways to calculate it.

First Things First: What Are R and R-squared?

You might see the terms R and R-squared used in data analysis, and while they're related, they tell you slightly different things. It's a common point of confusion, especially since Excel graphs specifically display the R-squared value.

Here’s a quick breakdown:

• R (Correlation Coefficient): This value tells you about the strength and direction of a linear relationship between two variables. It ranges from -1 to 1. A value of 1 means a perfect positive correlation (as one variable goes up, the other goes up), -1 means a perfect negative correlation (as one goes up, the other goes down), and 0 means no correlation at all.
• R-squared (Coefficient of Determination): This value tells you how much of the variation in your dependent variable (the one on the Y-axis) can be explained by an independent variable (the one on the X-axis). It’s calculated by squaring the R value, which is why it's always a positive number between 0 and 1 (or 0% and 100%).

When you add this value to an Excel chart, you are displaying the R-squared value. It’s what most people are looking for when they want to quickly assess the strength of a model directly on a visual.

Step-by-Step: How to Add R-squared to an Excel Graph

Let's get right into the steps. For this example, we’ll use a common business scenario: analyzing the relationship between monthly ad spend and website traffic.

Step 1: Set Up Your Data in Two Columns

Your data needs to be organized properly for Excel to create the chart. Place your independent variable (the cause) in one column and your dependent variable (the effect) in the adjacent column.

• Independent Variable (X): The factor you control or change. In our case, this is Monthly Ad Spend.
• Dependent Variable (Y): The factor you're measuring. Here, it’s Monthly Website Sessions.

Here’s how your data might look:

Step 2: Create a Scatter Plot

A scatter plot is the perfect chart type for visualizing the relationship between two numerical variables because it plots each pair of values as a single point.

1. Click and drag to select all of your data, including the headers.
2. Go to the Insert tab on Excel's ribbon.
3. In the Charts group, click the icon for Insert Scatter (X, Y) or Bubble Chart.
4. Choose the first option, the basic Scatter plot.

Excel will instantly generate a chart showing your ad spend on the X-axis and website sessions on the Y-axis.

Step 3: Add a Trendline to the Chart

The scatter plot shows the dots, but the trendline shows the pattern. A trendline is a straight or curved line that visually represents the general direction of your data.

1. Click on your newly created chart to select it.
2. Look for a small plus sign (+) icon to appear in the top-right corner of the chart. This is the Chart Elements button.
3. Click the + icon. A menu of chart elements will appear.
4. Hover over Trendline and click the arrow that appears. Select Linear. You will now see a dotted line cutting through your data points.

Step 4: Display the R-squared Value on the Chart

This is the final step where you make the R-squared value visible. You did all the prep work, and now Excel's menu will do the rest.

1. In that same Chart Elements menu, click the arrow next to Trendline again and select More Options…. This will open the Format Trendline pane on the right side of your screen.
2. Scroll down to the bottom of the Trendline Options.
3. You'll see a checkbox labeled Display R-squared value on chart. Check this box.

And that’s it! A small text box with the R² value will immediately appear on your chart. You can click and drag this text box to move it anywhere on the chart for better visibility.

Bonus Tip: Display the Equation of the Line

Right below the R-squared option, you'll see another helpful checkbox: Display Equation on chart. Checking this box shows the linear equation (in the format y = mx + b) for your trendline. This is incredibly useful for making predictions. For example, you could use the equation to estimate your website traffic if you increased your ad spend to $5,000.

How to Interpret the R-squared Value

Now that you have the R² value on your chart, what does it mean? R-squared tells you the proportion of the variance in the dependent variable that is predictable from the independent variable.

That might sound complicated, but it's simpler in practice. It's a percentage that tells you how well your model (the trendline) fits your data.

• An R-squared value of 0.92 means that 92% of the variation in your website traffic can be explained by your ad spend. The remaining 8% is caused by other factors. This indicates a very strong relationship.
• An R-squared value of 0.35 means that only 35% of the change in website traffic can be attributed to ad spend. This is a much weaker relationship, suggesting other variables play a major role.
• An R-squared value of 0 means there's no correlation at all. The trendline does not explain the data whatsoever.

What counts as a "good" R-squared value depends entirely on the context. In tightly controlled scientific experiments, you might expect an R² of 0.95 or higher. In social sciences or marketing, where many unpredictable human factors are at play, a value of 0.60 could be considered quite strong and useful.

Alternative: Using Excel Formulas to Calculate R and R-squared

Sometimes you need the R or R-squared values without creating a chart. Excel has dedicated formulas for this, making it simple to calculate these figures directly in a cell.

Calculating R (Correlation Coefficient) with CORREL

To find the R value, which tells you about the direction and strength of the relationship, use the CORREL function.

The syntax is: =CORREL(array1, array2)

Using our example data, with ad spend in cells A2:A7 and website sessions in cells B2:B7, the formula would be:

=CORREL(A2:A7, B2:B7)

This will return a value between -1 and 1. A result of, say, 0.98 indicates a very strong positive correlation.

Calculating R-squared with RSQ

To directly calculate the R-squared value, use the RSQ function. This is especially helpful as it aligns with what a chart's trendline shows you.

The syntax is: =RSQ(known_y's, known_x's)

It’s important to get the order right here: the dependent variable (Y) comes first.

For our data, the formula would be:

=RSQ(B2:B7, A2:A7)

This formula gives you the exact same R-squared value you would get by adding a trendline to your chart, but without adding the visual component.

Final Thoughts

Analyzing the relationship between two variables is a cornerstone of data-driven decision-making, and Excel makes this process accessible to everyone. By adding a trendline and displaying the R-squared value on a scatter plot, you can get an immediate visual and statistical understanding of how well one factor predicts another.

While Excel is great for this sort of direct analysis, it often starts with hours of manual work—downloading CSVs, cleaning data, and stitching together reports just to get your data in the right format. We built Graphed to remove that drag. You connect your core systems like Google Analytics, Shopify, and your ad platforms in a few clicks. Then, you can simply ask in plain English "Compare our monthly Facebook ad spend to Shopify sales" and instantly get back a dashboard with real-time data, letting you focus on insights instead of data wrangling.