Snap ticks to decades

If you really want to do it, you can change the Vertical axis crosses to the value you want to start with. In this case, we will start with 18.

We want to get rid of the ugly axis on the left so you then create a copy of the chart and delete everything and remove all fill colors except for the axis such as the chart below. You then create a white box with no borders and cover the original chart Y axis. Please note that I forgot to set the line color to "No" and the ticks off for the top chart.

Next, you overlay the transparent chart and you get what you want. To use VBA to automatically update your chart, you can use ActiveChart.Axes(xlCategory).CrossesAt = 20 and have all scale changes be done for both the overlay chart and the underlying chart.

You may want to use another graphing program or just use the first chart you posted because it probably won't be worth your time to do this for complex charts.

Code to do so automatically:

Sub CreateDemoPlot()    Dim chart2 As ChartObject    Dim shape1 As shape    Range("A1:A6") = Application.Transpose(Split("20,40,100,1000,4500,10000", ","))    Range("B1:B6") = Application.Transpose(Split("-30,-50,-90,-70,-75,-88", ","))    Range("D3:K15").Name = "ChartArea" 'Set Chart Area    With ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=200)        .Chart.SeriesCollection.NewSeries        .Chart.ChartType = xlXYScatterLinesNoMarkers        .Chart.Axes(xlValue).ScaleType = xlLinear        .Chart.Axes(xlValue).CrossesAt = -1000        .Chart.Axes(xlCategory).ScaleType = xlScaleLogarithmic        .Chart.Axes(xlCategory).HasMajorGridlines = True        .Chart.Axes(xlCategory).HasMinorGridlines = True        .Chart.Axes(xlCategory).MinimumScale = 0.9 * Cells(1, 1)        .Chart.Axes(xlCategory).MaximumScale = 1.1 * Cells(6, 1)        .Chart.Axes(xlCategory).MajorUnit = 10        .Chart.HasLegend = False        .Chart.SeriesCollection.NewSeries        .Chart.SeriesCollection(1).XValues = Range("A1:A6")        .Chart.SeriesCollection(1).Values = Range("B1:B6")        .Chart.Axes(xlCategory).CrossesAt = 18 'Or where ever the actual data starts        .Chart.Axes(xlCategory).MinimumScale = 10 'Set to 10 instead of the above code        'position to chart area        .Top = Range("ChartArea").Top        .Left = Range("ChartArea").Left        .Copy        'create white box        ActiveSheet.Shapes.AddShape msoShapeRectangle, 50, 50, 45, 200        Set shape1 = ActiveSheet.Shapes(2)        shape1.Fill.ForeColor.RGB = RGB(255, 255, 255)        shape1.Line.ForeColor.RGB = RGB(255, 255, 255)        'Position whitebox        shape1.Left = Range("ChartArea").Left        shape1.Top = Range("ChartArea").Top        'Paste overlay chart        ActiveSheet.Paste        Set chart2 = ActiveSheet.ChartObjects("Chart 3")        'Position overlay Chart        chart2.Top = Range("ChartArea").Top        chart2.Left = Range("ChartArea").Left        'Clear out overlay chart        chart2.Chart.Axes(xlValue).Format.Line.Visible = msoFalse        chart2.Chart.SeriesCollection(1).Format.Line.Visible = msoFalse        chart2.Chart.PlotArea.Format.Fill.Visible = msoFalse        chart2.Chart.Axes(xlCategory).Delete        chart2.Chart.SetElement (msoElementPrimaryValueGridLinesNone)        chart2.Chart.SetElement (msoElementPrimaryCategoryGridLinesNone)        chart2.Chart.ChartArea.Format.Fill.Visible = msoFalse        'Adjust Y axis position from overlay chart        chart2.Chart.PlotArea.Left = 10        chart2.Chart.PlotArea.Top = 0    End WithEnd Sub

I believe I was able to coax Excel into producing exactly the graph you were asking for using the following strange set of steps:

1. Create the log plot with the limits you want
2. Turn off the vertical grid lines
3. create a new array with the values of the grid lines you would like (e.g. 70, 80 90, 100, 200, 300 etc)
4. Plot a second series where the desired grid values are the X, and the negative limit of the graph is the Y (same value for all of them)
5. Use no markers, and no line for this series
6. Add error bars for Y - only in the positive direction, with a value equal to the total range of the Y axis (max - min)
7. Add data labels to the series, with only the X value represented
8. Move the label underneath the point

Result:

It's a log plot; the labels are correct; the "gridlines" are correct. It's beautiful.

Now for automating this… Unfortunately I don't have time right now to produce a "cleaned up" version of the code needed to do this (when you record the above as a macro it produces the usual Excel mess…), but if you follow the above instructions to do it manually you do indeed get exactly the plot shown.