Finding the line of best fit on a scatter plot helps turn scattered data points into a clear, predictive model. In Google Sheets, you can easily add this trendline to your charts and display its exact formula, known as the y = mx + b equation. This article will walk you through the entire process, from setting up your data to interpreting what the final equation actually means for your business.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is Y = MX + B Anyway? A Quick Refresher

Before we jump into Google Sheets, let's quickly review what this formula represents. The y = mx + b formula is the equation for a straight line. It's used in many fields, especially data analysis, to describe the relationship between two variables. This kind of analysis is often called linear regression.

Here’s a simple breakdown of each part:

• y: This is the dependent variable. Its value depends on the other variable. Think of it as the result or outcome you're trying to measure or predict (e.g., total sales, website clicks, or customer sign-ups).
• x: This is the independent variable. It's the factor you control or observe to see how it affects 'y' (e.g., ad budget, number of sales calls, or website traffic).
• m: This is the slope of the line. It tells you how steep the line is. In practical terms, the slope represents the rate of change - for every one-unit increase in 'x', 'y' will increase by the value of 'm'. For example, if your slope is 5, it means for every $1 you spend on ads (x), you generate $5 in sales (y). A negative slope would mean that as 'x' increases, 'y' decreases.
• b: This is the y-intercept. It's the value of 'y' when 'x' is equal to zero. Think of it as your baseline. For instance, it could be the number of sales you get from word-of-mouth even if your ad budget (x) is $0.

By finding this equation for your data, you can move from just looking at data points to making educated predictions about future performance.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 1: Get Your Data Ready in Google Sheets

First things first, you need to organize your data properly. For a linear regression and a scatter plot, you need two sets of corresponding numeric data. Your sheet should have two columns, one for your independent variable (x-axis) and one for your dependent variable (y-axis).

It’s best practice to put your independent (x) variable in the left column and your dependent (y) variable in the right column. This helps Google Sheets correctly identify the axes when it creates the chart.

Let's use a common marketing scenario as an example. Suppose you want to see the relationship between your monthly ad spend and the number of leads you generated. Your data might look something like this:

Month | Ad Spend ($) | Leads Generated January | 1000 | 45 February | 1500 | 62 March | 1200 | 51 April | 2000 | 85 May | 2500 | 105 June | 1800 | 77

Step 2: Create a Scatter Plot Chart

Once your data is neatly arranged in two columns, it's time to visualize it. A scatter chart is the perfect choice for this because it shows each pair of data points as a single dot on the graph, making it easy to see the general relationship or trend.

Here’s how to create one:

1. Select your data: Drag your cursor to highlight all the cells containing your data, including the headers (e.g., A1:B7 in our example).
2. Insert the chart: Go to the Google Sheets menu at the top and click on Insert > Chart.
3. Choose the chart type: Google Sheets will try to guess the best chart for your data. If it doesn't automatically create a scatter plot, you can change it manually. In the Chart editor pane on the right, go to the Setup tab. Click on the dropdown menu under 'Chart type' and select Scatter chart.

You should now see a graph with several points plotted on it. Each point represents a month's ad spend and the corresponding leads generated. Just by looking at the chart, you can probably see a general trend - as ad spend goes up, so do the leads.

Step 3: Add the Trendline and Display the y = mx + b Equation

Now for the main event. With your scatter plot created, we can add the line of best fit, or trendline, and display its underlying equation.

Your Chart editor should still be open on the right side of your screen. If not, just double-click on your chart to open it again.

1. Navigate to the customization options: In the Chart editor, click on the Customize tab.
2. Open the Series section: Find and click on the Series dropdown. This is where you can style your data points, but more importantly, it's where the trendline option lives.
3. Add the Trendline: Scroll down within the 'Series' options until you see a checkbox for Trendline. Click it. A straight line will immediately appear on your chart, running through your data points. Google Sheets has automatically calculated the line that best fits your data.
4. Show the Equation: Now, look for the 'Label' dropdown menu right below the Trendline checkbox. By default, it's set to 'None'. Click on it and select Use Equation from the options.

Voilà! The y = mx + b equation will pop up on your chart, often in the legend or near the trendline. Congratulations, you've done it.

Graphed

Still Building Reports Manually?

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

Watch the Demo

A Bonus Tip: Show R-Squared (R²)

Just below the Use Equation option, you'll see a checkbox for Show R². It’s highly recommended you check this box. R-squared is a statistical measure that tells you how well your trendline fits your actual data. Its value ranges from 0 to 1.

• An R² value close to 1 (like 0.95) indicates that your model is a great fit and explains a large portion of the variability in your data.
• An R² value close to 0 suggests the model is a poor fit and there's little to no linear relationship.

Displaying R² gives you a quick way to judge how confident you should be in your trendline’s predictions.

Step 4: How to Read and Use Your Equation

Simply having the equation on your chart isn't enough, the real value comes from understanding what it means and how you can use it.

Based on our sample data, Google Sheets might produce an equation like Leads Generated = 0.041x + 3.12 (your numbers may vary slightly).

Let's break this down:

• The y-intercept (b): In this case, b is 3.12. This means that if you spent $0 on ads (x = 0), the model predicts you'd still get about 3 leads. This is your baseline, perhaps from existing customers or organic channels.
• The slope (m): The slope is 0.041. This is the most powerful part of the equation. It means for every additional $1 you spend on advertising, you can expect to generate an additional 0.041 leads. We can use this to figure out the cost per lead by flipping the number: 1 / 0.041 = $24.39 per lead.

Making Predictions

You can now use this formula as a simple predictive tool. For example, what if you're planning to spend $3,000 next month? Just plug it into the formula:

y = (0.041 * 3000) + 3.12

y = 123 + 3.12

y = 126.12

Your model predicts you will generate approximately 126 leads with a $3,000 ad budget. This helps with forecasting, goal setting, and justifying budget decisions.

Step 5: Get 'm' and 'b' with Standalone Formulas

Sometimes you don't need the chart at all - you just need the numbers for the slope and intercept for a calculation. Google Sheets has dedicated formulas for that:

Graphed

Still Building Reports Manually?

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

Watch the Demo

The SLOPE Formula

This formula directly calculates the slope (m) of the line of best fit.

• Syntax: =SLOPE(data_y, data_x)
• In our example: =SLOPE(B2:B7, A2:A7)

The result of this formula should be the same 'm' value you see on your chart's equation.

The INTERCEPT Formula

This formula works just like SLOPE, but it calculates the y-intercept (b).

• Syntax: =INTERCEPT(data_y, data_x)
• In our example: =INTERCEPT(B2:B7, A2:A7)

Using these formulas can be much faster if you just want to quickly build a predictive model directly in your cells without going through the chart creation process.

Final Thoughts

Displaying the y = mx + b equation on a Google Sheets graph is a powerful way to turn raw numbers into a clear relational model. By adding a trendline and showing its equation, you can easily visualize trends, calculate rates of change, and make data-driven forecasts to better inform your business decisions.

While tools like Google Sheets are fantastic for this kind of manual analysis, this process can get time-consuming when your data is scattered across multiple platforms. At Graphed , we created a way to connect your marketing and sales data sources (like Google Ads, Shopify, or HubSpot) and generate these insights automatically. Instead of setting up charts and formulas, you can just ask a question like, "Show me the relationship between ad spend and revenue last quarter," and get an interactive dashboard in seconds, freeing you up to focus on strategy instead of manual reporting.