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?
microsoft-excel charts
add a comment |
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?
microsoft-excel charts
add a comment |
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?
microsoft-excel charts
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?
microsoft-excel charts
microsoft-excel charts
edited Aug 8 '12 at 19:16
Excellll
11.1k74162
11.1k74162
asked Dec 23 '09 at 12:22
djeidotdjeidot
1,21311214
1,21311214
add a comment |
add a comment |
4 Answers
4
active
oldest
votes
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
-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
This should be a comment rather than an answer.
– fixer1234
Oct 14 '16 at 17:27
add a comment |
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
add a comment |
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).
add a comment |
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
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f86862%2fset-automatic-axis-limits-for-bar-chart-without-defaulting-to-zero%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
-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
This should be a comment rather than an answer.
– fixer1234
Oct 14 '16 at 17:27
add a comment |
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
-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
This should be a comment rather than an answer.
– fixer1234
Oct 14 '16 at 17:27
add a comment |
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.
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.
answered Dec 24 '09 at 0:20
Jon Peltier
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
-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
This should be a comment rather than an answer.
– fixer1234
Oct 14 '16 at 17:27
add a comment |
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
-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
This should be a comment rather than an answer.
– fixer1234
Oct 14 '16 at 17:27
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
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
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
1
-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
-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
This should be a comment rather than an answer.
– fixer1234
Oct 14 '16 at 17:27
This should be a comment rather than an answer.
– fixer1234
Oct 14 '16 at 17:27
add a comment |
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
add a comment |
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
add a comment |
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 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.
edited Sep 29 '11 at 4:09
3498DB
15.7k114762
15.7k114762
answered Dec 23 '09 at 13:03
Mehper C. PalavuzlarMehper C. Palavuzlar
43.4k42175233
43.4k42175233
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
add a comment |
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 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
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
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
add a comment |
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).
add a comment |
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).
add a comment |
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).
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).
answered Jan 9 '13 at 4:47
adelriosantiagoadelriosantiago
1112
1112
add a comment |
add a comment |
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
add a comment |
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
add a comment |
No. Moreover, remember: left limit more than zero is BAD.
No. Moreover, remember: left limit more than zero is BAD.
answered Dec 27 '09 at 15:32
TocToc
1,25451824
1,25451824
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
add a comment |
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
Oh, come on, is it ALWAYS bad? Check my comments to Jon Peltier's answer.
– djeidot
Jan 5 '10 at 11:25
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
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
-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
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
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f86862%2fset-automatic-axis-limits-for-bar-chart-without-defaulting-to-zero%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown