How do I write blank cells so they don't display??
I have a series of charts that display calculated results from a set of variables on a weekly, monthly and annual basis. The various worksheets have all the formulae in place ready for the core data to be ‘populated’ up to December this year. Each worksheet, therefore, has zeroes in months that have yet to have data recorded.
The charts I produce are mixed line and bar, occasionally with 2 x-axes or 2 y-axes
Taking the following series: 1,2,3,0,5,6,0,0,0,0,0,0 as an example, I want my line charts (not a problem with bar charts) to display ‘included’ zeroes (in this series, the zero between 3 and 5) but not to display subsequent zeroes – in this case, any zero after 6 . Admittedly this is primarily for appearances sake but also avoids confusion with those out there less able to read charts. The first part I realise I may have to do manually
I have tried having the various formulae insert a null (“ “) or an #N/A I have seen mentioned, where core data is a zero but even though the cell appears empty, this still charts as zero. However, if I delete the contents to create a truly blank cell then the non-display bit works, if you follow, and lines stop ‘in mid-air’ as desired.
So my question to you is, how do I create/write a truly blank cell from a formula?
I have a series of charts that display calculated results from a set of variables on a weekly, monthly and annual basis. The various worksheets have all the formulae in place ready for the core data to be ‘populated’ up to December this year. Each worksheet, therefore, has zeroes in months that have yet to have data recorded.
The charts I produce are mixed line and bar, occasionally with 2 x-axes or 2 y-axes
Taking the following series: 1,2,3,0,5,6,0,0,0,0,0,0 as an example, I want my line charts (not a problem with bar charts) to display ‘included’ zeroes (in this series, the zero between 3 and 5) but not to display subsequent zeroes – in this case, any zero after 6 . Admittedly this is primarily for appearances sake but also avoids confusion with those out there less able to read charts. The first part I realise I may have to do manually
I have tried having the various formulae insert a null (“ “) or an #N/A I have seen mentioned, where core data is a zero but even though the cell appears empty, this still charts as zero. However, if I delete the contents to create a truly blank cell then the non-display bit works, if you follow, and lines stop ‘in mid-air’ as desired.
So my question to you is, how do I create/write a truly blank cell from a formula?
Last edited: