How to Get Trendline Equation in Excel

Adding a trendline to a chart in Excel is a quick way to see which direction your data is heading. But seeing the line is only half the story, getting the actual mathematical equation for that trendline is where you can unlock real predictive power. This article will show you exactly how to display that equation on your chart and, more importantly, how to interpret what it means for forecasting and data analysis.

Why You Need the Trendline Equation in Excel

A trendline, also known as a line of best fit, is a straight or curved line through the data points on your chart that shows the general pattern or direction. While the visual is helpful, the underlying equation is what turns your simple chart into a powerful forecasting tool. The formula, which Excel can calculate and display for you, quantifies the relationship between your X and Y variables in a clean, mathematical way.

Here’s why that’s so useful:

• Predict Future Values: Once you have the equation, you can plug in future values for 'x' to predict what 'y' will be. For example, if you know the relationship between your ad spend (x) and your revenue (y), you can forecast your revenue if you decide to increase your ad spend next month.
• Understand the Relationship: The equation tells you exactly how much your dependent variable (like sales) changes for every single unit increase in your independent variable (like ad spend or website traffic). It gives you a specific number to work with, not just a vague "up and to the right" idea.
• Measure Your Model’s Accuracy: Along with the trendline equation, Excel can show you the “R-squared” value. This number tells you how well the line actually fits your data, giving you confidence in how reliable your forecasts might be.

Simply put, getting the equation is the difference between guessing where your business is going and having a data-informed model to guide your decisions.

Step 1: Create a Scatter Plot or Line Chart

Before you can add a trendline, you need a chart. For analyzing the relationship between two different variables, a scatter plot is almost always the best choice. If you're looking at data over a specific time period (like monthly sales), a line chart also works well.

Let's use a common business example: tracking monthly ad spend against resulting sales revenue. This helps us see if our advertising efforts are actually paying off.

Here’s how to set up the data and create the chart:

1. Organize Your Data: Set up your data in two columns. The independent variable (the one you control or that comes first, like time or ad spend) should be in the left column (Column A). The dependent variable (the one you are measuring, like sales revenue) should be in the right column (Column B).

Example Data:

1. Select Your Data: Click and drag to highlight all the cells containing your data, including the headers.
2. Insert the Chart: Go to the Insert tab on Excel’s top ribbon. In the Charts section, find the icon that looks like a plot with dots on it. This is the Insert Scatter (X, Y) or Bubble Chart option. Click it.
3. Choose the Chart Type: Select the first option, the basic Scatter plot.

Excel will immediately generate a chart showing your ad spend on the horizontal (X) axis and your sales revenue on the vertical (Y) axis. You should already be able to see a rough pattern – as spend goes up, so does revenue.

Step 2: Add a Trendline and Display Its Equation

Now that you have your scatter plot, adding the trendline and its equation takes just a few clicks. The process is very similar across modern versions of Excel for both Windows and Mac.

1. Add Chart Element: Click anywhere on your chart to select it. When selected, you'll see a green “+” icon appear in the top-right corner of the chart container. This is the Chart Elements button. Click it.
2. Select a Trendline: A menu will appear with elements like Axes, Chart Title, etc. Find Trendline in the list. Check the box next to it to add a basic linear trendline to your chart.
3. Open More Options: To get the equation, you need more detailed options. Hover over Trendline again and then click on the small black arrow that appears to the right. From the sub-menu, select More Options…

This action will open the Format Trendline pane on the right side of your Excel window.

1. Display the Equation and R-Squared Value: In the Format Trendline pane, make sure you are in the Trendline Options tab (the icon with three green bars). Scroll down to the bottom of the options list.

Check the box next to Display Equation on chart.

While you're here, it’s a great idea to also check the box for Display R-squared value on chart.

As soon as you check these boxes, a small text box will appear on your chart containing the trendline equation and the R-squared value. You can click on this text box and drag it to a more visible location on your chart.

