How to Get an Equation from a Graph in Excel

Turning a visual data trend into a practical mathematical formula is one of Excel’s most powerful, and often overlooked, features. You might have a scatter plot showing the relationship between ad spend and sales, but what if you want to predict your sales for next month's ad budget? This is where finding the underlying equation becomes essential. This guide will walk you through exactly how to add a trendline to your Excel graph and pull out its specific equation for analysis and forecasting.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

First, Why Would You Need an Equation from a Graph?

Before jumping into the "how," it's helpful to understand the "why." A chart gives you a visual story, but the equation behind it gives you predictive power. Here are a few common scenarios where this is incredibly useful:

• Forecasting: The most obvious use. If you know the formula linking historical ad spend to revenue, you can plug in a future ad spend budget to estimate future revenue. This moves you from guessing to data-informed planning.
• Identifying Relationships: The equation quantifies the relationship between your two variables. The formula y = 2.5x + 10 tells you that for every single unit increase in x, y increases by 2.5 units.
• Resource Planning: If your graph shows the relationship between website traffic and the number of customer support agents needed, the equation helps you predict staffing needs as you plan to scale traffic.
• Setting Performance Goals: By understanding the formula that connects activity (e.g., sales calls made) to results (e.g., deals closed), you can set realistic, data-backed goals for your team.

In short, the equation transforms your chart from a static report of the past into a dynamic tool for predicting the future.

Step 1: Get Your Data Ready

Your analysis is only as good as your data setup. For Excel to generate an equation, it needs to understand which variable influences the other. You need two columns:

• Independent Variable (X-axis): This is the variable you control or the one that happens first. Think "cause." Examples include time, advertising dollars, or temperature.
• Dependent Variable (Y-axis): This is the variable you are measuring, it's the outcome you hope is influenced by the independent variable. Think "effect." Examples include sales revenue, sign-ups, or ice cream sales.

Let’s use a simple example. Imagine we're a SaaS company tracking the number of paying subscribers over the first eight months since our launch. Our data would look like this:

Having your data organized like this is the foundation for everything that follows.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 2: Create a Scatter Plot

To see the relationship visually, you first need a graph. A scatter plot (or XY chart) is perfect for this, as it shows each data pair as a single point.

1. Highlight both columns of your data, including the headers.
2. Go to the Insert tab on Excel's ribbon.
3. In the Charts section, click on the icon that looks like a plot with dots. This is the Insert Scatter (X, Y) or Bubble Chart option.
4. Select the first option, the basic Scatter chart.

Excel will instantly generate a chart. You should see a series of dots that trend upward and to the right, showing a positive correlation between the months and the number of subscribers. Now, let’s find the line that best describes this trend.

Step 3: Add a Trendline and Display Its Equation

This is where the magic happens. We'll ask Excel to draw a line of best fit through our data points - the trendline - and then give us the mathematical equation for that line.

1. Click anywhere on your newly created chart to select it.
2. Right-click on any of the blue data points within the chart. A context menu will appear.
3. From this menu, select Add Trendline...

This will cause two things to happen: a dotted line will appear on your chart, and the Format Trendline pane will open on the right side of your screen. This pane is your control center.

Now, to get the equation:

1. Scroll down to the bottom of the Format Trendline options.
2. Check the box that says "Display Equation on chart".
3. While you're here, it’s highly recommended to also check the box for "Display R-squared value on chart". We’ll discuss what this means in a moment.

As soon as you check the box, a small text box will appear on your chart with an equation like y = 69.107x + 65.643 and an R-squared value. You can click and drag this text box to move it wherever you like on the chart for better visibility.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 4: Choose the Right Trendline Type

By default, Excel adds a Linear trendline, which is a straight line. But sometimes your data doesn't follow a straight path. The Format Trendline pane allows you to choose the type of line that best fits your data. Picking the right one is crucial for an accurate equation.

Common Trendline Options

• Linear: A classic straight line. Use this when the rate of change is constant. The equation format is y = mx + b. This is great for an initial analysis because it's simple to understand.
• Exponential: Use for data that rises or falls at an increasingly rapid rate. The points form a sharp curve that gets steeper. Think of viral growth or compound interest. The equation contains an exponential term, like y = a*e^(bx).
• Logarithmic: The opposite of exponential. It's best for data that grows quickly at first and then starts to level off or slow down. A great example is a learning curve, where you make fast progress initially but then improvements become smaller over time.
• Polynomial: This is a curved line that can have "hills" and "valleys," making it very flexible. You can choose the "Order" of the polynomial. An Order 2 polynomial has one bend (like a U-shape), an Order 3 has up to two bends, and so on. Be careful with this one! Too high of an order can lead to "overfitting," creating a wildly complex equation that fits your past data perfectly but does a terrible job of predicting the future.
• Power: Use this for data that represents a consistent rate of increase, often seen in scientific measurements - for example, comparing acceleration and force.

Which One Should You Choose?

Try clicking on different trendline types in the pane. As you do, watch how both the line itself and its corresponding equation and R-squared value change. Your goal is to find the simplest model that makes logical sense and has the highest R-squared value.

Step 5: Understand the R-Squared Value

So, what is that mysterious "R²" value? R-squared, also known as the Coefficient of Determination, is your quality score. It's a number between 0 and 1 that tells you how well the trendline's equation actually fits your data points.

• An R² of 1 means the equation perfectly fits your data - every single one of your points falls directly on the line.
• An R² of 0 means there is no linear relationship whatsoever. The line does not explain the data at all.

In our subscriber example, we got an R² value of 0.9859. This is very good! It means that nearly 98.6% of the variation in our subscriber numbers can be explained by the month. It gives us high confidence that this equation is a reliable model for our data. When choosing between trendline types, look for the one that has the highest R² value. For our data, the linear model fits extremely well.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 6: Use Your New Equation for Forecasting

Now that you have your equation (y = 69.107x + 65.643), you can use it. The "y" represents Subscribers, and the "x" represents the Month.

Want to predict how many subscribers you might have in Month 12?

Simply replace "x" with 12 in your equation:

Subscribers = (69.107 * 12) + 65.643

This calculates to approximately 894.9. So, based on our trend, we can forecast around 895 subscribers by the end of our first year.

You can even do this directly back in your Excel sheet. In a new cell, type:

=(69.107 * A10) + 65.643

...where A10 is a cell containing the future month (like 12) you want to predict.

Final Thoughts

Pulling an equation from an Excel graph is a technique that bridges the gap between seeing your data and using it to make calculated predictions. By creating a scatter plot, adding a trendline, displaying the equation, and confirming its fit with the R-squared value, you transform a simple dataset into a predictive model.

This process in Excel is invaluable for single datasets, but we know the reality is often more complex. Your marketing and sales data lives across a dozen platforms, and manually exporting CSVs to perform this kind of analysis for every campaign is time-consuming. Using Graphed , we help you connect all those sources - from Google Analytics to Shopify to Salesforce - in one click. Then, instead of building charts manually, you can simply ask questions in plain English, and our AI builds real-time dashboards and reports for you, so you can spend less time wrangling data and more time acting on it.