[Excel] How to Plot a Specific Value on a Graph Line | Using Linear Interpolation

2025年12月31日

Introduction

When plotting data on a graph, have you ever found yourself manually placing a shape on a dotted line, wondering, “Where exactly does this value fall?”

I used to manually paste red dots while looking at the numbers, but this method is time-consuming and lacks precision.

Thinking there must be a way to calculate the position automatically, I arrived at Linear Interpolation. In this article, I will introduce how to use linear interpolation to calculate the exact position on a graph corresponding to a specified value and plot it automatically.

Why Use Linear Interpolation?

Linear interpolation is a method of estimating a value within two known data points. By using this, you can accurately determine where to plot a point on a graph line, rather than guessing.

Concrete Example: Automatically Placing a Red Dot

For this example, we will use the following data structure:

C3:G3 (X-axis): Data such as temperature or distance (e.g., 400, 600, 800, 1000, 1200)
C4:G4 (Y-axis): Data such as measurement values (e.g., 127, 119, 100, 65, 14)
- Note: For the formula below to work, this Y-data must be sorted in descending order.
B7 (Target Y): The value you want to find (e.g., 59).
B8 (Calculated X): The value calculated via linear interpolation (e.g., 1034).

Based on these, we will plot a red dot at the appropriate location on the graph.

Linear Interpolation Formula and Implementation in Excel

Linear interpolation is calculated using the following formula:

$$X = x_1 + \frac{(y – y_1) \cdot (x_2 – x_1)}{y_2 – y_1}$$

$y$: The specified Y value (B7)
$x_1, x_2$: The X values surrounding the target Y
$y_1, y_2$: The Y values surrounding the target Y

In Excel, this is expressed using the following formula:

=INDEX(C3:G3, MATCH(B7, C4:G4, -1)) + (B7 - INDEX(C4:G4, MATCH(B7, C4:G4, -1))) * (INDEX(C3:G3, MATCH(B7, C4:G4, -1) + 1) - INDEX(C3:G3, MATCH(B7, C4:G4, -1))) / (INDEX(C4:G4, MATCH(B7, C4:G4, -1) + 1) - INDEX(C4:G4, MATCH(B7, C4:G4, -1)))

By entering this into cell B8, the X value corresponding to B7 is calculated automatically.

Steps to Plot on the Graph

1. Add the Calculation Result as a New Series

Use cells H3:H4 (or any empty cells) to input the coordinates for the single point:

H3: =B8 (The calculated X value)
H4: =B7 (The specified Y value)

2. Add to Graph

Right-click the graph and select Select Data.
Select Add Series.
Specify H3 for the Series X values and H4 for the Series Y values.

3. Set the Marker

The new point might not be visible initially if it’s just a dot. Right-click the new data point (or select it from the format tab) and choose Format Data Series.
Go to Marker Options.
Set the shape to a circle and the color to red.
Remove the line if necessary so only the dot appears.

Result

Now, the X value (e.g., 1034) corresponding to the specified Y value (e.g., 59) is accurately plotted on the graph line. This eliminates the need for manual placement and makes the data visually clear.

Summary

By using linear interpolation, it becomes possible to accurately plot specific values on a graph. This streamlines the process of visually confirming data positions and improves the quality of analysis. Please try it out.