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

Get FREE Advanced Excel Exercises with Solutions!

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


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.


How to Find X Intercept for Linear Data in Excel

We will consider a linear equation (y = 3x – 7). To be specific, it is a straight-line equation. Here, we will find a value of X when y = 0.

First, choose cell C12 >> apply the following formula >> hit Enter.

=INTERCEPT(B6:B10,C6:C10)

Now, plot a Scatter with Smooth Lines and Markers chart to get a better understanding.

Finding X Intercept for Linear Data

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.

How to Find x-Intercept in Excel: 5 Suitable Methods

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.

🔀 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%.

2. Using FORECAST.LINEAR Function to Find X-Intercept

Here, we will use FORECAST.LINEAR to find X Intercept for the above dataset.

🔀 Steps:

  • First, choose cell D19 >> apply the following formula >> hit OK.
=FORECAST.LINEAR(D18,C5:C16,D5:D16)

Use of FORECAST.LINEAR Function to find X Intercept

Notes

When calculating X Intercept using the FORECAST.LINEAR function, we must switch between the known_ys and know_xs values. So, use People Using Masks (X) as known_ys and Covid Cases (Y) as know_xs.


3. 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, 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


4. 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


5. Using Data Analysis ToolPak

The fourth option is using the Data Analysis ToolPak of 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


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.

Download Practice Workbook

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


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. Thank you!


<< 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.
Masum Mahdy
Masum Mahdy

Abu Talha Masum Mahdy (Assistant Project Manager, ExcelDemy) has been working with the ExcelDemy family for over 2 years. He wrote 30+ articles and reviewed more than 1300 others for ExcelDemy. Currently, he is responsible for reviewing articles from 2 writing teams of ExcelDemy. He ensures correctness and fluency in his team write-ups. Mahdy holds a BSc in Naval Architecture and Marine Engineering from BUET. His interests are Project Management, Content Writing, SEO, Digital Marketing, Data Analytics and... Read Full Bio

2 Comments
  1. Hey, these 4 methods to determine an x-intercept for a data set are wrong. You have to determine the x incept for a given line by solving the equation of a line for x and setting y=0. None of these methods provide and even close to accurate value for a data sets linear regression line’s x-intercept. READER BEWARE.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Aug 10, 2023 at 12:41 PM

      Hello Dr Timber

      I went through this article and did not find any technical issues. In my view, you should reconsider what you have said.

      This article is about finding an X intercept. To demonstrate this point, this context considers a practical dataset where the percentage of people still using masks is calculated when the number of Covid Cases becomes Zero. I think you expected the calculation to find X Intercept for Y = 0. That’s what is calculated in those methods. When the writer introduced us to the dataset, it was explicitly mentioned.

      Anyway, I am introducing another method that may overcome your confusion. I have calculated the percentage of people who still use masks when the number of Covid Cases becomes Zero. I am using FORECAST.LINEAR Function to predict the percentage. And I get the same result shown in this article.
      Excel Formula:

      =FORECAST.LINEAR(D18,C5:C16,D5:D16)

      Finding X Intercept with Article Dataset

      The data considered in this article is not Linear. That’s why you get confused. However, these methods must be able to find an X intercept for any Linear data.

      X Intercept with Linear Data

      Stay safe. And good luck!

      Regards
      Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo