Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .