Excel chart - suppressing forecast periods
I have an Excel chart showing results by month for 2010. At the moment, I have data for Jan & Feb, and blank cells for the remainder of the year. I'd like the chart to show actual results only, for those months where results are available. Leaving the cells for the rest of the year as text, blank or zero gives me a chart that drops to the baseline, which is not acceptable.
It seems the only way to do this is to go into the chart and change all the cell references each month. Is there a better way to do this?
Alan
Chart options for empty cells
For certain types of chart, including line charts, you can also choose how empty cells (as opposed to zeros) are plotted in a chart. Select the chart then go to Tools, Options, Chart - you can then choose between: not plotted, zero or interpolated. In Excel 2007 the option is within the Chart Tools, Design tab, Data group, Select Data, Hidden and Empty Cells button.
Use NA()
Hi Alan
Rather than leaving the future data blank or zeros, use a function like:
=NA()
to return the #N/A error value which won't be plotted on the chart.
Richard