How to Add Trendline and R2 in Excel

Adding a trendline to an Excel chart instantly turns a scattered set of data points into a clear visual story. It helps you spot patterns, make forecasts, and understand the relationship between variables. This article will walk you through exactly how to add a trendline to your chart in Excel and, just as importantly, how to include the R-squared value to see how well that trendline actually fits your data.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What Exactly Is a Trendline?

A trendline, also known as a line of best fit, is a straight or curved line on a chart that shows the general direction of a series of data points. Think of it as summarizing the noise. Your individual data points might jump up and down, but the trendline cuts through the middle of them to show the overall pattern: are things generally increasing, decreasing, or staying flat?

In business settings, trendlines are incredibly useful for:

• Identifying Trends: Is your monthly revenue slowly climbing? Is customer churn decreasing over time? A trendline makes these patterns obvious at a glance.
• Forecasting: Excel can extend a trendline into the future to help you make educated guesses about future performance based on past data. For example, you can forecast next quarter's sales based on the trend from the previous year.
• Spotting Relationships: When you plot two variables on a scatter chart (like ad spend vs. sales), the trendline shows the relationship or correlation between them. A line sloping upwards indicates a positive correlation - as one goes up, the other tends to go up, too.

While there are several types of trendlines (linear, exponential, polynomial), the most common is the linear trendline, which shows a steady, straight-line rate of increase or decrease.

And What About R-Squared?

Simply adding a trendline is only half the story. The R-squared value, or coefficient of determination, tells you how reliable that trendline is. It’s a statistical measure rendered as a value between 0.0 and 1.0 that tells you how much of the variation in your dependent variable (the Y-axis) can be explained by the independent variable (the X-axis).

That might sound complicated, but here’s a simple way to think about it:

• An R-squared of 1.0 (or 100%) means all your data points fall perfectly on the trendline. The fit is perfect.
• An R-squared of 0.0 (or 0%) means the trendline does not explain any of the variability in your data points at all. There is no relationship.
• An R-squared of 0.85 (or 85%) means that 85% of the variation in your Y-axis data can be explained by your X-axis data. This is generally considered a strong fit.

Essentially, the R-squared value is a grade for your trendline. The closer it is to 1.0, the more confidence you can have in the relationship your trendline is suggesting. If you add a trendline to your data and the R-squared is 0.12, you know that the model doesn't fit the data well and any forecasts based on it will likely be inaccurate.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 1: Get Your Data and Chart Ready

Before you can add a trendline, you need a chart. The best chart type for analyzing the relationship between two different variables is a scatter chart, but you can also add trendlines to bar, line, and column charts.

Let's use a common marketing scenario: you’re tracking website sessions and sales per month. You want to see if more website traffic leads to more sales.

Set up your data in two columns. Your independent variable (the thing you control or that comes first, like "Website Sessions") should be in the left column, and your dependent variable (the result you're measuring, like "Monthly Sales") should be in the right column.

Here’s our sample data table:

Now, let's create a scatter chart:

1. Highlight both columns of your data, including the headers.
2. Go to the Insert tab on the Excel ribbon.
3. In the Charts section, click the icon for Insert Scatter (X, Y) or Bubble Chart.
4. Select the first option, the basic Scatter plot.

Excel will instantly generate a chart. Each point on the chart represents a month, showing the intersection of website sessions (on the X-axis) and sales (on the Y-axis). Already, you can see the points suggest an upward trend, but a trendline will confirm it and measure its strength.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 2: How to Add the Trendline

With your chart created and selected, adding the trendline is just a few clicks away. Modern versions of Excel offer a very convenient shortcut.

1. Click anywhere on your chart to select it. You'll see editing and formatting buttons appear on the right side.
2. Click the green plus sign icon, which is the Chart Elements button.
3. In the menu that appears, hover your mouse over Trendline. Excel will give you a live preview of the default linear trendline on your chart.
4. Check the box next to Trendline to add it to your chart permanently.

That's it! You now have a solid line cutting through your data points, showing the general upward trend of sales as website sessions increase.

Step 3: How to Display the R-Squared Value

You’ve added the trendline, but is it a good fit? Now it's time to add the R-squared value to find out. A common mistake is stopping after adding the line without checking its statistical relevance.

Here’s how to reveal that all-important number:

1. Go back to the Chart Elements menu (the green plus sign).
2. Hover over Trendline again, but this time, click on the small right-facing arrow that appears.
3. From the sub-menu, select More Options.... This will open the Format Trendline pane on the right side of your screen.
4. In the Format Trendline pane, make sure the Trendline Options tab (the one with the bar chart icon) is selected.
5. Scroll down to the bottom of the options.
6. Check the box for Display R-squared value on chart.

As soon as you check the box, a little text box will appear on your chart showing something like "R² = 0.9782". You can click and drag this text box to a better position on your chart for readability.

Bonus Tip: Display the Equation

While you're in the Format Trendline pane, also check the box for Display Equation on chart. This will add the linear equation (in the form of y = mx + c) to your chart. This formula is the engine behind the line - it tells you exactly how to calculate the expected sales (y) for any given number of website sessions (x).

Graphed

Still Building Reports Manually?

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

Watch the Demo

Customizing Your Trendline: Finding the Best Fit

Excel defaults to a linear trendline, which works well for data with a simple, straight-line relationship. However, relationships in the real world can be more complex. Your data might curve upwards or level off.

The Format Trendline pane is where you can experiment to find the best model for your data. Under "Trendline Options," you'll see several types:

• Exponential: Use for data that rises or falls at an increasingly rapid rate. Think of viral growth.
• Logarithmic: Ideal for data that rises or falls quickly at first, then flattens out. Consider the law of diminishing returns.
• Polynomial: Excellent for volatile data that has peaks and valleys. You can increase the "Order" of a polynomial trendline (e.g., from 2 to 3) to make it fit a more complex curve.
• Power & Moving Average: These are more specialized and generally used in financial or scientific analysis.

How do you know which to choose? The easiest way is to switch between them and watch what happens to the R-squared value. Click on 'Exponential' - does R-squared go up or down? Click on 'Polynomial (Order 2)' - how does that change R-squared?

Your goal is to find the model that provides the highest R-squared value. For our sample data on sessions and sales, the linear model results in an R-squared of 0.9782. This is an incredibly strong fit, so there’s no need to look for a different model. It tells us that nearly 98% of the variation in our monthly sales can be explained by the volume of website sessions. Now that’s a powerful insight!

Final Thoughts

Mastering trendlines and the R-squared value in Excel takes you from simply visualizing data to actively analyzing it. With just a few clicks, you can uncover hidden relationships in your numbers, test your assumptions about what drives business performance, and start making more data-driven forecasts with confidence.

While Excel is fantastic for this kind of manual analysis, the whole process of exporting data into CSVs, building charts, and adding trendlines is what we created Graphed to solve. We connect directly to your live data sources like Google Analytics, Shopify, and Salesforce. Instead of running these analyses yourself, you can simply ask, "create a chart showing the correlation between my Facebook ad spend and Shopify revenue last quarter." Instantly, you'd see the visualization and the insights without juggling spreadsheets - it's a fundamentally faster way to move from data to decisions.