Excel: Prevent the Drop to Zero

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: I've built formulas to create a chartable range. I want to show how the fund balances have been increasing for three years. All of a sudden, the points for future months look like someone spent all the money, dropping to zero.

  1. Prevent the zero points for the future.

    Strategy: Zero cells will be plotted. Empty cells or #N/A! cells will not be plotted. Change your formulas to put an NA() instead of 0. Say that you are building the chart range with =SUMIF(). Start to use =IF(SUMIF()=0,NA(),SUMIF()). The chart will look much better.

  2. Use #N/A instead of zero.

    It is ironic that 99% of the time, you are trying to avoid the #N/A. In this case, however it solves the problem.