Don't Chart/Graph Cells With Formula That Returns Blank

[xtinct2]

Hi,

I have a line chart with an x-axis of Jan-Dec.
The data that the chart is pulling from will be updated each month
For months with no data (i.e. future months) I don't want the chart to plot those (non-existent) points -- so I'd like for the chart to cut off after the current month.

-I first had the formula for future months result in 0, but the chart plots it at 0 (makes sense)
-I then had the formula for future months return "" since I know a chart doesn't plot blanks, but the chart plots it at 0
-I then had the formula for future months return #N/A since I know a chart doesn't plot blanks, but the chart plots it at 0

I'd rather not make the cells truly blank b/c that would involve me having to drag over the formula each month -- and I'd rather set it and forget it.

Is this possible?

Thank you so much!

 

[pgc01]

Hi

Just define a dynamic range that does not include future months, and that will automatically update when you include the value for a new month.

This is an example by Jon Peltier:

Create a Dynamic Chart

 

[xtinct2]

Hi

Just define a dynamic range that does not include future months, and that will automatically update when you include the value for a new month.

This is an example by Jon Peltier:

Create a Dynamic Chart

Thanks

However, I don't want to resize the chart -- i want to show the fwd months, but have the line end at the current month

Still possible?

 

[thisoldman]

With Excel 2013 I can get data to display as you want on the chart by using =NA() for the future months.

Select the chart. From the right-click pop-up menu, click on 'Select Data...'. In the lower left-hand corner of the Select Data Source dialog box, click on the button labeled 'Hidden and Empty Cells'.

In the new pop-up dialog, select 'Show empty cells as: * Gaps', then click 'OK'.

I think there's a similar setting available in 2010. I'm pretty sure Excel versions released after Excel 2013 also have that same setting.