Excel Line Chart: How to prevent the line from dropping to zero

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)

One thought on “Excel Line Chart: How to prevent the line from dropping to zero

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s