I don't want series with #N/A or NA() values plotted. Excel normally should ignore #N/A values and not plot them.

[wefokjnewkfewknf]

http://s17.postimg.org/7gfqc615r/excel.png


I don't want series with #N/A or NA() values plotted. Excel normally should ignore #N/A values and not plot them. Unfortunately Excel is deciding to plot #N/A as zero values . What's going on? Picture is linked to above.

 

[martindwilson]

Re: I don't want series with #N/A or NA() values plotted. Excel normally should ignore #N/A values and not plot them. Unfortunately Excel is deciding to plot #N/A as zero values. What's going on? File is attached, and I also did a screen capture whic

fair comment

 

[wefokjnewkfewknf]

Re: I don't want series with #N/A or NA() values plotted. Excel normally should ignore #N/A values and not plot them. Unfortunately Excel is deciding to plot #N/A as zero values. What's going on? File is attached, and I also did a screen capture whic

Peter_SSs did add information that was not on the other thread. Thank you Peter. As far as the minutiae with rules and etiquette I apologize if I broke one. I'm not a veteran here. I'll go read them now.

With this new information my question will change to, how do I get Excel to ignore my series titled Mango. Since there are only #N/A values in this series I don't want blank spaces plotted. I have a spreadsheet with thousands of categories that are dynamic. On any given day only 5 will contain values and the other thousands will contain #N/A. Daily, I need to plot the 5 categories with values as a status report to my team. It messes up the chart for Excel to plot out the other 2,000 "Mango" #N/A categories. Makes it unreadable.

 

[wefokjnewkfewknf]

Re: I don't want series with #N/A or NA() values plotted. Excel normally should ignore #N/A values and not plot them. Unfortunately Excel is deciding to plot #N/A as zero values. What's going on? File is attached, and I also did a screen capture whic

I tried changing values to 0 and formatting as 0;-0;;@ but that didn't help either

 

[shg]

Surely this has been suggested: If you autofilter to exclude the rows with all blanks, the categories will not appear.

 

[Peter_SSs]

Re: I don't want series with #N/A or NA() values plotted. Excel normally should ignore #N/A values and not plot them. Unfortunately Excel is deciding to plot #N/A as zero values. What's going on? File is attached, and I also did a screen capture whic

On any given day only 5 will contain values and the other thousands will contain #N/A. Daily, I need to plot the 5 categories with values as a status report to my team.

Then another option would be to create another table with just the relevant value and use that as the chart data source.

G2 is copied down to the bottom of the original table.
I2 is copied across to M2 & down 5 rows.
I1:M6 is used as the chart data source.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		MON	TUE	WED	THU					MON	TUE	WED	THU
2	Oranges	1	3	6	0		1		Oranges	1	3	6	0
3	Bananas	#N/A	#N/A	#N/A	#N/A				Pears	0	4	0	2
4	Pears	0	4	0	2		2		Grapes	0	1	3	0
5	Grapes	0	1	3	0		3		Kiwi	2	0	5	3
6	Mango	#N/A	#N/A	#N/A	#N/A				Melon	0	2	0	1
7	Kiwi	2	0	5	3		4
8	Melon	0	2	0	1		5
9
Chart To Ignore #NA Rows

 

[wefokjnewkfewknf]

Re: I don't want series with #N/A or NA() values plotted. Excel normally should ignore #N/A values and not plot them. Unfortunately Excel is deciding to plot #N/A as zero values. What's going on? File is attached, and I also did a screen capture whic

Thank you Peter. That solution will work.

 

[Peter_SSs]

Re: I don't want series with #N/A or NA() values plotted. Excel normally should ignore #N/A values and not plot them. Unfortunately Excel is deciding to plot #N/A as zero values. What's going on? File is attached, and I also did a screen capture whic

Thank you Peter. That solution will work.

No problem. Thanks for letting us know.