don't plot null "" value in bar chart

**maacmaac** · 07-25-2010, 04:30 PM

I am trying create a bar chart based on following data

mon = 12
tues = 22
wed = ""
thurs = 30
fri = 18

I have created the chart but the value for wed continues to show up in the chart. The value for wednesday is null/empty, which means, for example, the office was closed that day. When I create the chart, I only want to see mon, tues, thurs, and fri along the x axis. Is there a way to not plot if the value is null without having to delete or hide the column for wed? Thanks in advance for any comments.

**teylyn** · 07-25-2010, 04:57 PM

Is there a way to not plot if the value is null without having to delete or hide the column for wed?

In short, no. If there is a category, it will be plotted. If you hide it, it won't.

The only way to avoid deleting or hiding the data is to build a new table that contains only the data you want to plot and build the chart off that.

**quekbc** · 07-25-2010, 07:27 PM

Not sure if this helps, Excel doesn't plot

=NA()

functions. I use that instead of "" when it comes to charting.

**teylyn** · 07-25-2010, 09:59 PM

quekbc, you will still see an empty category for that value. In order to prevent an empty category, you need to either hide the value or create a table that has only the data you want to plot.

**maacmaac** · 07-25-2010, 10:13 PM

I am getting the same charting result whether I use "" or =NA(). I searched some other forums and getting same answer that teylyn mentioned. It does appear you can't prevent an empty category from appearing in chart. If anyone can think of anything thing else I could try, much appreciated. Attached is chart example with desired result if needed.

**quekbc** · 07-25-2010, 11:25 PM

Ahh, my bad, didn't see that it's a bar chart. The NA thing worked for a scatter plot.

As teylyn mentioned, I'd create another table to address it... and then maybe use a dynamic named range to only take relevant values.