Creating a chart in Excel that ignores #N/A (or blank cells or zero cells)

[Manojlo]

I create a chart, that is have same number of rows (for example 18) but not every rows are filled with information. I create Pareto diagram, and I want to avoid chart to show me empty rows, I put in empty rows #N/A value or blank or zero value, but always I see on chart empty rows.

Is there possibility to see on chart just information without empty rows.

I tried options Select Data / Hidden and Empty Cells, but with no results.

Thank you in advance

 

[GraH]

Hi,

I guess you can use dynamic ranges for this purpose.

Book1
	I	J
3	Country	Cum
4	us	25
5	uk	43
6	russia	59
7	canada	70
8	china	80
9	japan	87
10	#N/A	#N/A
11	#N/A	#N/A
12	#N/A	#N/A
13	#N/A	#N/A
14	#N/A	#N/A
15	#N/A	#N/A
16	#N/A	#N/A
17	#N/A	#N/A
Sheet1

Cell Formulas
Range	Formula
I10:J17	I10	=NA()

Country=Sheet1!$I$4:INDEX(Sheet1!$I$4:$I$17,MATCH("zzz",Sheet1!$I$4:$I$17))
Cum=Sheet1!$J$4:INDEX(Sheet1!$J$4:$J$17,MATCH(9^103,Sheet1!$J$4:$J$17))

Your chart will "grow/shrink" when new data is added/removed.

Repeat the logic for all your chart elements. I was not going to type over all your data. Consider using XL2BB in future posts.

 

[Manojlo]

@GraH Thank you for advance about using XL2BB in future post.

It is complicate for me to understand your explanation above using logic for chart elements, I can not edit Name Manager.
Can you please explain me step by step.

Thank you in advance.

 

[Manojlo]

@GraH Can you send me attached excel file with example?

Best regards

 

[GraH]

Hi, we can't share workbooks (forum rules/features)
Please take a look at a few tutorials

How to Create a Dynamic Chart Range in Excel

Learn how to create a dynamic chart range that instantly updates Excel charts when the data changes. Done using OFFSET and INDEX function.

trumpexcel.com

How to create a dynamic named range with INDEX

In this video we'll look at how to create a dynamic named range with the INDEX function. INDEX is a non-volatile function, so it won't recalculate whenever a change is made to a worksheet.

exceljet.net

Last row in text data

To get the last relative position (i.e. last row, last column) for text data (with or without empty cells), you can use the MATCH function. In the example shown, the formula in D5 is: =MATCH(REPT("z",255),B4:B11)

exceljet.net

Last row in numeric data

To get the last relative position (i.e. last row, last column) for numeric data (with or without empty cells), you can use the MATCH function with a so-called "big number". In the example shown, the formula in E5 is: =MATCH(9.99E+307,B4:B9)

exceljet.net