You may have faced the problem of having a chart where some of the data doesn’t exist or it’s entered as zero to represent missing data. When faced with these “holes” how can you create a valid and decent looking chart?
Perhaps the worst case is when data is missing or invalid and that data point in your Excel sheet contains a zero. I’ve seen this happen from the output of electronic test equipment. It looks like the chart below, terrible.
Using a zero to replace bad or missing data causes the line to nose-dive to zero and then rocket back up at the next data point.
Another problem happens if instead bad or non-existent data appears as a blank in the spreadsheet. That blank data point isn’t plotted in Excel 2010 and Excel 2013. While this gives a more valid presentation of the data there are cases where you want to show a trend in the data and therefore show a line that continues between the two known points.
Using a Blank Cell for bad or missing data causes the line to disappear between data points.
In the case where you want to create a continuous line and ignore the blank or zero cell, enter =NA() in the cell. Entering the formula =NA() in a cell connects the line between the previous and next data points. The =NA() function results in #N/A showing in the cell.
So what do you do if you have 546 cells of data in a column and they randomly contain a zero or blank that you want replaced with =NA()? Use an IF() function to find the errant cells and replace them with =NA(). For example, if the data in your column starts at B2, then in C2 enter the formula,
Copying this formula down column C fills column B with data and substitutes NA() for the zeroes and blanks. You can either use column C data for your chart or copy and Paste-Value the column C results over the original data in column B.