How to Show Equation of Trendline in Google Sheets

Adding a trendline to a chart in Google Sheets is a great way to spot patterns in your data, but displaying its equation is where the real power lies. This simple line of text unlocks the ability to make data-driven forecasts right inside your spreadsheet. This tutorial will walk you through exactly how to create a trendline, show its equation, and use that equation to make confident predictions about future performance.

What is a Trendline Equation, and Why Should You Care?

A trendline (or "line of best fit") is a straight or curved line on a chart that shows the general direction or pattern of your data points. It smooths out the ups and downs to give you a clearer picture of the underlying relationship between two variables, like time and sales, or ad spend and website traffic.

But the line itself is just a visual guide. The equation is the predictive engine behind it. You might remember the formula y = mx + b from school - that's what we're talking about. When Google Sheets displays this equation for you, it's handing you a custom formula that describes the exact mathematical relationship in your specific dataset.

Here’s what it means in a business context:

• y: The outcome you want to predict (e.g., monthly sales).
• x: The input you control or measure (e.g., your marketing budget).
• m: The slope. This is the most important number! It tells you how much 'y' changes for every one-unit increase in 'x'. For example, a slope of 1.5 could mean that for every additional $1 you spend on ads, you generate $1.50 in sales.
• b: The y-intercept. This tells you the baseline value of 'y' when 'x' is zero. In our example, it would be your expected sales with a $0 marketing budget (perhaps from organic traffic or word-of-mouth).

By getting this equation, you can plug in hypothetical values for 'x' to predict future outcomes for 'y', turning your historical data into a powerful forecasting tool.

First, Prepare Your Data for Analysis

Before creating any charts, your data needs to be structured properly. For a trendline, you need two columns of corresponding numerical data: an independent variable and a dependent variable.

• Independent Variable (X-axis): This is the factor you control or that changes independently, like time (Day 1, Day 2, etc.), ad spend, or the number of sales calls made.
• Dependent Variable (Y-axis): This is the metric that you suspect is influenced by the independent variable, like website traffic, revenue, or deals closed.

Let's use a clear example. Imagine we're tracking the relationship between weekly social media ad spend and the number of leads generated. Our data in Google Sheets would look like this:

Example Data:

Make sure your data is clean, contains only numbers (no stray symbols or text), and is organized in two adjacent columns.

Step-by-Step Guide: Adding a Trendline and its Equation

With your data set up, getting the trendline and its equation takes just a few clicks. We'll use the sample data from above.

1. Create a Scatter Plot Chart

A scatter plot is the best chart type for visualizing the relationship between two variables, making it ideal for a trendline analysis.

• Select both columns of data, including the headers (e.g., A1 through B7).
• Go to the menu bar and click Insert → Chart.
• Google Sheets is pretty smart and will often default to a scatter plot. If it doesn't, go to the Chart editor on the right-hand panel, click the Setup tab, and select Scatter chart from the 'Chart type' dropdown menu.

You should now see a chart with your data points plotted on it, showing the relationship between ad spend and leads.

2. Add the Trendline to Your Chart

Now we'll add a visual indicator of the trend.

• With your chart selected, open the Chart editor panel (if it's not already open, double-click the chart).
• Go to the Customize tab.
• Click to expand the Series section. This applies settings to your core data series.
• Scroll down and check the box next to Trendline.

Instantly, a line will appear on your chart, cutting through your data points. This is your line of best fit. But right now, it's just a visual aid. Let’s get the equation.

3. Display the Trendline Equation

This is the final and most important step to unlock the predictive power of your chart.

• In that same Series section, under the 'Trendline' checkbox, find the dropdown menu for Label. By default, it says 'None'.
• Click on the dropdown and select Use Equation.

The equation will immediately appear as a label right on your chart, likely near the trendline itself. For our sample data, it might look something like 0.17x + 10.45 (Your numbers may vary slightly based on the exact fit). Now your chart is not just showing you data, it's telling you the formula that governs it.

Bonus Tip: Show R-Squared to Measure Accuracy

But how much can you trust this new equation? Is the relationship between your ad spend and leads strong, or is it weak? That's where R-squared comes in.

Also located in the Series menu in the chart editor, check the box for Show R². R-Squared is a statistical measure called the "coefficient of determination." But you don't need to be a statistician to understand it.

• It's a value between 0 and 1.
• A value closer to 1 (like 0.95) indicates a very strong relationship. This means your model is a great fit, and predictions based on the equation will be reliable.
• A value closer to 0 (like 0.20) indicates a weak relationship. There's likely little correlation between your variables, and predictions will be unreliable.

By enabling R-squared, you're adding a confidence score to your analysis, instantly telling you and your team how much weight to give to any forecasts you make.

How to Make Predictions with Your New Equation

Now for the fun part: using the equation to make a forecast. Let's take our equation from the previous step: Leads = 0.17 * Ad Spend + 10.45.

The question we want to answer is: "If we plan to spend $500 on ads next week, how many leads can we expect to generate?"

The 'Ad Spend' is our value ('x'). We simply substitute it into the equation:

Leads = 0.17 * 500 + 10.45

Leads = 85 + 10.45

Leads = 95.45

Based on our historical data, the model predicts we can expect about 95 leads if we spend $500.

You can even build a mini-calculator for this right in your Google Sheet. In an empty cell, you can create a simple model:

• In cell E1, type "Enter Ad Spend:"
• In cell E2, you will enter your value (e.g., 500)
• In cell E3, type "Predicted leads generated"
• In cell F3, type the formula replacing "x" with a reference to the cell:

=0.17*E2+10.45

Now, any time you input a future ad spend number into cell E2, cell F3 will automatically update with the projected number of new customers. You’ve moved beyond simple reporting and into predictive analysis!

Customizing Your Trendline's Type

While the standard Linear trendline is the most common, Google Sheets offers others for different kinds of data patterns:

• Exponential: Use when your 'y' values increase or decrease at an accelerating rate. Think population growth or radioactive decay.
• Polynomial: Use for data with several peaks and valleys, showing a more complex, fluctuating trend.
• Logarithmic: Ideal when the values start to sharply increase or decrease before beginning to level out.
• Moving Average: Best for smoothing out highly variable and volatile data and provides a clearer long-term trend.

You can select these under the 'Type' dropdown in the 'Series' editing pane to see which fits your data best. Typically, the one with the highest R-squared value is the best mathematical fit.

Final Thoughts

Adding a trendline equation and R-squared value to your Google Sheets chart transforms a simple visualization into an insightful analytical tool. It allows you to move beyond describing what happened in the past and start making educated predictions about what's likely to happen next, empowering you to make smarter, data-driven decisions for your business.

All this analysis in Google Sheets is powerful, but it often involves manual steps across multiple platforms just to get the data into the right format. At Graphed we automate the entire data pipeline. Instead of setting up calculations and building charts by hand, you can just connect your sources and ask questions in plain English like, "What's the relationship between our Facebook ad spend and Shopify sales?". We instantly build an interactive, real-time dashboard that not only shows you the trend but explains what it means, giving you back time to focus on strategy instead of spreadsheets.