# Set automatic axis limits for bar chart without defaulting to zero

I am building a bar chart in Excel with data values ranging from, e.g., 10 to 20. I want the x-axis limits to be automatic, but although the right limit (near 20) works correctly, the left limit always defaults to 0.

I'd like the left limit to be near 10, instead of zero, without having to have the limit fixed. Is there any way to do this?

-

You should keep the minimum at zero if it's a bar chart. The reason is that the length of the bars encode their values, and truncating the bars breaks the relationship between length and value.

Or you could make a line or XY chart instead. You didn't say what the category axis consisted of.

-
Hi. I have no real problem keeping the minimum at zero, it's just that it leaves a lot of blank space (if you only have data from 10 to 20, 50% of the chart is blank space!). – djeidot Dec 26 '09 at 15:23
The category axis has unsequenced text. What I'm trying to do is something like a Gantt chart. – djeidot Dec 26 '09 at 15:24
-1 I need to do this as well. This is not an answer, it's a lecture. – Erick Robertson Jul 13 '12 at 11:40

I don't think it can be done without making it `Fixed`. You cannot change the results of `Auto` value since it is using a built-in algorithm of Excel. For those who would like to know how to change the `Fixed` values:

Click on the X axis, then right-click and select `Format Axis`. On the `Axis Options` tab, change the `Maximum` and `Minimum` values from `Auto` to desired `Fixed` values.

-
I know how to make it fixed. But if I fix it to 10, I won't be able to see data values of 9.8, for instance. Hence I have to keep reformatting the axis to lower the limit, which is annoying (this is a chart i'm reusing over and over). But thanks :) – djeidot Dec 23 '09 at 18:34
So one possibility that comes to mind: Estimate the minimum possible value that may occur in your case (let's say 8.7) and set the fixed axis value to something a bit less than that (let's say 8 or 8.5). Just an idea. – Mehper C. Palavuzlar Dec 25 '09 at 9:10

I know this thread is old but, recent users may find it useful to know that it is possible to adjust the minimum scale with a Macro.

Just create a macro with the following code:

Sheet1.ChartObjects("Chart 1").Chart.Axes(xlValue).MinimumScale = Sheet1.Range("A1")

Sheet1.ChartObjects("Chart 1").Chart.Axes(xlValue).MaximumScale = Sheet1.Range("A2")

End Sub

And of course create a formula on A1 and A2 to be the =MIN(data) and =MAX(data).

-

No. Moreover, remember: left limit more than zero is BAD.

-
Oh, come on, is it ALWAYS bad? Check my comments to Jon Peltier's answer. – djeidot Jan 5 '10 at 11:25
Before my comment, I gave you my answer. Maybe you don't like it, but according to my limited knowledge it is the only right answer. Nevertheless, I would be happy if someone will show you that it is not so and there is a different solution as you hope. – Toc Jan 14 '10 at 15:38
-1 Don't tell us it's bad to do this. I need to do it too for a line chart. Same problem, same question. – Erick Robertson Jul 13 '12 at 11:41
Your question is the same, my answer is the same. NO, it is not possible. Throw away everything after "moreover", if you do not like it. – Toc Jul 13 '12 at 16:12