Snap ticks to decades

charts, excel, vba

Solution

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

Problem

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 ```

Original source