How can I ignore zero-values in an Excel graph?












2















I have built a template in Excel that organizes data and shows a graph. The graph plots two fields, Var1 and Var2, on the vertical against year along the horizon. Both Var1 and Var2 are pulled from cells that are formulae.



I have found that I cannot tell the graph to ignore certain years from one variable and not from another. I have also found that the graph of either variable will show show values of zero. I would like for it to not. The only way I have found around this is to go to the cells from which the graph pulls and delete the formulae therein. This is a tedious process as we use this template for many instances. Is there a way to tell Excel to ignore cells that have a zero value even if it is the output of a function?enter image description here



Thanks.






microsoft-excel worksheet-function







share|improve this question

























  • Brilliant, clear well worded question. +1

    – Dave
    Feb 9 at 20:13











  • What is actually in the cells displaying as zero (number 0, blank cell, formula that returns a null (""), something else?

    – fixer1234
    Feb 10 at 1:26
















2















I have built a template in Excel that organizes data and shows a graph. The graph plots two fields, Var1 and Var2, on the vertical against year along the horizon. Both Var1 and Var2 are pulled from cells that are formulae.



I have found that I cannot tell the graph to ignore certain years from one variable and not from another. I have also found that the graph of either variable will show show values of zero. I would like for it to not. The only way I have found around this is to go to the cells from which the graph pulls and delete the formulae therein. This is a tedious process as we use this template for many instances. Is there a way to tell Excel to ignore cells that have a zero value even if it is the output of a function?enter image description here



Thanks.






microsoft-excel worksheet-function







share|improve this question

























  • Brilliant, clear well worded question. +1

    – Dave
    Feb 9 at 20:13











  • What is actually in the cells displaying as zero (number 0, blank cell, formula that returns a null (""), something else?

    – fixer1234
    Feb 10 at 1:26














2












2








2








I have built a template in Excel that organizes data and shows a graph. The graph plots two fields, Var1 and Var2, on the vertical against year along the horizon. Both Var1 and Var2 are pulled from cells that are formulae.



I have found that I cannot tell the graph to ignore certain years from one variable and not from another. I have also found that the graph of either variable will show show values of zero. I would like for it to not. The only way I have found around this is to go to the cells from which the graph pulls and delete the formulae therein. This is a tedious process as we use this template for many instances. Is there a way to tell Excel to ignore cells that have a zero value even if it is the output of a function?enter image description here



Thanks.






microsoft-excel worksheet-function







share|improve this question
















I have built a template in Excel that organizes data and shows a graph. The graph plots two fields, Var1 and Var2, on the vertical against year along the horizon. Both Var1 and Var2 are pulled from cells that are formulae.



I have found that I cannot tell the graph to ignore certain years from one variable and not from another. I have also found that the graph of either variable will show show values of zero. I would like for it to not. The only way I have found around this is to go to the cells from which the graph pulls and delete the formulae therein. This is a tedious process as we use this template for many instances. Is there a way to tell Excel to ignore cells that have a zero value even if it is the output of a function?enter image description here



Thanks.






microsoft-excel worksheet-function




microsoft-excel worksheet-function






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Feb 10 at 3:36







bloomers

















asked Feb 9 at 19:35









bloomersbloomers

1164




1164













  • Brilliant, clear well worded question. +1

    – Dave
    Feb 9 at 20:13











  • What is actually in the cells displaying as zero (number 0, blank cell, formula that returns a null (""), something else?

    – fixer1234
    Feb 10 at 1:26



















  • Brilliant, clear well worded question. +1

    – Dave
    Feb 9 at 20:13











  • What is actually in the cells displaying as zero (number 0, blank cell, formula that returns a null (""), something else?

    – fixer1234
    Feb 10 at 1:26

















Brilliant, clear well worded question. +1

– Dave
Feb 9 at 20:13





Brilliant, clear well worded question. +1

– Dave
Feb 9 at 20:13













What is actually in the cells displaying as zero (number 0, blank cell, formula that returns a null (""), something else?

– fixer1234
Feb 10 at 1:26





What is actually in the cells displaying as zero (number 0, blank cell, formula that returns a null (""), something else?

– fixer1234
Feb 10 at 1:26










1 Answer
1






active

oldest

votes


















2














There are various methods for avoiding the zeroes, none of them are perfect.
Below are a couple of such methods.



Filtering the data set




  1. Select the data range.

  2. On the Data tab, click Filter in the Sort & Filter group,
    to add a filter to all of the columns.

  3. Click the drop-down arrow on the column's first row and uncheck 0.

  4. Click OK to filter the column, which will filter the entire row.
    Be sure to remove the filter when you're done.


Replace 0s with NA()




  1. Select the data set

  2. Click Find & Select in the Editing group on the Home tab and choose Replace,
    or type Ctrl+H.

  3. Enter 0 in Find what.

  4. Enter =NA() in Replace

  5. Click Options to display additional settings and
    check Match entire cell contents.

  6. Click Replace All.

  7. Click Close and OK.


For more information see the article
How to suppress 0 values in an Excel chart.






share|improve this answer























    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%2f1403967%2fhow-can-i-ignore-zero-values-in-an-excel-graph%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    2














    There are various methods for avoiding the zeroes, none of them are perfect.
    Below are a couple of such methods.



    Filtering the data set




    1. Select the data range.

    2. On the Data tab, click Filter in the Sort & Filter group,
      to add a filter to all of the columns.

    3. Click the drop-down arrow on the column's first row and uncheck 0.

    4. Click OK to filter the column, which will filter the entire row.
      Be sure to remove the filter when you're done.


    Replace 0s with NA()




    1. Select the data set

    2. Click Find & Select in the Editing group on the Home tab and choose Replace,
      or type Ctrl+H.

    3. Enter 0 in Find what.

    4. Enter =NA() in Replace

    5. Click Options to display additional settings and
      check Match entire cell contents.

    6. Click Replace All.

    7. Click Close and OK.


    For more information see the article
    How to suppress 0 values in an Excel chart.






    share|improve this answer




























      2














      There are various methods for avoiding the zeroes, none of them are perfect.
      Below are a couple of such methods.



      Filtering the data set




      1. Select the data range.

      2. On the Data tab, click Filter in the Sort & Filter group,
        to add a filter to all of the columns.

      3. Click the drop-down arrow on the column's first row and uncheck 0.

      4. Click OK to filter the column, which will filter the entire row.
        Be sure to remove the filter when you're done.


      Replace 0s with NA()




      1. Select the data set

      2. Click Find & Select in the Editing group on the Home tab and choose Replace,
        or type Ctrl+H.

      3. Enter 0 in Find what.

      4. Enter =NA() in Replace

      5. Click Options to display additional settings and
        check Match entire cell contents.

      6. Click Replace All.

      7. Click Close and OK.


      For more information see the article
      How to suppress 0 values in an Excel chart.






      share|improve this answer


























        2












        2








        2







        There are various methods for avoiding the zeroes, none of them are perfect.
        Below are a couple of such methods.



        Filtering the data set




        1. Select the data range.

        2. On the Data tab, click Filter in the Sort & Filter group,
          to add a filter to all of the columns.

        3. Click the drop-down arrow on the column's first row and uncheck 0.

        4. Click OK to filter the column, which will filter the entire row.
          Be sure to remove the filter when you're done.


        Replace 0s with NA()




        1. Select the data set

        2. Click Find & Select in the Editing group on the Home tab and choose Replace,
          or type Ctrl+H.

        3. Enter 0 in Find what.

        4. Enter =NA() in Replace

        5. Click Options to display additional settings and
          check Match entire cell contents.

        6. Click Replace All.

        7. Click Close and OK.


        For more information see the article
        How to suppress 0 values in an Excel chart.






        share|improve this answer













        There are various methods for avoiding the zeroes, none of them are perfect.
        Below are a couple of such methods.



        Filtering the data set




        1. Select the data range.

        2. On the Data tab, click Filter in the Sort & Filter group,
          to add a filter to all of the columns.

        3. Click the drop-down arrow on the column's first row and uncheck 0.

        4. Click OK to filter the column, which will filter the entire row.
          Be sure to remove the filter when you're done.


        Replace 0s with NA()




        1. Select the data set

        2. Click Find & Select in the Editing group on the Home tab and choose Replace,
          or type Ctrl+H.

        3. Enter 0 in Find what.

        4. Enter =NA() in Replace

        5. Click Options to display additional settings and
          check Match entire cell contents.

        6. Click Replace All.

        7. Click Close and OK.


        For more information see the article
        How to suppress 0 values in an Excel chart.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Feb 9 at 20:09









        harrymcharrymc

        261k14271577




        261k14271577






























            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%2f1403967%2fhow-can-i-ignore-zero-values-in-an-excel-graph%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

            Probability when a professor distributes a quiz and homework assignment to a class of n students.

            Image
            0 0 $begingroup$ Need help with this problem. Suppose our lazy professor collects a quiz and a homework assignment from a class of n students one day, then distributes both the quizzes and the homework assignments back to the class in a random fashion for grading. Each student receives one quiz and one homework assignment to grade. (a) What is the probability that every student receives someone else's quiz to grade, and someone else's homework to grade? (b) What is the probability that no student receives both their own quiz and their own homework assignment to grade? In this case, some students may receive their own quiz, and others may receive their own homework assignment. (c) Compute the limiting probability as n approaches infinity in each case. ...
            Read more

            Aardman Animations

            Image
            Aardman Animations, Ltd. Aardman Animations Tipo privada Atividade Animação em stop-motion , Animação em CGI Fundação 1972 Fundador(es) Peter Lord David Sproxton Sede Bristol, Inglaterra  Reino Unido Proprietário(s) Dreamworks Pessoas-chave Peter Lord David Sproxton Nick Park Divisões Aardman Features Aardman Digital Aardman Commercials Aardman Broadcast Aardman International Aardman Rights Aardman Effects Aardman 3-D Systems Aardman Nathan Love AardBoiled Website oficial aardman.com ...
            Read more

            Are they similar matrix

            Image
            0 $begingroup$ Do $begin{bmatrix} 0&i&0\0&0&1\0&0&0 end{bmatrix} $ and $begin{bmatrix} 0&0&0\-i&0&0\0&1&0 end{bmatrix} $ are similar.Is this True/false Clearly both are nilpotent and one is conjucate transpose of other but how to know if they are similar.i'm stuck. Please help me linear-algebra vector-spaces eigenvalues-eigenvectors generalizedeigenvector share | cite | improve this question edited Dec 10 '18 at 6:38 Vasanth Kris asked Dec 10 '18 at 6:19 ...
            Read more