When data points are bad or missing, how do you create a usable and decent looking Excel Chart?

Excel

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.

Charts with a zero data point drop the line to the X-axis.

Charts with a zero data point drop the line to the X-axis.

 

 

 

 

 

 

 

 

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.

Charts with a zero data point drop the line to the X-axis.

Charts with a missing data point show a blank line at the point of missing data.

 

 

 

 

 

 

 

 

 

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.

Entering an NA() function as chart data creates a continuous line.

Entering an NA() function as chart data creates a continuous line.

 

 

 

 

 

 

 

 

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,

=IF(B2=0,NA(),B2)

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.

Share the power...