### A new version of this article has been published here: http://easy-excel.com/?p=526

Let’s say you have a table where you enter new sales figures every day, and a line chart connected to it. The sales figures for the last few days in the table have not yet been filled out, so the last six values for the y-axis are zero. This is what the line chart would look like:

How can we get rid of the vertical line that appears after the last data entry?

The y-axis gets its data from column D, Total Revenue, which is the product of Units Sold and Price per Unit (=B2*C2, copied down). We don’t have any entries for the last six days, so the Total Revenue is zero. To Excel, it looks like the sales suddenly dropped from $144 to $0 overnight.

To solve this, we have to **replace the zeroes with #N/A** (value not available). The #N/A message usually appears in lookup queries such as VLOOKUP, MATCH etc., when you search for a non-existing value, but that doesn’t apply here, so we have to cheat!

We’ll use a simple IF-statement in column D: IF UnitsSold = 0, THEN return #N/A, ELSE return TotalRevenue. In Excel syntax:

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

(copy down)

**Problem solved!**

**Note 1:** If you use comma as the default decimal separator (applies to most non-English users) you have to replace the commas in the formulas with semicolons.

**Note 2:** If you have an older version of Excel, the NA() function won’t work. Try this instead: =IF(B2=0,#N/A,B2*C2)

Good tip!