Skip graphing cells that don't have values.

[sethness15]

Hi All,

I'm not sure if this is possible but I figured I'd ask.. I've got an excel template that is set up to always graph the values over a specified range of cells. I don't want to have to edit the graph each time but I'd like it to only graph the cells that have values and skip the ones that are equal to 0 or #N/A.

Please let me know if anyone has a solution to this.

Thanks!

 

[pbornemeier]

What version of Excel are you using? In 2007 and later, you can specify how Excel treats empty cells with the button in the Select data popup.
As far as the zeros go, you will likely have to either delete the zeros with a search and replace, if they are fixed values, or
If they contain formulas, modify the formula to change a value of 0 to NA(): =If(original formula=0,NA(),original formula), or
if the source is created by code, modify the code to do the same.

 

[sethness15]

I'm using excel 2010. I can get it not to graph certain values using the Hidden and Empty Cell Settings but I'd like it make the x axis scale automatically so it only graphs where there is y value data.. I probably didn't explain that well enough in my first post.

 

[pbornemeier]

Please post a sample of your data that illustrates the problem.

If you use a dynamic named range to define your data, then you can adjust it to not have any blank areas on either end, but I am not sure if you can remove interior points.