How to Make Trendline Go Through Origin in Google Sheets

When you add a trendline to a chart in Google Sheets, you expect it to accurately represent the relationship in your data. But sometimes, the standard trendline doesn't quite behave as it should - specifically, it doesn't pass through the origin (0,0), even when logic dictates it must. This article will show you exactly how to solve that and create a trendline that starts right where it's supposed to.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Should a Trendline Go Through the Origin, Anyway?

Before diving into the "how," let's quickly cover the "why." While it might seem like a minor cosmetic issue, forcing a trendline through the origin is crucial when the relationship between your two variables logically requires it. In these cases, it’s not just about aesthetics, it’s about creating a mathematically honest and accurate model of your data.

Think about a few common business scenarios:

• Sales and Revenue: If you sell zero units of a product, your revenue from that product must be zero. The line on your chart should start at (0,0).
• Ad Spend and Clicks: If you spend $0 on an ad campaign, you will get zero clicks from that campaign. The chart should reflect this with a line starting at the origin.
• Time Worked and Items Produced: If zero hours are worked, zero items are produced.

In all these examples, 'zero in' must result in 'zero out'. A standard trendline, however, doesn’t know this. It simply calculates the line of best fit using a formula like y = mx + b, where:

• y is your dependent variable (like Revenue).
• m is the slope of the line.
• x is your independent variable (like Sales).
• b is the y-intercept - the point where the line crosses the vertical axis.

The algorithm might calculate a small y-intercept (for example, $5.50) even if it's statistically insignificant and logically impossible. Forcing the trendline through the origin is simply telling your model, "Hey, listen, I know for a fact that the 'b' value here is zero. Now, calculate the best-fit line based on that fact."

Graphed

Still Building Reports Manually?

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

Watch the Demo

The Challenge: Google Sheets Lacks the "Set Intercept" Option

If you're coming from Microsoft Excel, you might be looking for a simple "Set Intercept = 0" checkbox in the chart formatting options. This is a common source of frustration for many Google Sheets users - because that option doesn't exist. There is no simple button to click to force your trendline through the origin.

Don't worry, though. While there isn't a simple setting, there is a reliable workaround. It involves using a formula to calculate the slope ourselves and then plotting that result as a new data series on the chart. It sounds more complicated than it is, and we'll walk through it step-by-step.

Creating a Trendline That Passes Through the Origin

For this walkthrough, we'll use a sample dataset showing daily ad spend and the corresponding website clicks. Logically, $0 in spend should result in 0 clicks, so this is a perfect candidate for a trendline that passes through the origin.

Here is our sample data:

We've put Ad Spend ($) in Column A and Website Clicks in Column B.

Here's what a standard scatter plot and trendline look like in Google Sheets. Notice how the default trendline (the dotted red line) doesn't start at (0,0). It intercepts the y-axis a little bit above zero, suggesting we get some clicks for no money, which is incorrect.

Now, let's fix it.

Step 1: Calculate the Slope Forcing a Zero Intercept with LINEST

The key to this entire process is the powerful but often overlooked LINEST (an abbreviation for "Line Estimation") function. This function uses the "least squares" method to calculate a straight line that best fits your data and returns an array that describes the line.

Its real superpower for our purpose is its optional third argument, [calculate_b]. When you set this to FALSE, you're telling the function to force the y-intercept (b) to be zero.

The syntax we'll use is:

=LINEST(known_data_y, [known_data_x], [calculate_b])

• known_data_y: The range containing your y-values (e.g., Website Clicks). In our example, this is B2:B6.
• known_data_x: The range containing your x-values (e.g., Ad Spend). For us, A2:A6.
• calculate_b: We will set this to FALSE to force the y-intercept to be 0.

Click on an empty cell somewhere else on your sheet (say, E2) and enter the following formula:

=LINEST(B2:B6, A2:A6, FALSE)

When you press enter, this cell will now contain the slope (m) of our perfect, origin-passing trendline. In this example, the value is approximately 5.09.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 2: Create a New Data Column for the Trendline

Now that we have the slope (m), we can use it to create our own trendline data points. Remember the formula y = mx? That's what we'll use. We'll create a new column next to our original data called "Trendline Clicks."

In cell C2, enter this formula:

=$E$2 * A2

Important Notes on This Formula:

• A2 is our first x-value (Ad Spend).
• $E$2 is the slope we just calculated. The dollar signs ($) make it an absolute reference. This is crucial because it means when you drag the formula down, it will always refer to the slope value in cell E2, but the A2 reference will update correctly to A3, A4, and so on.

After entering the formula in C2, click the small blue square on the bottom right corner of the cell and drag it down to C6. Your sheet will now have a third column showing the calculated y-value for each point on your forced trendline.

Step 3: Add the New Trendline Series to Your Chart

With our new data ready, it’s time to add it to our chart.

1. Double-click your existing chart to open the Chart editor pane on the right.
2. Under the Setup tab, find the "Series" section. Click on "Add series."
3. A box will pop up asking you to "Select a data range." Click the grid icon to select your new "Trendline Clicks" data, C1:C6. Make sure to include the header C1.

Google Sheets will add this new data to your chart, but it will likely default to showing it as more dots on your scatter plot, which isn't what we want.

Step 4: Format the New Series to Look Like a Trendline

The final step is to make this new series look like a proper trendline.

1. In the chart editor pane, switch from the Setup tab to the Customize tab.
2. Open the "Series" dropdown. You should see both your original "Website Clicks" series and your new "Trendline Clicks" series.
3. Select "Trendline Clicks" from the dropdown.
4. This gives you all the formatting options you need. Let’s make it look like a trendline:

You now have a perfect trendline that accurately visualizes your data's relationship while correctly passing through the origin (0,0). You can also go back and remove the original, incorrect trendline that Google Sheets created automatically. Simply select it under the "Series" dropdown and uncheck the "Trendline" box to remove it.

Graphed

Still Building Reports Manually?

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

Watch the Demo

A More Advanced Approach: The =ARRAYFORMULA Trick

For those who like to keep their sheets cleaner, you can combine Steps 1 and 2 by embedding the LINEST function directly within an ARRAYFORMULA. This lets you create the entire trendline column with a single formula in the header cell, so you don’t need to drag anything down.

Instead of the previous steps, just enter this formula directly in cell C2:

=ARRAYFORMULA(LINEST(B2:B6, A2:A6, FALSE) * A2:A6)

This formula first calculates the slope that goes through the origin, and then immediately multiplies that single slope value against your entire range of x-values (A2:A6) in one go, filling the column automatically. The results are identical, it's just a more elegant and efficient way to achieve them!

Final Thoughts

Forcing a trendline through the origin in Google Sheets is a classic example of something that should be simple but requires a clever workaround. By using the LINEST function to calculate your own slope with a mandated zero intercept, you can build a new data series to plot a mathematically correct trendline for your data.

Digging through formulas like LINEST highlights how much a time sink spreadsheets can be, especially when you have to perform these corrections across multiple reports. Instead of manually wrangling data in sheets like this, we built Graphed to automate this grunt work. We let you connect your data sources - like Google Ads and Google Analytics - and use natural language to create the exact charts you need, already formatted correctly. There's no need to hunt for formula workarounds, you just ask for what you need to see, and a real-time, accurate dashboard is built instantly.