7

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.

2
  • 2
    Brilliant, clear well worded question. +1
    – Dave
    Feb 9 '19 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 '19 at 1:26
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.