How to make sure a bar chart does not plot my zero-results (of yet to come data)

[Anoebis]

I'm going crazy... I have been looking for hours but didn't find any solution.

So, I have a very simple XY data graphic which is viewable as a bar chart.

X-axis are numbers in this structure: =SUM(B1135:B1146)
Y-axis are months

All my future months are now plotted as zero as there is no data yet, and I don't want to make a news graph every month.

Can anyone help me how to make sure the zero's of data that will still come are NOT viewable? Of course it must be possible to add data in the future, so the graphic becomes bigger automaticaly.

Thanks
Joske

[noboffinme]

Hi Anoebis,

If you are charting the whole dataset (including future data) where there is no data yet, you could try using OFFSET in the data that your chart reads so that as you add data the chart autoupdates.

Have a read of this;

http://spreadsheetpage.com/index.php...nter_new_data/

[Anoebis]

Nope, it is not what I am looking for... basically this is the info that use for my graph. As said, every nr is a SUM code (stuff like this) =SUM(B1015:B1026)

The data I want to plot in a bar chart:

Total 2004 356
Total 2005 865
Total 2006 1226
Total 2007 1718
Total 2008 1724
Total 2009 2135
Total 2010 1626
Total 2011 1319
Total 2012 1345
Total 2013 1069
Total 2014 1049
Total 2015 0
Total 2016 0
Total 2017 0
Total 2018 0
Total 2019 0
Total 2020 0


It is very easy to make a graph of course, but the data will be updated automatically in the future. So, the problem:

I have a graph now that goes untill 2020 with 6 empty years (useless). I need a graph where they are unviewable, and once there is data (so, other then zero) the bar should be viewable...

Please help

[Anoebis]

Oops... Double post, internet was doing weird (I'm in an airport...)

[Anoebis]

Oops... Double post, internet was doing weird (I'm in an airport...)

[MrShorty]

The technique of dynamic named ranges described in noboffinme's link is the suggestion most people use for this kind of thing. You may need to use something other than the COUNTA() part of the function -- maybe a COUNTIF(), but the basic idea should be the same. Was there something about defining and using dynamic named ranges that you did not understand?

[Pete_UK]

A simple way is to apply a filter to your totals column and filter out the zeroes - then you can re-apply the filter when you have new data (as it is not dynamic).

Hope this helps.

Pete