How to Show Intersection Point in Excel Graph

To show the intersection point of two lines on an Excel graph, calculate the crossing point with a formula and plot it as a separate one-point series so it appears as a marker on the chart. For two straight lines, use SLOPE and INTERCEPT to find each line’s equation, then solve for where they are equal; for curved data, use Goal Seek or Solver to find the value where the two series match.

This guide covers both the quick visual method (trendlines on a chart) and the exact algebraic method, plus a worked break-even example and how to handle non-linear cases.

Step by Step Guide: How to Show Intersection Point in Excel Graph

Graphs or charts are an excellent way to represent data, but they can get complicated when there are too many data sets. It can be challenging to determine where two lines intersect on a graph. Luckily, Excel has a built-in tool that makes it easy to display the intersection point between two data sets on an Excel graph. Here is a step-by-step guide on how to show the intersection point in an Excel graph.

Step 1: Create Your Excel Graph

The first step to showing the intersection point is to create a graph in Excel. If you’re unsure how to do this, follow these steps:

1. Select the data you want to include in your graph
2. Click on the “Insert” tab on the Excel ribbon
3. Select the type of graph you want to create
4. The graph will now appear on your Excel sheet.

Step 2: Add a Trendline to the Graph

Next, you need to add a trendline to your graph. A trendline is a line that shows a pattern in your data. To add a trendline, follow these steps:

1. Click on the graph to select it
2. Click on the “Chart Elements” button that appears on the right-hand side of the graph
3. Select “Trendline” and then “More Trendline Options”
4. In the “Format Trendline” window that opens, check the “Display Equation on chart” box and click “Close”.

Step 3: Calculate the Intersection Point

Now that you have added the trendline to your graph, you can calculate the intersection point. To do this, follow these steps:

1. Click on any point on the trendline
2. Press “Ctrl+1” to open the “Format Data Point” window
3. Select “Fill” and then “No Fill”.
4. Under “Border”, select “Solid Line” and then choose a color that stands out from your data
5. Click “Close”

Step 4: Display the Intersection Point

You’re almost done! The final step is to display the intersection point on your graph. To do this, follow these steps:

1. Right-click on a point on the x-axis where the trendline crosses it (you can click on any of the points if the line is curved)
2. Select “Format Axis” from the menu
3. Select “Axis Options” and then “Horizontal axis crosses”
4. Under “Axis value”, select “Fixed” and then type in the y-value where the trendline crosses the x-axis
5. Click “Close”

Congratulations! You have successfully displayed the intersection point on your Excel graph. You can now easily analyze and interpret your data.

Why Show Intersection Points in Excel Graphs?

Before we conclude, let’s discuss why showing intersection points in Excel graphs is beneficial.

When analyzing data, it’s often valuable to know where two lines intersect since it can provide insight into the relationship between the two variables. It can also help answer questions like:

• At what point do these two factors affect one another?
• At what point does one factor become more important than the other?
• Are there any trends or patterns in when these factors intersect over time?

Tips for Analyzing Data with Intersection Points

Now that you know how to display intersection points in Excel graphs let’s take a look at some tips for analyzing data with intersection points:

• Label intersection points: Be sure to label your intersection points clearly on your graph to avoid any confusion when analyzing your data.
• Interpret data trends: Identify any trends and patterns in your intersection points over time to draw conclusions about your data.
• Conduct further analysis: Once you have identified potential relationships between your variables, conduct additional analysis to determine the significance of these relationships.

By following the step-by-step guide outlined in this article, you now know how to display intersection points in Excel graphs. Remember that intersection points offer valuable insights to help you better interpret your data, identify trends and patterns, and gain deeper insights into relationships between variables.

Plotting Two Series So the Crossing Is Visible

Before you can mark an intersection, both lines need to share the same horizontal axis. The most reliable chart type is an XY Scatter (with straight lines) because it treats the X values as real numbers rather than evenly spaced categories. A standard Line chart works too, but only when both series use identical X labels.

1. Lay your data out in three columns: the X values in column A, the first series in column B, and the second series in column C.
2. Select all three columns, then go to Insert → Charts → Scatter and pick Scatter with Straight Lines (or use a Line chart — see how to make a line graph in Excel and the broader Excel charts complete guide).
3. Add titles so the crossing is easy to read — see how to add axis titles and how to label axes in Excel.

For a deeper walkthrough of building the chart itself, see how to create a scatter plot in Excel or how to make a scatter plot in Excel.

Finding the Intersection Algebraically (Two Straight Lines)

The trendline method above is quick but only displays an approximate crossing. To find the exact intersection of two straight lines, solve for the X where both lines have the same Y.

Each straight line follows the equation y = mx + b, where m is the slope and b is the intercept. Excel gives you both directly with the SLOPE function and the INTERCEPT function. Suppose Series 1 is in B2:B11 and Series 2 is in C2:C11, both against X values in A2:A11:

Slope 1 (m1):     =SLOPE(B2:B11, A2:A11)
Intercept 1 (b1): =INTERCEPT(B2:B11, A2:A11)
Slope 2 (m2):     =SLOPE(C2:C11, A2:A11)
Intercept 2 (b2): =INTERCEPT(C2:C11, A2:A11)

Two lines cross where m1·x + b1 = m2·x + b2. Rearranging gives the X of the intersection:

X intersection = (b2 - b1) / (m1 - m2)
Y intersection = m1 * X_intersection + b1

In cells (with the slopes/intercepts in F2:F5 as listed above):

X:  =(F5 - F3) / (F2 - F4)
Y:  =F2 * <X cell> + F3

