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

How to Find x-Intercept in Excel (4 Suitable Methods)

In this tutorial, I will discuss 4 suitable methods to find the x-intercept in Excel.


Download Practice Workbook

Download the following workbook for your practice from the following link.


What Is x-Intercept?

Representation of x-intercept in Excel graph

If a straight line intersects X-axis at (2,0), the x-intercept will be 2. So, you can say that the x-intercept is the x-ordinate of a point on a straight line, where the line intersects the X-axis.


4 Methods to Find x-Intercept in Excel

Let’s consider the following data consists of monthly Covid cases (set as Y-values) and monthly use of masks (set as X-values).

We have to know which percentage of people are still using masks when the number of covid cases becomes zero. Excel has many functions and features to find the x-intercept. Of them, we will see 4 in the next sections.


1. Using INTERCEPT Function to Find x-Intercept

If you just need to know the x-intercept, then using the INTERCEPT function of Excel is the fastest method.

Important Notes
  • The INTERCEPT function finds the point at which a line intersects the y-axis. It uses known x-values and known y-values.

INTERCEPT function syntax

  • Note that, the INTERCEPT function considers the intersection at the y-axis which means it calculates the y-intercept.
  • So, when you calculate the x-intercept with the INTERCEPT function, your y-values will be INTERCEPT’s x values and your x-values will be INTERCEPT’s y-values.

🔀 Steps:

  • Use the following formula in Cell C18 to find the x-intercept of this Covid-Mask data.
=INTERCEPT(C5:C16,D5:D16)

Using INTERCEPT Function to Find x-Intercept

  • Here, your x-values=C5:C16y-values in the formula.
  • And your y-values=D5:D16x-values in the formula.
  • So, we get the x-intercept = 90.16% (after applying the percentage format of Excel).

🔎 Interpretation of x-Intercept:

  • x-Intercept means the y value is set to zero. In our case, the y-value is covid cases.
  • So, this result means: when the use of masks is 90.16%, the covid cases will be zero.
  • Or, when the covid cases tend to zero, the use of masks among people is still 90.16%.

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


2. Using the Trendline Equation Feature of Excel Graph

You can also find the x-intercept by using the Trendline Equation feature of the Excel graph created from the dataset you have. Follow the steps below to do this.

🔀 Steps:

  • First off, go to the Insert tab and click on the Scatter icon and select the first suggested icon.

approaching to create a blank graph background in Excel

  • A blank graph canvas will appear.
  • Now, click on the Chart Area canvas and then right-click on your mouse. Then click on Select Data… option.

  • From the Select Data Source window, press the Add button.

  • The Edit Series pop-up will appear.
  • From this window, give a suitable name for the series.

selecting X and Y values of the series

  • Then choose X values. Note that, like the previous method, Excel’s X values are equivalent to our Y values here.

select y values from x coordinates in Excel to get the x intercept

  • Similarly, choose the Y values.
  • Then, press OK.

  • Now we will be back to the Select Data Source window again.
  • Notice that the Calculate x-Intercept (the name we gave) series appears and is selected automatically.
  • Finally, press the OK button.

  • You are done with making the graph, a couple more steps to get the result now.
  • Click on the Chart Elements button (a + sign you see in the image below).
  • Then unmark the Gridlines for a better view.
  • After that, select the Trendline option and click on the angle icon () just beside it.
  • Select More Options.

  • A window named Format Trendline will appear.
  • From this window, click the 3rd icon (when hovering your mouse, you will see its name is Trendline Options).
  • Then select the Linear radio button.
  • Lastly, mark the ‘Display Equation on the chart’ checkbox.

  • Get back to the graph.
  • You will see that an equation appears in the chart area.
  • The equation is:
y=-0.0001x+0.9016
  • So, finally, we get the desired result, our x-intercept is 0.9016 (90.16%, when formatted as a percentage).

Using the Trendline Equation Feature of Excel Graph to find x-intercept

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


3. Using LINEST Function to Find x-Intercept

The LINEST function is another option to find the x-intercept in Excel. This function is a combination of SLOPE and INTERCEPT functions. It returns slope and intercepts as well with a single click.

🔀 Steps:

  • Here is the formula to use the LINEST function to get the x-intercept.
=LINEST(C5:C16,D5:D16)
  • Put this formula in Cell C19 and press ENTER.

Using LINEST Function to Find x-Intercept

  • And you will get an intercept in Cell C20 (and a slope in Cell C19).

Read More: How to Find Intercept of Two Lines in Excel (3 Suitable Ways)


4. Using Data Analysis ToolPak

The fourth option is using the Data Analysis ToolPak of Excel. To know more details about this tool, click on the following article: How to Use Data Analysis ToolPak in Excel.

🔀 Steps:

  • To enable the Data Analysis ToolPak, press ALT+F+T and get to the Excel Options window.
  • Then go to the Add-ins section and select Excel Add-ins from the Manage drop-down.
  • Then click on the Go button.

Going to Excel add-ins in Excel Options window

  • A new window will pop up named Add-ins.
  • Mark the Analysis ToolPak checkbox and press OK.

Enabling Analysis ToolPak to get x-intercept and other data

  • Now you will have this feature on your Excel ribbon.
  • Go to the Data tab and click on Data Analysis from the Analysis group.

  • From the Data Analysis options, select Regression and press OK.

  • The Regression window will pop up.
  • Select Input Y Range first, remember that you have to select your X-values here.
  • Similarly, select Input X Range.
  • Finally, press OK.

  • You will be back to the Regression window again.
  • Select where to place the Output Range (in our case, the location is Cell F4).
  • Then press OK.

Using Regression method of Analysis ToolPak to find x-intercept

  • The regression summary will be generated by ToolPak.
  • From this report, you will get the x-intercept along with other statistics.
  • In the following image, we have highlighted the x-intercept.

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


Quick Notes

  • You must remember that all through the article, we have considered the X values of our data as the known_ys (and vice versa for Y values) of the Excel function or graph tool or the Data Analysis tool.
  • Use the INTERCEPT and Trendline Equation methods if you just need to know the x-intercept quickly.

Concluding Words

So that’s it for today. I hope you have enjoyed this article and can easily find the x-intercept now. If you face any problems, then leave us a comment. And for more Excel-related articles, visit our blog. Thank you!


Related Articles

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo