Excel - dynamic chart x axis - ignore x categories with no data

Question

I have a predefined list of x labels, e.g. months of the year. I want to dynamically exclude some of these from my excel plot, e.g. if some of the y values are blank, zero or errors.

Things I've tried:

• If I use an (if data is bad, then change xlabel to blank or NA()) process, excel still leaves a space for the blank or #N/A x label - see image. This is the second most upvoted answer to Creating a chart in Excel that ignores #N/A or blank cells, but doesn't quite do what I'm after... Unfortunately, there is no option for 'No Gaps' in the Hidden and Empty Cell Settings.

• If I filter out rows where the Y values meet the reject condition, I get the chart I want, but the filter doesn't update automatically when the Y values get changed by a calculation elsewhere, ie its not dynamic. Pivot charts are the same.
• I can't get formulaic named ranges (Excel charts - setting series end dynamically) to do what I want - the formula will return something like 'Jan, FALSE, Mar, Apr' rather than 'Jan, Mar, Apr', and we end up with the same problem as in the picture above.

Non-VBA solutions preferred = )

Answer 1

Buddy, seems Non-VBA solution is still to be found, as solution lies on hiding the said row. However if you are bent on finding non-vba solution then here is very crude solution - suppose this is the data (C3:I15) with the helper columns which has been described below -

• helper column (hC1) has formula

  =IF(ISNUMBER(D4),1,"")

• hC2 has formula

  =IFERROR(E4*ROW(),"")

• hC3 has formula

  =SMALL($F$4:$F$15,ROW()-3)

  • X has formula

  =IF(ISNUMBER(G4),INDEX(C4:C15,MATCH(G4,F4:F15,0)),"")

  • Y has formula

  =VLOOKUP(H4,C4:D15,2,FALSE)

drag down all the formulas after entering into first rows....

Now you can plot chart from continuous data -