Search code examples
excelplotexcel-charts

Excel chart shows wrong data when zero-length string "" is in data

I am trying to make a scatter plot in Excel for Mac which should look like this:

enter image description here

I am using an IF formula which returns "" when my x-data is negative or 0 and then using this new column to make the plot. However, all the x-values in the plot turn out to be very different as here:

enter image description here

When I manually delete all the "" from the column, I get the correct plot. If I just add one "" in the column all the x-values change in the plot. I see the correct x-values in the spreadsheet column.

How can I get Excel to ignore the "" when plotting or plot the values that are shown? What is causing this error?

Solution

  • You can replace "" with NA() in your IF formula to avoid plotting these values. Excel charts ignore #N/A cells. In your case, assuming your x-values are in column A, the formula in the new column (for plotting) is =IF(A2<=0,NA(),A2).