How to Add R2 in Excel Graph

Adding an R-squared value to your Excel graph is the quickest way to see how well your data fits a trendline. It’s a simple but powerful metric that shows the strength of the relationship between your variables without needing a statistics degree. This article will show you how to add R-squared to any chart and explain what that number actually means for your analysis.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is R-Squared (R²)? A Simple Explanation

Before adding it to your chart, let's quickly cover what R-squared means. In simple terms, R-squared (also called the "coefficient of determination") measures how much of the change in one variable can be explained by a change in another variable.

It’s always a value between 0 and 1 (or 0% and 100%):

• An R² of 1 (or 100%) means there's a perfect relationship. All the changes in your dependent variable (Y-axis) are completely explained by the changes in your independent variable (X-axis).
• An R² of 0 (or 0%) means there's no relationship at all. The trendline does not fit the data any better than a simple horizontal line through the average.

For example, let's say you're plotting your monthly ad spend (independent variable) against your website conversions (dependent variable). If you get an R-squared value of 0.82, it means that 82% of the variation in your website conversions can be explained by your ad spend. The remaining 18% is due to other factors not in your model, like seasonality, website changes, or competitor activities.

Essentially, R² gives you a quick score to assess how meaningful your trendline is. A higher R² suggests a stronger correlation and a more reliable trend.

Setting Up Your Data in Excel

To calculate R-squared, you first need data that is properly set up. You need at least two columns: one for your independent variable (X-axis) and one for your dependent variable (Y-axis).

• The independent variable is the factor you are changing or observing, like time, ad spend, or sales calls made.
• The dependent variable is the outcome you are measuring, which hopefully changes in relation to the independent variable, like revenue, user signups, or deals closed.

Here’s a sample dataset we'll use for our examples. We want to see if there's a relationship between our weekly ad spend and the number of leads generated.

(Be sure your data is formatted as numbers, without extra text or currency symbols inside the cells. You can apply number formatting to make it look like currency.)

Sample Data:

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 1: Adding R-Squared Directly to an Excel Chart

The most common and visually intuitive way to get the R-squared value is by adding it to a chart. This method works best with Scatter charts but also applies to Line, Column, and Bar charts in Excel.

Step 1: Create a Scatter Chart

First, highlight your data, including the headers. Navigate to the Insert tab on the Ribbon, find the Charts group, and click the Scatter (X, Y) icon. Select the first option, which is a simple scatter chart with just the data points.

Excel will instantly generate a chart. Your independent variable (Ad Spend) will be on the X-axis (horizontal), and your dependent variable (Leads Generated) will be on the Y-axis (vertical).

Step 2: Add a Trendline to Your Chart

Next, we need to add a line of best fit, which is what the R-squared value will be based on.

1. Click on the chart to select it. You should see three icons appear in the top-right corner.
2. Click the "+" icon, which stands for Chart Elements.
3. In the menu that appears, check the box next to Trendline. A dotted line will immediately appear on your chart, showing the general trend of your data points.

Step 3: Display the R-Squared Value

Now for the main event - getting that R² value on the chart.

1. In the same Chart Elements menu, hover over Trendline and click the small arrow that appears to the right.
2. Select More Options.... This will open up the Format Trendline pane on the right side of your Excel window.
3. Scroll down to the bottom of the Trendline Options.
4. Check the box for "Display R-squared value on chart".

As soon as you check the box, a small text box with the R-squared value will appear on your chart. You can also select "Display Equation on chart" if you want to see the linear formula Excel used for the trendline, which is useful for forecasting.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Format and Position the Label

The default placement of the R-squared label isn't always ideal. You can simply click on the text box containing the R² value and drag it anywhere you like on the chart area for better readability. You can also reformat the text (change the font size, make it bold, etc.) just like any other text box in Excel.

Method 2: Calculating R-Squared with an Excel Formula

Sometimes you don't need a chart - you just want the R-squared value itself for a report or formula. Excel has dedicated functions for this.

Using the RSQ Function

The simplest way to calculate R-squared is with the RSQ function. The syntax is very straightforward:

=RSQ(known_y's, known_x's)

• known_y's: Your dependent variable (the data in Column B).
• known_x's: Your independent variable (the data in Column A).

Using our sample data, you would click an empty cell and type the following formula:

=RSQ(B2:B10, A2:A10)

Press Enter, and Excel will return the R-squared value, which for our data would be something around 0.98. This tells us there's a very strong positive relationship between ad spend and leads.

Using the CORREL Function

Another way to think about R-squared is that it's the result of the correlation coefficient (often represented as "R") multiplied by itself. Excel has a function for this, too: CORREL.

To get the R-squared value this way, you would calculate the correlation and then square the result:

=CORREL(A2:A10, B2:B10)^2

This will give you the exact same result as the RSQ function. While RSQ is more direct, knowing this connection can be helpful for understanding the underlying statistics.

How to Interpret Your R-Squared Value

Now you have your R² value. What does it actually mean? Is it "good" or "bad"?

The definition of a "good" R-squared value depends heavily on your industry and what you're measuring. In machine-calibrated physics experiments, researchers might demand an R² of 0.99 or higher. However, when you're analyzing human behavior - like in marketing or sales - the variables are much less predictable.

• Above 0.70: Generally considered a strong relationship.
• 0.50 to 0.70: Considered a moderate relationship.
• 0.30 to 0.50: A weak relationship.
• Below 0.30: Very little to no relationship.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Beware: Correlation Is NOT Causation

This is the most important rule of data analysis. A high R-squared value shows a strong correlation - the two variables move together - but it DOES NOT prove that one causes the other.

In our example, the high R² (0.98) strongly suggests that spending more on ads leads to more leads. But it doesn't rule out other factors. Maybe a major competitor went out of business during that same period, or we received a great piece of PR that drove extra traffic. R-squared is a starting point for analysis, not the final conclusion.

Final Thoughts

Adding the R-squared value to an Excel chart is a simple action that gives you an immediate quantitative measure of your trendline’s reliability. Whether you add it directly via the Chart Elements menu or calculate it with the RSQ formula, it’s an invaluable tool for making smarter, data-driven observations without having to drown in complex statistics.

Putting together analyses like this in spreadsheets is valuable, but it's often slow and manual, especially when your data lives in different places like Shopify, Google Analytics, and Facebook Ads. At Graphed , we connect directly to your tools, pulling all your live sales and marketing data into one place. From there, I can instantly build you a live dashboard based on simple natural language prompts. Just ask me, "Show me the correlation between ad spend and conversions," and I'll create the chart, add the trendlines, and a lot more - no more exporting CSV files.