Plotting blank cells

Copper Contributor

I am plotting a time series of readings.  The dates are converted from "day of the year" to "day of the heating season" by a formula.  If the date is in the future when no reading has yet been taken, it shows as a blank. 

For example


If the data selected for plotting is only the dates for which readings have been created, it works fine.  But if the data range is extended to include blanks calculated by the above formula, the whole graph is corrupted.  Instead of the x cordinates being the day of the heating season (eg 6, 13, 20....), they are converted to a numerical order (1, 2, 3....). 

If I manually delete the formula from the blank dates then the graph is restored.

How can I get Excel to plot a formula-created blank in the same way that it plots a manually-created blank?


2 Replies


You might use the FILTER function to create a new range containing only the non-blank values, and use that as source range for the chart.

@Hans Vogelaar 

Thanks Hans.  That's a good suggestion as a workaround.  I was really hoping that I had missed some basic way of telling Excel to ignore the formula that creates the blank cell, and just plot the blank cell.  Maybe that doesn't exist.