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.
Step 2: Applying INTERCEPT Function
In this section, we will apply the INTERCEPT function to find the Y-intercept from our data set.
Based on known x and y values, the INTERCEPT function in Excel determines the location where a regression line will intersect the y-axis.
|The dependent set of observations or data.
|The independent set of observations or data.
- 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.
- Thirdly, press Enter.
- 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.
- 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.
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.
- 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.
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.