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

Get FREE Advanced Excel Exercises with Solutions!

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.


Set Intercept Trendline in Excel: Step-by-Step Procedure

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


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.


Download Practice Workbook

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


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. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles


<< Go Back to Excel INTERCEPT Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bishawajit Chakraborty
Bishawajit Chakraborty

Bishawajit Chakraborty, a Rajshahi University of Engineering & Technology graduate with a B.Sc. in Mechanical Engineering, has been associated with ExcelDemy since 2022. Presently, he is a content developer, specializing in Excel Power Query, Data Analysis and VBA. It is worth mentioning that he has authored more than 90 articles on VBA content development. His profound interest lies in the fields of data analytics and data science. He possesses expertise in VBA, Power BI, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo