Excel Chart - x axis won't automatically resize when there is no data to display

[Jasesair]

I'm charting very simple data from a set range - charting as a bar graph. This range will include blank/#N/A (I can adjust my formulas to whatever is needed) until new data comes in via formula...it's chronological so I have room for future dates' data.

I'm not able to create a chart that will ignore the currently empty future data. Instead it plots along the x axis leaving me with a condensed graph unnecessarily. Hoping this makes sense! I've tried things and have had no success.

 

[mole999]

if you hide row 129 to 132 that should change it

 

[Jasesair]

if you hide row 129 to 132 that should change it

I'm in need of a different way unfortunately, so that this graphs automatically based on the data in the table. It can't have any user intervention.

 

[mole999]

#N/A should model as nothing, a setting on the graph itself

 

[Jasesair]

Are you able to share how to do this? #N/A hasn't modelled as nothing for me so far. I've attempted an iferror "" added to my formula but this still plots along the x axis.

 

[mole999]

If you select your chart, right click and select data source, the window that opens, bottom left has Hidden and Empty cells, click that and that gives a tock box for Show #N/A as an empty cell

 

[Jasesair]

Ah yes, I found this fantastic sounding solution in my long-winded search only to find it's been removed in an update.

 

[mole999]

Ah yes, I found this fantastic sounding solution in my long-winded search only to find it's been removed in an update.

It would be a fair bet that if you set that in an earlier version it would stay working in your current one, its been around for years, I would suspect that someone moved it

In VBA it would be
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.DisplayValueNotAvailableAsBlank = True

just make sure you select the correct chart