Removing #N/A from the X Axis of any chart Automatically

[dharmon]

I have a table with #N/A values. The #N/A values must be in the range selection for the chart (below the table). I cannot rely on filtering for my project. The #N/A and associated column space shows up in the chart. It needs to go away so only customers with real values are shown.

Please help.

Thank you kindly,

[southward]

Hi,

At the end of your formula you have "NA()". Replace it with "".

Cheers

[dharmon]

I've tried that. However, despite it getting rid of the "#N/A" in the X axis legend, it does not get rid of the associated chart space/columns. Thus leaving the chart at the same size it was before where the real data is plotted to the far left and 30 blank columns remain to the right.

The associated blank chart column areas alsChart 1.pngChart 2.pngo need to go away so that all you see is the real data in the colored bars.

[MrShorty]

In my limited experience with category axes, there is not good way to deal with "empty" categories. Those solutions that I have seen have all resorted to getting all of the meaningful categories together in a single block (all the N/A's at the end) and using "dynamic named ranges" to identify the meaningful (non-N/A) blocks of data. http://www.excel-easy.com/examples/d...med-range.html

[dharmon]

I have encountered the same "dynamic named ranges" solution across multiple sources. However, as easy as it is to set up a dynamic named range, I have been thus far unable to apply it to a chart.

How do you complete the process, from establishing a dynamic named range on a column through to it being applied to a chart?

[MrShorty]

This http://peltiertech.com/dynamic-charts/ shows how to use the named range in charts.

[dharmon]

The link you provided was exactly what I needed!

Thank you much!