Set automatic axis limits for bar chart without defaulting to zero












1















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?










share|improve this question





























    1















    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?










    share|improve this question



























      1












      1








      1








      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?










      share|improve this question
















      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






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Aug 8 '12 at 19:16









      Excellll

      11.1k74162




      11.1k74162










      asked Dec 23 '09 at 12:22









      djeidotdjeidot

      1,21311214




      1,21311214






















          4 Answers
          4






          active

          oldest

          votes


















          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.






          share|improve this answer
























          • 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



















          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






          share|improve this answer


























          • 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



















          1














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






          share|improve this answer































            -2














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






            share|improve this answer
























            • 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











            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
            });


            }
            });














            draft saved

            draft discarded


















            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









            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.






            share|improve this answer
























            • 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
















            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.






            share|improve this answer
























            • 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














            1












            1








            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.






            share|improve this answer













            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.







            share|improve this answer












            share|improve this answer



            share|improve this answer










            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



















            • 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













            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






            share|improve this answer


























            • 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
















            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






            share|improve this answer


























            • 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














            1












            1








            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






            share|improve this answer















            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







            share|improve this answer














            share|improve this answer



            share|improve this answer








            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



















            • 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











            1














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






            share|improve this answer




























              1














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






              share|improve this answer


























                1












                1








                1







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






                share|improve this answer













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







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Jan 9 '13 at 4:47









                adelriosantiagoadelriosantiago

                1112




                1112























                    -2














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






                    share|improve this answer
























                    • 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
















                    -2














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






                    share|improve this answer
























                    • 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














                    -2












                    -2








                    -2







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






                    share|improve this answer













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







                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    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



















                    • 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


















                    draft saved

                    draft discarded




















































                    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.




                    draft saved


                    draft discarded














                    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





















































                    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







                    Popular posts from this blog

                    flock() on closed filehandle LOCK_FILE at /usr/bin/apt-mirror

                    Mangá

                    Eduardo VII do Reino Unido