Excel 2007 : Hide Empty Cells when Plotting a Graph

I've read a lot of answers to this question, which all suggest using =na() so that the graph doesn't plot any data for the relevant cells, however, despite having #N/A in the relevant cells, even though nothing visable is plotted, the plotted graph data is still pushed to one side and if showing a legend, items still appear for the #N/A entries.

For example if the first four rows of data contain =na() producing a #N/A in those cells and the next six rows contain actual data, when the graph is plotted, the six plotted entries are pushed to the right because allowance is still being made for the first four rows.

Is there any way to completely not plot cells containing #N/A (as if they weren't in the data set at all and for the #N/A cells not to appear in the legend?

Hi benishiryo,

Many thanks for your sample. This works perfectly and does exactly what I need it to do, however, having applied the same logic to my spreadsheet, the only thing my version isn't doing is to change the 'Chart data range', which means that not all the data points are being plotted. I've been looking at this for the last few hours and I can't see any difference between your version and mine, but I'm obviously missing something. I've attached my version to see if you can spot any errors.

You will see, that despite either 'Region' or 'Area' being selected in cell C4, even though the table updates correctly and the COUNT is correct between the two data sets, the graph only ever plots four data points, rather than adjusting to the correct number of data points.

What am I doing wrong here?

Andy

Thanks for your input, that is useful to know as I hadn't realised that the na() trick only applied to line series, which accounts for why I was unable to get this to work correctly. Benishiryo's solution, overcomes this problem perfectly and is very neat. Now, if I could only figure out where I'm going wrong. Can you see the error in my formula. I've been staring at it for a few hours now and I still can't spot the error.

Many thanks

Hi Andy

Sorry (for being thick), are you referring to the fact that my version uses two sets of data, i.e. 2010/2011 and 2011/2012 or the fact that one set of data, namely 'Region' has four data points and the second set of data 'Area' has ten data points? I'm not quite sure I follow.

Hi Andy (again),

I can actually get it to work (sort of), but I get the following error in the 'Select Data Source' dialogue window. 'The data range is too complex to be displayed. If a new range is selected, it will replace all of the series in the series panel'. I'm unsure why excel thinks the data is to complex as it is being taken from a simple table! I'm clearly missing something here, but I'm not quite sure what? I guess it goes back to your comment, however benishiryo's sample works perfectly. What is the fundamental difference between benishiryo's sample and my version - I think this must be what I'm missing?

Is there any work around or a different approach that could be adopted to achieve what I'm trying to do or am I on to a non-starter?

Andy,

Okay, I understand what you're saying now and I can see how I can get this to work, it just means the data will be dispalyed differently... Many thanks for your help and input.

benishiryo,

Yes, I finally figured it out... Andy pointed out the error in my logic, so I'm simply displaying the data the opposite way and your solution works perfectly, so many thanks for taking the time to help. It is greatly appreciated.