Graphing blank cells in form of gaps in Excel
I'm graphing a set of data that has blanks in some cells. In the blank cells I have formulas and I have to keep the formulas. When I graph the data, the blank cells are graphed as zeros. I'd like to put gaps instead of zeros in the graph. I tried right click on the graph > Select Data > Hidden and Empty Cells Settings > Show empty cells as Gaps. But this did not help!
Answer
Instead of putting zeros or empty strings try to put #N/A.
You can do it with a formula like =IF([test],[value],NA()).
This will allow the graph not to show the missing values as zeros, but if I understand your question, it is still not what you want, because you want the missing values to be represented as gaps, not as missing values.
The only way that I know of to see the gaps is to use a scattered graph.
As far as I know, all the graphs that make a line to join two points, do join two points, and don't have the concept of missing point. They just join the two closest points.
A solution could be to make a VBA macro that goes inside the graph and changes the color of each graph line when the data is missing.