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:
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)