Time units and automatic formatting of the y-axis in Excel

Question

I have a chart with time values on the vertical (Y) axis. As the values can change a lot the formatting of the axis minor and major units is automatic.

The problem is that Excel chooses values which could be nice if they where decimal but are not ideal when using times.

E.g., 0:00, 0:28, 0:57, 1:26, 1:55, 2:24, 2:52 (they corresponds to steps of 0.02 decimal)

Is there a way to have an automatic scale which looks nice when using time on the vertical axis?

Answer 1

You could use VBA to ensure that the maximum value is always a full hour, say, and that the minor unit is 5 mins and the major 10 minutes.

The macro could get very complex depending on how much you want to automate. Much easier would be four worksheet cells where you can type in the desired min, max, major, minor as time values and the chart updates instantly.

For example, a change event in the sheet could look like this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("chrtSettings")) Is Nothing Then

    With ActiveSheet.ChartObjects("Chart 1").Chart
        .Axes(xlValue).MinimumScale = Range("min")
        .Axes(xlValue).MaximumScale = Range("max")
        .Axes(xlValue).MajorUnit = Range("major")
        .Axes(xlValue).MinorUnit = Range("minor")
    End With


End If
End Sub

And in the worksheet there is a range of cells (with range names) where the values can be set quickly