Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Set Intercept Trendline in Excel (with Easy Steps)

Excel charts are a fantastic tool for displaying data. Excel charts can easily represent complex data and provide users with useful information. The majority of the data may be shown using one of the various chart templates available in Excel charts. This article will show how to set the intercept Excel trendline by adding trendlines in Excel charts.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Step-by-Step Procedures to Set Intercept Trendline in Excel

This article will demonstrate how to set an intercept Excel trendline using the INTERCEPT function. Firstly, we will create a scatter chart, then we will add a trendline to find the Y-intercept and the Y vs. X intercept as well. Finally, we will set the value for the intercept point.


Step 1: Making Data Set with Proper Parameters

Here, we will create our data set, including the X and Y variables in the following step.

  • In order to create a chart, we will first create a data set, and then add a trendline.

Making Data Set with Proper Parameters to Set Intercept Excel Trendline


Step 2: Applying INTERCEPT Function

In this section, we will apply the INTERCEPT function to find the Y-intercept from our data set.

Objective:

Based on known x and y values, the INTERCEPT function in Excel determines the location where a regression line will intersect the y-axis.

Syntax:

=INTERCEPT(known_y's, known_x's)

Arguments Explanation:

Argument Required/Optional Explanation
Known_y’s Required The dependent set of observations or data.
Known_x’s Required The independent set of observations or data.

Versions

  • The INTERCEPT function is available from 2003 or later versions. Here, we will use Microsoft 365 version to accomplish our task.

  • Firstly, choose the E5 cell.
  • Secondly, write down the following formula here.
=INTERCEPT(C5:C12,B5:B12)
  • Thirdly, press Enter.

Applying INTERCEPT Function in Excel to Set Trendline

  • Finally, you will get the result in the below image.

Read More: How to Find Y Intercept in Excel (3 Effective Methods)


Step 3: Using Charts Group to Set Trendline

Excel’s charts group provides a range of chart types. In particular, many charting tools enable users to visualize complex data in straightforward, graphical forms. Here, we will add a trendline on the basis of the Y-intercept according to our data set after creating a scatter chart.

  • Firstly, go to the Insert tab after selecting the data range from the given data set.
  • Secondly, choose the Insert Scatter or Bubble Chart from the Charts group.
  • Thirdly, select the Scatter option.

Using Charts Group to Set Intercept Excel Trendline

  • As a result, you will see the Scatter chart in the below image.
  • Then, select the Trendline option.

  • So, you will see the given result after selecting the Trendline option.
  • After that, click on the Chart Elements option.
  • Then, go to More Options… from the Trendline option.

  • If you want to show the equation on the graph, select the Display Equation on Chart option.


Step 4: Showing Result for Intercept Value of Y-Axis

Here, you can see the final intercept trendline graph in Excel we have created by finding the Y-intercept from our data set based on X and Y variables.

  • Therefore, you can take a look at the given output here.

Showing Result for Intercept Value of Y-Axis to Set Excel Intercept Trendline


Step 5: Utilizing Set Intercept Option for Creating New Trendline

Following the finest linear regression that was projected across the known x-values and known y-values, the intercept point was calculated. When the independent variable is 0, and you want to know the value of the dependent variable, use the INTERCEPT function (zero).

  • Here, we want to show the zero intercept on the graph, select the Set Intercept option.

Utilizing Set Intercept Option for Creating New Trendline

  • Finally, you will get the following result of the Y Vs X with the intercept y value of zero.

Read More: How to Show Intersection Point in Excel Graph (3 Effective Ways)


Conclusion

In this article, we’ve covered step-by-step procedures to set an intercept Excel trendline. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, ExcelDemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles

Bishawajit Chakraborty

Bishawajit Chakraborty

I'm Bishawajit Chakraborty. Hello. I graduated from Rajshahi University of Engineering & Technology (RUET) with a degree in Mechanical Engineering. I'm working with ExcelDemy as a Content Developer for Excel & VBA. You can visit our website, Exceldemy if you'd like to read my published articles on MS Excel and VBA.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo