2

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?

Improve this question

4 Answers 4

Reset to default
2

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:

Sub adjustscales()

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

Improve this answer
1

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.

Improve this answer
4
  • 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
    Commented Dec 26, 2009 at 15:23
  • The category axis has unsequenced text. What I'm trying to do is something like a Gantt chart.
    – djeidot
    Commented Dec 26, 2009 at 15:24
  • 2
    -1 I need to do this as well. This is not an answer, it's a lecture.
    – Erick Robertson
    Commented Jul 13, 2012 at 11:40
  • 1
    This should be a comment rather than an answer.
    – fixer1234
    Commented Oct 14, 2016 at 17:27
1

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.

alt text

Improve this answer
2
  • 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
    Commented Dec 23, 2009 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
    Commented Dec 25, 2009 at 9:10
-2

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

Improve this answer
4
  • Oh, come on, is it ALWAYS bad? Check my comments to Jon Peltier's answer.
    – djeidot
    Commented Jan 5, 2010 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
    Commented Jan 14, 2010 at 15:38
  • 1
    -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
    Commented Jul 13, 2012 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
    Commented Jul 13, 2012 at 16:12

You must log in to answer this question.

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