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'm creating a lot of graphs, so I'm making a common scale to be able to compare them using MAX/MIN values that I'm hiding in the graph as a separate series. My Vertical AXIS (Y) is in units of 5.

The problem is that if my MAX is 27.5 then my scale in the graph is going to 35, and not 30. It always bumps up one extra 5. My data ranges (and space) demands I keep things tight, so:

How do I amend my MAX/MIN values/series so it ROUNDS() to the next higher unit of 5?

How do I then limit the graph scale to the next nearest unit of 5?

Hopefully it's not a manual thing with each graph.

share|improve this question
Possible solution: change one graph to the prefered Y AXIS scale and then > copy > paste special> formats. It's not ideal but it's quicker than I had expected. Any other solutions still very welcome. – RocketGoal Aug 20 '10 at 13:08
I'm not 100% sure I understand exactly what you're wanting, but are you just looking for =CEILING(A1,5) to round up the values of max? – DMA57361 Aug 20 '10 at 13:17
Yep. Ceiling is what I needed - just found it next to roundup in the help file. Thanks. – RocketGoal Aug 20 '10 at 16:21
up vote 1 down vote accepted

I think you have to do it using VBA.

  1. You can first compute the MAX, MIN and Major Unit value in a cell.
    D1: =CEILING(MAX(B:B),5) gives the next higher unit of 5 (eg. 25)
    D2, D3: Any fix value or formula as you like

  2. And then use the following script to link the MaximumScale, MinimumScale and MajorUnit of the chart to the cells.

    Option Explicit
    Private Sub ChangeAxisScales()
        With ActiveSheet.ChartObjects("Chart 1").Chart
            ' Value (Y) Axis
            With .Axes(xlValue)
                .MaximumScale = ActiveSheet.Range("$D$1").Value
                .MinimumScale = ActiveSheet.Range("$D$2").Value
                .MajorUnit = ActiveSheet.Range("$D$3").Value
            End With
        End With
    End Sub

(Alternatively, you can calculate the values in VBA as well.)

Reference link:

share|improve this answer

@DMA57361 has the right solution, the CEILING function is your friend.

share|improve this answer
Ceiling works. My remaining problem is making a graph scale that does not bump up by the additional 5 extra units (see question). Ceiling gives me 22 but graph gives me an axis limit of 30. That's too much for my space, etc. – RocketGoal Aug 20 '10 at 16:23

You must log in to answer this question.

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