how to drop down on teh x-axis the line from a point in XY scatter graphas

AFAIK, Excel does not provide a built-in Drop Lines feature for XY (Scatter) charts. Drop lines are available for some line charts, but they are not supported for scatter charts, in particular when the chart uses logarithmic axes.

One workaround is to create a helper series that contains two points for each vertical line. Each pair of points uses the same X value. The first point is the actual data point, and the second point is placed at the minimum value of the Y-axis. On a logarithmic axis, this minimum cannot be zero because logarithms are undefined for zero. Instead, use the lowest value displayed on the axis, such as 1 or 0.1.

For example, if your original data are:

|    X |     Y |
| ---: | ----: |
|  600 | 91.02 |
| 1200 | 57.87 |
| 4500 | 28.84 |

Create a helper table as follows:

|         X |         Y |
| --------: | --------: |
|       600 |     91.02 |
|       600 |         1 |
| *(blank)* | *(blank)* |
|      1200 |     57.87 |
|      1200 |         1 |
| *(blank)* | *(blank)* |
|      4500 |     28.84 |
|      4500 |         1 |

The repeated X values cause Excel to draw a vertical line, while the two Y values define the top and bottom of the line. The blank rows ensure that Excel does not connect one vertical line to the next.

After creating the helper table, add it to the chart as a new data series by right-clicking the chart and selecting Select Data. Click Add, then specify the helper X and Y ranges. Next, change the new series to XY (Scatter) with Straight Lines, remove the markers, and format the line with the desired color and thickness. The result is a set of vertical drop lines extending from each point to the bottom of the logarithmic Y-axis.

For a chart whose Y-axis ranges from 1 to 100, the finished chart will appear similar to the following:

Y
100 |        ●
 50 |        │
 20 |        │        ●
 10 |        │        │
  5 |        │        │         ●
  1 |________│________│_________│________ X
          600      1200      4500

Each vertical line is simply another XY series consisting of two points with the same X-coordinate.

Using error bars is generally not suitable for this purpose on a logarithmic Y-axis. Error bars cannot extend to zero, and because a logarithmic axis does not include zero, they cannot create a drop line to the X-axis. Similarly, manually drawing lines with Insert → Shapes → Line may work for a static figure, but the lines will not move or resize automatically when the chart or data changes.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin