Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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)

Screenshot

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

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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 enter image description here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.