The y-intercept of a graph represents the point where a line crosses the y-axis when x is equal to zero.
To find the y-intercept of a line in Excel, we can use the INTERCEPT function.
This functions uses the following syntax:
INTERCEPT(known_y’s, known_x’s)
where:
- known_y’s: The range of y-values
- known_x’s: The range of x-values
The following example shows how to use this function in practice to calculate the y-intercept of a graph in Excel.
Step 1: Create the Data
First, let’s create a dataset with two variables in Excel:
Step 2: Calculate Y-Intercept Value
Next, let’s type the following formula into cell E1 to calculate the y-intercept for this dataset:
=INTERCEPT(B2:B21, A2:A21)
The following screenshot shows how to use this formula in practice:
From the output we can see that the y-intercept is 12.46176.
Step 3: Visualize the Y-Intercept Value
To visualize the y-intercept value, we can create a scatterplot and add a trendline.
To do so, highlight the values in the range A2:B21, then click the Insert tab, then click Insert Scatter (X, Y) in the Charts group and click the first option to create a scatterplot:
The following scatterplot will appear:
Next, click the green plus sign in the top right corner of the plot, then click the arrow next to Trendline and click More Options:
In the Format Trendline panel that appears on the right side of the screen, click the Linear trendline option, then check the box next to Display Equation on chart:
The linear trendline and the equation for the linear trendline will be displayed on the chart:
From the output we can see the formula for the linear trendline:
y = 0.917x + 12.462
This means that when x is equal to zero, the estimated value of y (i.e. the y-intercept) is 12.462.
If we imagine that the linear trendline extends all the way to the left until it reaches the y-axis, it does indeed look like the line would cross the y-axis at a value of y = 12.462.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Create a Scatterplot Matrix in Excel
How to Add Labels to Scatterplot Points in Excel
How to Add a Horizontal Line to a Scatterplot in Excel
How to Create a Scatterplot with Multiple Series in Excel