How to Find the Tangent on a Graph in Excel

By Sean Mann

The tangent line for a graph at a given point is the best straight-line approximation for the graph at that spot. The slope of the tangent line reveals how steep the graph is rising or falling at that point. This type of information can be utilized on a business graph to highlight the rate at which important data, such as profits, are rising during a certain period. You can approximate a tangent line in Microsoft Excel 2010 by adding a trendline to your graph.

Open your spreadsheet in Microsoft Excel 2010.

Create a column containing the X values for your graph. It's helpful to include multiple X values close to the point for which you're calculating the tangent line. For example, enter values of 0, 0.999, 1, 1.001, 2, 3, 4 and 5 in the cells A1 through A8. The X values of 0.999 and 1.001 will assist in calculating the tangent line for an X value of 1.

Create a column containing the Y values for your graph. If you're using a formula, such as "y = 5 + sqrt(x)," enter "=5+sqrt(A1)" into cell B1, then copy and paste cell B1 to cells B2 through B8. Copying is done by clicking cell B1, pressing the Ctrl+C keys to copy, click and dragging cells B2 through B8, then pressing Ctrl+V to paste.

Draw a graph for your data by click and dragging the cells containing the X and Y values closest to the point for which you want to calculate the tangent. Click the "Insert" tab on the Excel ribbon, click the "Scatter" drop-down menu from the "Charts" group and select the "Scatter with only Markers" option. Using the previous example, draw a graph with the X and Y values from A2 to B4.

Draw a trend line for your graph by right-clicking a point on the graph and selecting "Add Trendline...." Select "Linear," click the box for "Display Equation on chart" and click "Close." This trend line is an approximation for the actual tangent line. The equation for the trendline is displayed on the graph using the "y = mx + b" format. Using the previous example, this gives the approximate equation for the tangent line as "y = 0.5x + 2.5."

Tips

Mathematically calculate the tangent line with Excel's "SLOPE(known_y's,known_x's)" function. The "known_y's" and "known_x's" should be the range of cells with Y and X values closest to the point for which you're calculating the tangent line. Using the previous example, enter "=SLOPE(B2:B4,A2:A4)" in an empty cell, which results in a slope of 0.5. The equation for the tangent line is "y - Y = dy/dx * (X) * (x - X)." The point (X,Y) is where you're calculating the tangent line. The variable "dy/dx" is the result of the "SLOPE" function, representing the slope of the tangent line. Using the previous example at the point (1,3), this gives an equation for the tangent line of "y - 3 = 0.5 * 1 * (x - 1)," which simplifies to "y = 0.5*x + 2.5."

Warnings

Information in this article applies to Microsoft Excel 2010. It may vary slightly or significantly with other versions or products.

References

Writer Bio