How to Get R2 on Excel Graph

Ever added a trendline to a chart in Excel and noticed that little "R²" value pop up? It’s a number that often gets ignored, but understanding it can totally change how you analyze your data. This simple metric tells you how much of the story your data is telling, and this guide will show you exactly how to find it and, more importantly, what it actually means for your business.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

We’ll walk through how to calculate and display the R-squared value on any Excel graph. Along the way, we'll cover what it represents in plain English and how you can use it to make smarter, data-informed decisions without needing a degree in statistics.

What Is R-Squared, Anyway? (And Why Should You Care?)

In the simplest terms, R-squared (also known as the coefficient of determination) is a statistical measure that tells you how well your data points fit a regression line or curve. Think of it as a "goodness-of-fit" score on a scale from 0 to 1.

• An R-squared of 1 means the model is a perfect fit. All of your data points fall exactly on the trendline. This rarely happens with real-world data unless you've made a mistake!
• An R-squared of 0 means the model doesn’t explain any of the variability in the data. There's no discernible linear relationship between your variables.

The closer the R² value is to 1, the better the trendline represents the data. For example, if you're plotting marketing spend against website traffic and get an R² of 0.85, it means that 85% of the variation in your website traffic can be explained by changes in your marketing spend. The other 15% is due to other factors (like seasonality, promotions, or random chance).

This is incredibly useful because it helps you validate your assumptions. Is your ad budget really driving sales? R-squared gives you a number to quantify that relationship, turning a gut feeling into a measurable insight.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

A Quick Word of Caution

While R-squared is a great tool, remember one golden rule of data analysis: correlation does not equal causation. A high R² value shows a strong relationship between two variables, but it doesn't prove that one causes the other. For instance, ice cream sales and sunscreen sales might have a high R², but one doesn't cause the other - a third factor (hot weather) causes both. Always use context and business knowledge alongside your numbers.

Step 1: Get Your Data Ready for Analysis

Before you can create a chart, you need to structure your data correctly in Excel or Google Sheets. All you need are two columns of numerical data that you believe have a cause-and-effect relationship.

You’ll need an:

• Independent Variable (X-axis): This is the factor you control or change. Examples include ad budget, number of social media posts, or discount percentage.
• Dependent Variable (Y-axis): This is the outcome you measure. Examples include total sales, website sessions, or conversion rate.

Here’s a simple sample dataset. Let's imagine you own a small e-commerce store and you're tracking your weekly ad spend against the number of sales you generated.

Your data in Excel might look like this:

Make sure your independent variable ("Ad Spend") is in the left column and your dependent variable ("Sales") is in the right column. This helps Excel correctly assign them to the X and Y axes.

Step 2: Create a Scatter Plot to Visualize Your Data

Once your data is set up, the next step is to visualize it. For seeing the relationship between two variables, a scatter plot is the perfect choice. Each point on the graph will represent a pair of values from your table (e.g., $100 in spend and 15 sales).

Here’s how to create one:

1. Select Your Data: Click and drag your cursor to highlight both columns of data, including the headers.
2. Insert Chart: Go to the Insert tab on the Excel ribbon.
3. Choose Scatter Plot: In the Charts section, click on the icon that looks like a set of dots. Select the first option, which is the basic Scatter chart.

Excel will instantly generate a scatter plot on your worksheet. You should now see a collection of points that reflect your data. In our example, you'd likely see the points trending upwards from left to right, suggesting a positive relationship between ad spend and sales.

Step 3: Add the Trendline and Display the R-Squared Value

Now for the main event. With your scatter plot created, adding a trendline and displaying the R-squared value takes just a few clicks.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Add the Trendline

A trendline is a straight or curved line on a chart that shows the general direction or pattern of the data. It helps you see the underlying trend that might not be immediately obvious just from looking at the raw data.

There are two easy ways to add it:

• Method 1 (Quick Add): Click anywhere on your chart to select it. A green "+" icon (Chart Elements) should appear in the top-right corner. Click it, then check the box next to Trendline.
• Method 2 (Right-Click): Right-click on any of the data points in your scatter plot. From the menu that appears, select Add Trendline...

Either method will add a default linear trendline to your chart.

Find and Display the R-Squared Value

When you add a trendline, the Format Trendline pane will open on the right side of your Excel window. If it doesn't open automatically, right-click the trendline itself and select "Format Trendline."

This pane is where all the controls for your trendline live. Here's how to display the R-squared value:

1. In the Format Trendline pane, make sure you are on the Trendline Options tab (the icon with three green bars).
2. Scroll down to the bottom of the options.
3. You'll see two checkboxes:
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² value will appear on your chart. You can click and drag this text box to move it anywhere you'd like for better visibility.

Pro Tip: Find the Best Fit

Under Trendline Options, you'll see choices like Linear, Logarithmic, Polynomial, and Exponential. Excel defaults to Linear, but your data might have a different type of relationship. Try clicking different options and see how the R² value changes. The model with the highest R² value is a better statistical fit for your data. Just be careful not to "overfit" with an overly complex model like a high-order polynomial unless you have a good theoretical reason for it.

Step 4: Making Sense of Your R-Squared Value

For our sample data, you will likely see an R² value around 0.97 or 0.98. But what does that number actually tell you?

An R² of, say, 0.978 means that 97.8% of the variance in the number of sales can be explained by the ad spend. This indicates an extremely strong and reliable relationship. For every dollar you spend on ads, you can predict the resulting increase in sales with a high degree of confidence.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

What is a "Good" R-Squared Value?

The definition of a "good" R² is highly context-dependent:

• In precise fields like physics or chemistry, where variables are tightly controlled, you might expect R² values of 0.99 or higher.
• In less predictable fields like marketing or social sciences, where human behavior introduces a lot of "noise," an R² of 0.60 or 0.70 might be considered very strong. A model explaining 60% of customer behavior is often a huge win!

So, a low R² isn't necessarily a failure. It might simply mean that the one independent variable you chose isn't the main driver of the outcome. For sales, factors like website performance, product pricing, and brand reputation all play a role. A low R² is an insight in itself - it tells you to go look for other variables that might be influencing your results.

By finding and interpreting the R-squared value, you move beyond just saying, "when spend goes up, sales go up.” You can now say, "Our ad spend accounts for approximately 98% of the fluctuation in our weekly sales," which is a far more powerful statement when presenting to your team or stakeholders.

Final Thoughts

Adding the R-squared value to your Excel charts is a small technical step that provides a huge analytical benefit. It transforms a simple visualization into a powerful tool for understanding the relationships in your data, helping you separate a weak correlation from a strong one. Having this skill means you can validate your strategies and communicate your findings with more clarity and confidence.

While Excel is great for this type of quick analysis, we know the real grind often isn't the analysis itself, but getting all the data into the spreadsheet in the first place. That’s why we built Graphed. Our platform was designed to skip the entire routine of manually downloading CSVs from different sources like your ad platforms, CRM, and e-commerce dashboards. We connect directly to your data, so instead of wrangling spreadsheets, you can just ask questions in plain English - like "show me the relationship between my Meta ads spend and Shopify sales" - and instantly get a live, updating dashboard that does the work for you.