Step 3: What Does the Trendline Equation Mean?

Your chart now shows an equation that looks something like y = 21.605x + 5493.8. It might seem intimidating if you haven't seen it since a school math class, but it’s straightforward to break down. The formula follows the standard format for a straight line: y = mx + b.

Here’s what each part means in our business context:

• y is the dependent variable - the value you are trying to predict. In our case, y = Sales Revenue.
• x is the independent variable - the value you are changing. In our case, x = Ad Spend.
• m is the slope of the line. This is the most valuable part of the equation. It tells you the rate of change. Looking at our example equation, the slope is 21.605. This means that for every one-unit increase in x (i.e., for every $1 we increase ad spend), our Sales Revenue (y) is predicted to increase by $21.61.
• b is the y-intercept. This is the value of y when x is 0. In our example, b = 5493.8. This means that if we spent $0 on ads, our model predicts we would still generate about $5,494 in sales revenue (this is your baseline from organic traffic, repeat customers, etc.).

Bonus: Making Sense of the R-Squared Value

Next to your equation on the chart, you'll see a value like R² = 0.9634. This is your R-squared value, and it’s a quick measure of how well the trendline fits your data.

• It's a value between 0 and 1 (it’s often converted to a percentage).
• An R² of 1 means the line fits the data perfectly. An R² of 0 means there is no linear relationship at all.
• Our example of 0.9634 is very strong. It means that about 96.3% of the variation in our sales revenue can be explained by our ad spend. This indicates that ad spend is a very significant driver of sales and that our linear model is a good fit for this data.

As a general rule of thumb, anything above 0.7 is considered a strong correlation, while a value below 0.3 suggests a weak and often unreliable relationship.

Using the Equation to Predict Future Values

Now, let's put the equation y = 21.605x + 5493.8 to work. The management team wants to know what would happen to revenue if you increased the ad budget to $2,500 next month.

You can solve this without guesswork:

• x = 2500 (your new proposed ad spend)
• y = (21.605 * 2500) + 5493.8
• y = 54,012.5 + 5,493.8
• y = 59,506.3

Your model predicts that an ad spend of $2,500 would generate approximately $59,506 in sales revenue. This kind of forecast helps you make smarter budget decisions backed by data.

Advanced Tip: Get the Equation Without a Chart

Sometimes you need the slope and intercept for a financial model or a dashboard but don't actually need to see the chart. You can get these values directly in Excel cells using the SLOPE and INTERCEPT functions.

Using the SLOPE and INTERCEPT Functions

Assuming your sales data is in range B2:B8 and your ad spend data is in A2:A8:

To get the slope (the 'm' value):

Type in an empty cell:

=SLOPE(B2:B8, A2:A8)

Excel will return 21.605.

To get the intercept (the 'b' value):

Type in another empty cell:

=INTERCEPT(B2:B8, A2:A8)

Excel will return 5493.8.

Using these functions is incredibly efficient, as you can build a dynamic forecasting tool right in your spreadsheet. You can have a cell for "Future Ad Spend," and the cell next to it can use the calculated SLOPE and INTERCEPT values to generate an instant revenue forecast.

Final Thoughts

Learning how to display the trendline equation in Excel moves you beyond simple data visualization and into the realm of powerful, predictive analysis. By understanding the y = mx + b formula and the R-squared value, you can turn a simple chart into a functional model for forecasting sales, traffic, or any other important business metric.

Manually pulling data, creating these charts, and updating your forecasts in Excel is a great skill to have, but it can quickly become repetitive and inefficient when data is spread across different platforms like Google Analytics, Shopify, Facebook Ads, and Salesforce. That's why we built Graphed. We connect to all your marketing and sales sources automatically, so you can just ask questions in plain English, like, "show me a dashboard of ad spend vs. revenue by campaign," and get a live, interactive visualization in seconds, completely skipping the manual spreadsheet work.