If m1 equals m2 the lines are parallel and never cross, so the denominator is zero — Excel returns #DIV/0!, which correctly tells you there is no intersection.

Interpolating When Your Data Isn’t a Straight Line

Real-world series rarely fit one straight line. A common approach is to find the exact row range where one series overtakes the other, then interpolate linearly between just those two rows. The sign of the difference between the two series flips at the crossing.

1. Add a helper column: =B2 - C2, filled down. The crossing sits between the row where this difference is positive and the next row where it turns negative (or vice versa).
2. Once you know the two bracketing rows — call the lower one row a and the upper one row b — interpolate. With X values x_a, x_b and series differences d_a = B_a − C_a and d_b = B_b − C_b:

X intersection = x_a + (x_b - x_a) * d_a / (d_a - d_b)

This is exact when the segment between the two rows is treated as straight, which matches what an XY Scatter “with straight lines” actually draws. Note d_a and d_b have opposite signs across the crossing, so d_a - d_b is never zero there. Plug the result back into either line’s segment to get the Y value.

Showing the Intersection as a Marker on the Chart

Once you have the X and Y of the intersection, add them as a brand-new one-point series so a single marker lands exactly on the crossing:

1. Put the calculated X in one cell and the Y in the cell beside it.
2. Right-click the chart → Select Data → Add. Set the X-value reference to your X cell and the Y-value reference to your Y cell.
3. Click the new point, press Ctrl+1, and under Marker choose a large, contrasting marker (and turn the connecting line off). Add a data label if you want the coordinates shown.

Because it is its own series, the marker stays pinned to the true intersection even if you edit the underlying data — far more reliable than nudging an axis crossing by hand.

Worked Example: Break-Even Point Chart

A classic use of an intersection is the break-even point, where total revenue equals total cost. Say each unit sells for $20, variable cost is $8 per unit, and fixed costs are $1,200.

Units (A)	Revenue =20*A (B)	Cost =1200+8*A (C)
0	0	1,200
50	1,000	1,600
100	2,000	2,000
150	3,000	2,400

Revenue’s line is y = 20x (slope 20, intercept 0); cost’s line is y = 8x + 1200 (slope 8, intercept 1,200). Using the algebraic formula:

X = (1200 - 0) / (20 - 8) = 100 units
Y = 20 * 100 = $2,000

So the business breaks even at 100 units / $2,000, which matches the row where revenue and cost are equal in the table. Plot revenue and cost as two lines, then add (100, 2000) as a one-point series to mark the break-even point on the chart.

Non-Linear Cases: Goal Seek and Solver

When at least one series is a curve (a polynomial trendline, an exponential, etc.), there is no simple two-line formula. Two built-in tools handle this:

• Goal Seek — best when you have a single formula for the gap between the two curves. Build a cell that computes =curve1 - curve2 as a function of one input cell (X). Then use Goal Seek to set that gap cell to 0 by changing the X cell. The resulting X is the intersection.
• Solver — use Solver when there are constraints, multiple variables, or you want the nearest crossing to a starting guess. Set the objective to minimize the squared gap (curve1 - curve2)^2 (or set it to zero) by changing the X cell, optionally bounding X to the range you care about so Solver returns the crossing you actually want.

For either tool, start the X cell near where the curves visually meet on the chart, since both methods converge to the nearest solution.

Troubleshooting

• The lines never cross / #DIV/0! — the two lines are parallel (equal slopes). Confirm with =SLOPE(...) on each series; if they match, there is no intersection within any range.
• Excel won’t show the trendline equation — you must add the trendline first; the Display Equation on chart box lives in the trendline options. See how to add a trend line in Excel.
• The marker lands in the wrong spot on a Line chart — Line charts space X values evenly as categories, so a calculated X may not align. Switch to an XY Scatter chart, where X is a true numeric axis.
• Interpolation gives an X outside your data — the two series don’t actually cross within the rows you bracketed; widen the range or check that the difference column really changes sign.
• Goal Seek returns a far-off value — its starting point was closer to a different crossing. Reset the X cell near the visible intersection and run it again.

Frequently Asked Questions

Does Excel have a built-in function for calculating intersection points?

No, Excel has no single intersection function. For two straight lines you combine SLOPE and INTERCEPT and solve (b2 − b1) / (m1 − m2) for X. For curves, use Goal Seek or Solver. The trendline feature only shows an approximate crossing visually.

How do I find the intersection of two lines in Excel?

Get each line’s slope and intercept with =SLOPE(range, xrange) and =INTERCEPT(range, xrange), then compute the crossing X with =(b2 - b1) / (m1 - m2) and the Y with =m1 * X + b1. This gives the exact point where the two lines cross.

How do I mark where two lines cross on an Excel chart?

Calculate the intersection’s X and Y, put them in two cells, then right-click the chart → Select Data → Add and point the new series at those cells. Format that one-point series as a large contrasting marker so it sits exactly on the crossing.

How do I find a break-even point on an Excel chart?

Plot revenue and cost as two lines, then solve where they are equal: break-even units = fixed costs ÷ (price per unit − variable cost per unit). Plot that point as a separate one-point series to mark the break-even point on the chart.

Can I display multiple intersection points on one graph?

Yes. With more than two series, or curves that cross several times, calculate each crossing separately and add each one as its own one-point marker series, or list all the points in one helper table and add them as a single scatter series of markers.

Why doesn’t my interpolation match the chart?

Linear interpolation assumes the segment between the two bracketing rows is a straight line, which matches an XY Scatter drawn “with straight lines.” If your chart uses a smoothed or curved line, the visual crossing will differ slightly — use Goal Seek or Solver against the actual curve formula instead.