# 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?

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).

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!). Dec 26 '09 at 15:23
• The category axis has unsequenced text. What I'm trying to do is something like a Gantt chart. Dec 26 '09 at 15:24
• -1 I need to do this as well. This is not an answer, it's a lecture. Jul 13 '12 at 11:40
• This should be a comment rather than an answer. Oct 14 '16 at 17:27

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 :) 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. Dec 25 '09 at 9:10

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. 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. 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