Forum Discussion
Blank cells show up as zeros in chart
I followed the instruction but the empty cells still showed up as data point. (The column of data was copied and pasted as values to ensure no formula is in there). I found out that I have to press "delete" on the already emptied cells to prevent the point from showing up in the chart... feels like Excel does not treat an emptied cell truly emptied until delete is done on the cell? Thanks for the help.
Perhaps you copy/pasted as values cells with empty texts (=""). Even empty text is not visible and have zero length it's still the text. Thus the cell is not blank. If so - yes, you need to delete such empty texts.
I had the same problem with apparently blank cells plotting at zero on my graphs. I used the solution shown here [ rather than using '' in the IF statement, I used NA() ], and the problem seems to have been resolved. Thanks.
GaryChan "" is not an empty cell, it contains "", which is a short piece of text. Maybe it's not logical, but it's how Excel works.
- Thanks! It works. I wonder why "empty text" is not treated as empty. Maybe it has something to do with data type definition for that particular cell?
- A formula that looks like =IF(something,"") that returns "" is NOT an empty cell, and will be plotted as a zero value. Change it to =IF(something,NA()), and you will get #N/A in the cell, and #N/A can be plotted as if it is blank.
