How do I ignore non-numeric cells in a chart?
In Excel 2016, I have time series that may contain cells with missing data, typically labelled with a string. I'd like to plot the series ignoring non-numeric cells, but Excel wants to plot them as zeros. Here is how it looks:
A workaround could be to create another column with something like
=IF(C13="#N/A N/A",NA(),C13)
as suggested here.
However, I'd like to know if there is a way to avoid creating another column. Please note that I have selected the option: "Hidden and Empty Cell Settings"/"Show empty cells as: Gaps", but that doesn't seem to help.
Answer
Yes that's right, Excel Graph treats strings as zeros. The "Show empty cells as Gaps" option under "Hidden and Empty Cell Settings" won't work either, since these aren't empty cells.
Apart from creating another column, there are a couple of alternatives that you can think of.
Possible solutions:
Option 1 - The solution to this is what was actually considered as an issue in your provided link. If the "Series" column contains #N/A instead of #N/A N/A as shown in your screenshot, Excel Graph will ignore those items and will display what is desired. If this column contains formula such as
VLOOKUP, it should automatically generate "#N/A" for errors and the graph will ignore that.Option 2 - The other option being not to show the date if the "Series" column data is not a number. So if the cell address with the date "28/12/2016" is
A3, replace it with the formula=IF(ISNUMBER(B3),A2-1,"")and copy it to rest of the rows below.