Snap ticks to decades

Question

I have a chart with some data with a linear y-axis and a logarithmic x-axis. The question is about the logarithmic (x-)axis.

I want the logarithmic ticks on the x-axis to align with exact decades (powers of 10), but I don't want the axis to necessarily start at the exact decades; I want it to start where my data starts. So for instance, the axis could start at 3; but the first major tick should be at 10. How do I do this?

Currently when I set the axis to start at 3, the major gridline is at 3, which is no good.

When I set the following properties, the grid and ticks are fine, but that's because I force the axis to start at a decade (which I don't want to do).

.Chart.Axes(xlCategory).ScaleType = xlScaleLogarithmic
.Chart.Axes(xlCategory).HasMajorGridlines = True
.Chart.Axes(xlCategory).HasMinorGridlines = True
.Chart.Axes(xlCategory).MinimumScale = 10 ^ (Int(Application.Log10(Cells(DATA_START, 6))))
.Chart.Axes(xlCategory).MaximumScale = 10 ^ (Int(Application.Log10(Cells(DATA_START + n, 6)) - 0.00001) + 1)

This is how it looks: nice grid, but axis not starting at the right place.

Now, when I don't specifically round the min and max of my axis to a decade,

' ...
.Chart.Axes(xlCategory).MinimumScale = 0.9 * Cells(DATA_START, 6)
.Chart.Axes(xlCategory).MaximumScale = 1.1 * Cells(DATA_START + n, 6)

it looks like this, with the axis starting at the right place, but the grid/ticks looking silly:

In this example, I would expect the first tick to be at 100 and only minor ticks/gridlines before that.

I have already figured out, that I can set the multiplicative factor between two major ticks with .MajorUnit = 10.

---

I have a SSCCE for you: just run this macro on an empty sheet. It produces a chart that has the major ticks (and gridlines) at 18, 180, 1800, but I want them at 100, 1000.

Sub CreateDemoPlot()
    Range("A1:A6") = Application.Transpose(Split("20,40,100,1000,4500,10000", ","))
    Range("B1:B6") = Application.Transpose(Split("-30,-50,-90,-70,-75,-88", ","))
    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")
    End With
End Sub

Answer 1

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 With
End Sub

Answer 2

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.