Print this article | Return to Article | Return to CFO.com
Are you thwarted by an Excel chart that drops to zero – and off the charts – when data is not available for the current period? Here's a quick fix.
Bill Jelen, CFO.com | US
September 8, 2011
Editor's Note: If you would like Bill Jelen to answer your Excel question in an upcoming column, send an e-mail note to billjelencfo.com. If he chooses your question as a topic for his column, you'll win a book from CFO and MrExcel.com.
Reader Jeff E. sent in this week's question: "I was sitting in a meeting where a manager was bragging that they've managed to make money every month in a fund that was traditionally a money-loser. The chart showed a steady progression of the fund, month-by-month for the last four years - until the chart arrived at the current month. The line then promptly dropped to zero. I had to laugh as I penciled in a caption on the chart '...until someone apparently absconded with all the money last week....' How should we deal with the situation where we don't have the data for this month or future months? How do we prevent the chart line from dropping to zero?" (See Figure 1.)
This usually happens when the chart data is spread throughout the workbook. Someone builds a nice range for the chart data and then uses formulas to grab the correct values from the various worksheets into a nice contiguous block for the chart. This makes sense, because it is far easier to create an Excel chart when the data is in a contiguous block.
However, the problem is in the future months. If there is no data for the future months, the formulas are going to bring forward a zero, even if the source cell is empty (see Figure 2).
The solution is to adapt the formula to detect the zero and to replace it with an #N/A! error. This seems counterintuitive - you usually try to avoid the #N/A! errors. However, in a chart, using the #N/A! error will cause that point to not be charted.
How do you introduce an #N/A! error on purpose? Use the =NA() function. The new formula is shown in Figure 3.
After using the new formula, the chart now correctly shows the fund balance increasing (see Figure 4).
Bill Jelen is the host of MrExcel.com. His 33rd book on Excel, Learn Excel 2007-2010 From MrExcel, includes 511 more tips like this one. Win a book if your question is chosen as the topic of a future column. To send a question, simply send an e-mail note to billjelencfo.com.