How to Add Line of Best Fit to Excel Graph

Adding a line of best fit to a chart in Excel is one of the quickest ways to see the underlying trend in your data. More than just a visual guide, this simple line - also called a trendline - helps you understand the relationship between two variables, like your ad spend and revenue, or website traffic and user sign-ups. This tutorial will walk you through exactly how to create a scatter plot in Excel and add a line of best fit, interpret its meaning, and use it to make smarter forecasts.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What Exactly Is a Line of Best Fit?

Imagine you have a bunch of data points scattered on a graph. A line of best fit is a single straight line drawn through the middle of those points that best represents their overall relationship. It attempts to get as close as possible to all the data points simultaneously, summarizing the general trend in a way that’s easy to understand.

Think of it like this: if you plotted the number of hours you studied against your exam scores for different subjects, the points might be all over the place. A line of best fit would slice through that data to show you a simple trend: in general, the more hours you study, the higher your score.

For businesses, this is incredibly powerful. You can use it to answer questions like:

• Does increasing our paid ad budget actually lead to more sales?
• Is there a relationship between the number of sales calls made and deals closed?
• As we gain more social media followers, does our website traffic increase?

By visualizing this relationship, you move from guesswork to data-backed analysis.

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 for Plotting

Before you can create a chart, you need to make sure your data is structured properly in Excel. A line of best fit requires two variables:

• Independent Variable (X-axis): This is the variable you control or that you believe influences the other. For example, Ad Spend, Hours Studied, or Sales Calls Made. This data should be in your first column.
• Dependent Variable (Y-axis): This is the variable you are measuring - the outcome that you think depends on the independent variable. For example, Revenue, Exam Score, or Deals Closed. This data should be in the second column, right next to the first.

Let’s use a classic marketing example. We want to see if our daily ad spend has a clear impact on the daily revenue we generate. Your spreadsheet should look clean and simple, like this:

Pro Tip: Ensure there are no empty cells in your data range. While Excel can sometimes handle this, it can also lead to strange gaps or errors in your chart. It's best practice to have a complete dataset.

Step 2: Create a Scatter Plot

Once your data is organized, the next step is to create a scatter plot (also known as an XY scatter graph). This type of chart is ideal for showing relationships between two sets of numerical data because it plots each pair of values as a single point.

1. Select Your Data: Click and drag your cursor to highlight both columns of data, including the headers ("Ad Spend" and "Revenue").
2. Go to the Insert Tab: At the top of the Excel ribbon, click on Insert.
3. Find the Charts Group: Look for the "Charts" section. Click on the icon that looks like a plot with scattered dots. This is the "Insert Scatter (X, Y) or Bubble Chart" option.
4. Choose the Basic Scatter Plot: A dropdown menu will appear. Select the first option, which is simply titled "Scatter."

Excel will instantly generate a scatter plot chart on your worksheet. Each point on the chart represents a single day, showing the relationship between how much was spent on ads and how much revenue was earned.

Step 3: Add the Line of Best Fit (Trendline)

Now for the main event. With your scatter plot created, adding the trendline takes just a few clicks. There are a couple of ways to do this, but the easiest is directly from the chart itself.

Method 1: Right-Click on a Data Point

1. Hover your cursor over any of the blue data points on your scatter plot and right-click.
2. From the context menu that appears, select Add Trendline...

A dotted trendline will immediately appear on your chart, and the "Format Trendline" pane will open on the right side of your screen. That’s it! You've successfully added a line of best fit.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 2: Using the Chart Elements Button

1. Click anywhere on your chart to select it.
2. Look for a green plus (+) icon that appears in the top-right corner of the chart. This is the "Chart Elements" button. Click on it.
3. A list of elements will appear. Hover over Trendline. This will show you a preview on the chart.
4. Click the checkbox next to "Trendline" to add it permanently.

By default, Excel adds a Linear trendline, which is the most common type and works best for data that shows a straight-line relationship. Now, let's make this line even more useful.

Step 4: Interpreting Your Trendline with the Equation and R-Squared Value

A visual line is great, but the real analytical power comes from two additional pieces of information: the equation of the line and the R-squared value.

In the "Format Trendline" pane (if you closed it, just right-click the trendline and select "Format Trendline"), scroll down and check these two boxes:

• Display Equation on chart
• Display R-squared value on chart

A small text box will appear on your chart containing these values. Let's break down what they mean using our example.

Understanding the Equation

The equation will appear in the format y = mx + b. For our data, it might look something like this:

y = 2.8992x + 105.74

• y: Your dependent variable (Revenue).
• x: Your independent variable (Ad Spend).
• 2.8992 (the slope, or 'm'): This is the most important number. It tells you that for every one-unit increase in x, y increases by this amount. In our context, this means for every additional $1 we spend on ads, we can expect revenue to increase by approximately $2.90.
• 105.74 (the intercept, or 'b'): This is the predicted value of y when x is zero. In theory, this means if we spent $0 on ads, we would still generate about $106 in revenue. This is your baseline.

Understanding the R-squared Value

The R-squared value (R²) tells you how well your line fits the data. It's a value between 0 and 1 (or 0% and 100%).

An R-squared value of R² = 0.897 means that 89.7% of the variation in our revenue (the y-variable) can be explained by the ad spend (the x-variable). The closer to 1, the stronger the relationship and the more reliable your trendline is for making predictions.

Generally, an R² above 0.70 is considered a strong correlation, while a value below 0.30 is weak. Our example value of nearly 0.90 indicates a very strong, predictable relationship between ad spend and revenue.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Exploring Other Types of Trendlines

While Linear is the default, Excel offers other trendline models. These can be useful when your data doesn't follow a straight line.

• Exponential: Use this when your data values rise or fall at increasingly higher rates. Think of it like a "J-curve," perfect for modeling things like viral growth.
• Logarithmic: This is the opposite. It’s best for data that rises or falls quickly at first and then starts to level off. A good example is diminishing returns - the first $1,000 in ad spend might bring a huge return, but the next $1,000 brings slightly less.
• Polynomial: Use this for data that fluctuates with peaks and valleys. A polynomial trendline (especially "Order: 2") can fit a simple curve, like modeling sales data that rises mid-year and falls at the end of the year.

You can experiment with these in the "Format Trendline" pane. Choose the model that gives you the highest R-squared value, as that indicates the best fit for your specific data.

Final Thoughts

Adding a line of best fit in Excel transforms a simple chart into a powerful analysis tool. By visualizing the trend and displaying both the linear equation and R-squared value, you can quickly quantify relationships in your data, make informed predictions, and better understand the drivers of your business performance.

Creating these charts manually in Excel works well, but it's often part of a much larger, time-consuming reporting process. We built Graphed to eliminate that friction. Instead of exporting CSVs and building a chart click-by-click, you can connect your data sources (like Google Analytics, Shopify, Facebook Ads) and simply ask for what you need in plain English - like "Show me a scatter plot of TikTok ad spend vs. Shopify revenue with a trendline." We create the real-time, interactive dashboard for you in seconds, freeing you up to act on your insights instead of just finding them.