Create an empty cell via formula

[piannetta]

Hi,

This is actually a chart problem, I chart time series values and they align to days of weeks. Certain days have no values and when I display them in a chart, I get a funky looking line that goes up and down rather than follow a trend of the days where there is actually data.

I know that in Excel, an empty cell doesn't show on a chart, where as a zero value cell does. Ideally I just need a way to easily remove the zero values from a chart that doesn't require me having to manually filter them out. I couldn't find a way to do that so my next best approach was to see if in the formula for each cell I can display an "empty" value (I know how odd/contradictory that sounds) rather than a "blank" cell (using "", which doesn't work as these also appear in the chart) but I can't seem to find a function that does that.

Does anyone have any ideas please?

Cheers,
Pete

 

[JustynaMK]

Hi Pete,

Which Office version are you using? Did you try going to Chart Options > Select Data Source > Hidden and Empty Cells > Show empty cells as: Gaps?
Once this option is checked your blank (="") cells should be presented as gaps.

Alternative solution is to have "Show #N/A as an empty cell" option checked (the same options window), so that when your additional formula converts zeros to #N/As you will achieve the same result (gaps), e.g.:
=IF(A1<>0,A1,NA())

Screenshots with chart settings:
https://support.office.com/en-us/ar...r=ZXL900&HelpId=90807&ui=en-US&rs=en-US&ad=US

Take care,
Justyna

 

[piannetta]

Hi Justyna,

Thanks for the tips. I'm using the Office 365 version of Excel 2016.

The first tip doesn't work. I don't think Excel sees those cells as empty because there's a formula in them. Your second tyip does work but I've been trying to avoid that as I need the data table to also be readable and with a bunch of #NA's all through it, it's pretty ugly to look at.

Cheers,
Pete

 

[JustynaMK]

Hi Pete,

Thanks for the update.

1) You mentioned your Excel does not see cells as empty because you have some formula in them. Would you be able to share this formula with me?
2) As for #N/As, the other solution might be to apply conditional formatting and change font colour to white, e.g. =ISERROR(A1) ?
Alternatively, try wrapping your original formula with =IFERROR(...,"") - maybe by converting #N/As to blanks Excel chart will finally recognise these cells as empty.