Hide 0 values in excel chart

[Gabriell]

Hi Experts!

I want to create a chart in Excel and hide the values that are zero.

The percentage is calculated using a formula. If I replace zero values with =NA(), the percentage cannot be calculated, and the chart becomes empty.

Is there any solution to fix this?

Thank you in advance!
Gabor

 

[saboh12617]

Hi,
What if you just put a 0 for the error values in the sum. Like so:

=B21/SUM(IFERROR(B21:B38, 0))

 

[Gabriell]

Nothing will change...

 

[saboh12617]

Do not put the IFERROR before the whole, only in the SUM. You want to keep the error in column B.

Two methods :
- Either you filter your table without the #N/A.
- Or you use a dynamic range based on the FILTER function, again to remove #N/A as a base. You need to make 2 named ranges pointing to this two FILTER columns to use them as a graph source.

 

[Gabriell]

Do not put the IFERROR before the whole, only in the SUM. You want to keep the error in column B.

Two methods :
- Either you filter your table without the #N/A.
- Or you use a dynamic range based on the FILTER function, again to remove #N/A as a base. You need to make 2 named ranges pointing to this two FILTER columns to use them as a graph source.

If I use filter, it works, but I'd like to avoid the extra work.
Can you give me please, more details about the 2nd option?

 

[saboh12617]

Okay so the procedure is as follows:

You need two empty columns, in one you write the formula (replace Table1 with the name of your table) :

(1) =FILTER(Table1[Segment], NOT(ISERROR(Table1[QTY 2025])))

And in the other one

(2) =FILTER(Table1[%], NOT(ISERROR(Table1[QTY 2025])))

Then you go to the name manager, and you give the first cell of each of this columns a name. For instance if formula (1) is in F2, you define its name like so (put the name you want). BE VERY CAREFUL TO MANUALLY ADD THE "#" AT THE END of "refers to".

Do the same for the other formula.
You have two named ranges : let's say graph_leg for formula (1) and graph_perct for formula (2).

Now in your graph, go to the Select Data Source => Edit Legend Entries, and write the name you defined for formula (1). You need to put in front of it, either the name of the workbook, or the name of the worksheet. Like below for Sheet/Workbook named "Book2".

Do the same for Horizontal Axis Labels with formula (2).

Now your graph is dynamic \o/.

 

[Peter_SSs]

What about something like this?

25 04 04.xlsm
	A	B	C
1
2	A	5	50%
3	B	#N/A	#N/A
4	C	1	10%
5	D	#N/A	#N/A
6	E	2	20%
7	F	#N/A	#N/A
8	G	#N/A	#N/A
9	H	2	20%
10	I	#N/A	#N/A
Chart

Cell Formulas
Range	Formula
B3,B10,B7:B8,B5	B3	=NA()
C2:C10	C2	=B2/AGGREGATE(9,6,B$2:B$10)

 

[saboh12617]

Hi Peter, the result is the same as my 1st post. I think the op wants to disable the legend for #NA values. (so in your case the graph legend should only contain A,C,E,H). Hence the filter & named ranges.

 

[Peter_SSs]

Hi Peter, the result is the same as my 1st post. I think the op wants to disable the legend for #NA values. (so in your case the graph legend should only contain A,C,E,H). Hence the filter & named ranges.

Sure, just a different formula to produce the table results. I was hoping that actually showing the data, formula results and chart image would show it working.
The OP's image in post #3 has red arrows pointing at the 0% sections of the chart, not at the legend, so I presumed those red arrows were showing that they wanted the chart sections removed.

 

[Sam_D_Ben]

Something like this,

=IF(ISNUMBER(B21), B21/SUMIFS($B$21:$B$38, $B$21:$B$38, ">0"), NA())