How to Display Equation on Chart in Google Sheets

Showing the equation for a trendline on your Google Sheets chart is more than just a visual flourish - it transforms a simple graph into a predictive tool. This simple step unlocks the mathematical relationship behind your data, allowing you to make forecasts and gain deeper insights. This article gives you the step-by-step process for adding a trendline equation to your chart in Google Sheets and explains how to interpret what it tells you.

Why Display an Equation on Your Chart?

While a trendline gives you a nice visual summary of your data's direction, the equation is where the real power lies. It's the engine behind the trendline, quantifying the relationship between your X and Y variables. Displaying it directly on the chart makes your analysis more transparent, actionable, and professional.

Here are the three main benefits:

• Forecasting and Prediction: The equation, typically something like y = mx + b for a straight line, allows you to predict future outcomes. You can plug in a new X value (like 'next month's ad spend' or 'a future date') to calculate an estimated Y value ('projected sales' or 'expected website traffic').
• Understanding the Relationship: The equation clearly defines the link between your two variables. The slope (the 'm' value) tells you how much the Y value changes for every one-unit increase in the X value. This helps you answer questions like, "For every extra dollar we spend on marketing, how much additional revenue do we generate?" The intercept (the 'b' value) tells you the baseline value of Y when X is zero.
• Measuring How Well the Line Fits (R²): Alongside the equation, Google Sheets lets you display the R-squared (R²) value. In simple terms, R² tells you how much of the change in your Y variable can be explained by the change in your X variable. An R² of 0.85, for example, means that 85% of the variation in Y is accounted for by the trendline. A higher R² value (closer to 1) means you can have more confidence in your trendline and its predictions.

How to Add a Trendline and Its Equation in Google Sheets

Let's walk through the process from start to finish. We’ll use a simple dataset of 'Monthly Ad Spend' (our X-axis variable) and 'Website Visitors' (our Y-axis variable) to illustrate.

Step 1: Set Up Your Data

First, make sure your data is structured with your independent variable (X value) in one column and your dependent variable (Y value) in the column next to it. For our example, 'Ad Spend' influences 'Website Visitors,' so Ad Spend is our X and Visitors is our Y.

Your data should look something like this:

Step 2: Create a Scatter Chart

A scatter chart is the ideal format for visualizing the relationship between two numeric variables and is required for adding a linear trendline.

1. Highlight the two columns containing your data (in our case, B2:C7).
2. Navigate to Insert > Chart from the top menu.
3. Google Sheets will often default to a different chart type. In the Chart editor pane that appears on the right, go to the 'Setup' tab and under 'Chart type', select Scatter chart.

Step 3: Add and Customize Your Trendline

Now that you have your chart, it’s time to add the trendline and the equation. Your chart should still be selected, showing the Chart editor pane on the right. If not, simply double-click on your chart to re-open it.

1. Click on the Customize tab in the Chart editor.
2. Expand the Series section by clicking on it.
3. Scroll down and check the box next to Trendline. A line will immediately appear on your chart.

Step 4: Display the Equation and R-squared Value

This is the final and most important step. With the trendline added, several new options will appear right below the checkbox.

1. Find the dropdown menu labeled Label. By default, it's set to 'None'.
2. Click on the dropdown and select Use Equation.
3. You will immediately see the equation appear on your chart, overlaying the data series.
4. To measure the fit of your trendline, also check the box for Show R². This value will appear alongside the equation on your chart.

That's it! Your chart now clearly visualizes not only the data and the trend but also the mathematical formula that describes it.

Choosing the Right Trendline Type

While a straight line (Linear) is the most common trendline, it's not always the best fit for your data. Google Sheets offers several types of trendlines to model different kinds of relationships.

Under the 'Series' > 'Trendline' menu, you can select the 'Type' that best matches your data pattern. Always pick the trendline type that results in the highest R² value, as this shows the most accurate fit.

Linear

The standard y = mx + b format. Use this when the relationship between your variables is consistent - as X increases, Y increases or decreases at a more or less steady rate. Perfect for things like spend vs. returns when the ROI is stable.

Exponential

Use an exponential trendline when your Y values grow or shrink at an accelerating rate. It creates a curve that starts slow and then gets dramatically steeper (or vice-versa). Think of data like viral content views, compound interest, or population growth.

Polynomial

A polynomial trendline is useful for data with rises and falls, creating a curve with one or more bends. For example, it could model the effectiveness of a fertilizer–too little has no effect, an optimal amount boosts growth, and too much harms the plant, creating an inverted "U" shape.

Logarithmic

This is best for data series that increase or decrease quickly at first and then start to level off. Examples include learning curves (where you learn fast initially but progress slows over time) or a product's market saturation.

Putting It All Together: A Sales Forecasting Example

Let's make this ultra-practical. Imagine you run promotions with different discount percentages and you want to predict how many units you will sell based on the discount you offer.

1. The Data

Here’s your data from past promotions:

2. The Chart, Trendline, and Equation

Following the steps above, you create a scatter chart. You add a trendline and find that a linear trendline gives you a high R² of 0.98. The equation displayed on your chart is:

y = 23.5 * x - 15 (where Y is 'Units Sold' and X is 'Discount Offered')

3. Interpret the Results

Let’s break down that formula in plain English:

• The slope (23.5) means that for every 1% increase in the discount, we can expect to sell approximately 23.5 additional units.
• The intercept (-15) suggests that with a 0% discount, we'd sell -15 units, which doesn't make logical sense in this context. This is a great reminder that trendlines are for modeling within your data's range and should not be over-extrapolated to unrealistic scenarios. We obviously wouldn't sell a negative number of units. The model's strength is in predicting inside, or slightly outside our tested range (say from 5 - 30% discount).
• The R² of 0.98 is very high, telling us that 98% of the variation in units sold can be explained by the discount offered. This gives us strong confidence in our model.

4. Make a Prediction

Next week, you are planning a promotion with a 12% discount. How many units can you expect to sell?

Simply plug 12 into the equation for 'x':

Predicted Units Sold = (23.5 * 12) - 15

Predicted Units Sold = 282 - 15 = 267

Based on your historical data, you can confidently forecast sales of approximately 267 units with a 12% discount.

Final Thoughts

Displaying the equation on your Google Sheets chart moves you from simply observing your data to actively interacting with it. It makes your reporting more robust, allowing you to quantify relationships, validate your assumptions with the R-squared value, and make data-informed forecasts about the future.

While creating these analyses one by one in Google Sheets is powerful, we know that the real challenge is bringing data together from all your different platforms. At Graphed, we automate this process. Rather than exporting CSVs from your marketing and sales tools, you can just connect your data sources once. Then ask questions like, "Create a dashboard showing our website sessions from Google Analytics vs our ad spend from Facebook for the past year, and show me the trendline," to instantly get real-time, shareable dashboards. Our goal is to handle the tedious work of wrangling data and charts so you can focus on making decisions.