Creating a chart in Excel that ignores N A or blank cells

Question

I am attempting to create a chart with a dynamic data series  Each series in the chart comes from an absolute range  but only a certain amount of that range may have data  and the rest will be  N A   The problem is that the chart sticks all of the  N A cells in as values instead of ignoring them  I have worked around it by using named dynamic ranges  i e  Insert   Name   Define   but that is extremely inefficient  as each chart has 4 dynamic series  and I must make 25 of these charts   Are there any other solutions that allow me to specify a range  as normal  for a data series  but tell the chart to ignore all   N A  or blank cells

User · Answer

just wanted to put my 2cents in about this issue...

I had a similar need where i was pulling data from another table via INDEX/MATCH, and it was difficult to distinguish between a real 0 value vs. a 0 value because of no match (for example for a column chart that shows the progress of values over the 12 months and where we are only in february but the rest of the months data is not available yet and the column chart still showed 0's everywhere for Mar to Dec)

What i ended up doing is create a new series and plot this new series on the graph as a line chart and then i hid the line chart by choosing not to display the line in the options and i put the data labels on top, the formula for the values for this new series was something like :

=IF(LEN([@[column1]])=0,NA(),[@[column1]])

I used LEN as a validation because ISEMPTY/ISBLANK didn't work because the result of the INDEX/MATCH always returned something other than a blank even though i had put a "" after the IFERROR...

On the line chart the error value NA() makes it so that the value isn't displayed ...so this worked out for me...

I guess it's a bit difficult to follow this procedure without pictures, but i hope it paints some kind of picture to allow you to use a workaround if you have a similar case like mine

User · Answer

Best way is use Empty  Dim i as Integer For i   1 to 1000     If CPT DB Cells i  1   gt  100 Then        CPT DB Cells i  2    CPT DB Cells i  1      Else        CPT DB Cells i  2    Empty                              End If Next i

User · Answer

If you use PowerPivot and PivotChart  you will exclude non-existing rows

User · Answer

I had a similar issue using an X Y chart but then also needed to calculate the correlation function on the two sets of Data      IF A1 gt A2 A3  N A  allows the chart to be plotted but correlation of X  amp  Y fails   I solved this by  IF A1 gt A2 A3 FALSE   The FALSE can then be removed using conditional formatting or other tricks

User · Answer

I had the same problem with a full line appearing   The end of my data only had  N A   When I changed the chart type from a stacked line to line then the end of the line was removed from the chart   This did not work if the  N A was in the middle of the data  only when it was in the trailing data

User · Answer

One solution is that the chart graph doesn t show the hidden rows   You can test this features doing  1 right click on row number 2 click on hide   For doing it automatically  this is the simple code   For Each r In worksheet Range  A1 A200      If r Value      Then        r EntireRow Hidden   True     Else         r EntireRow Hidden   False Next

User · Answer

Select the labels above the bar  Format Data Labels  Instead of selecting  VALUE   unclick   SELECT Value from cells  Select the value  Use the following statement  if cellvalue       cellvalue  where cellvalue is what ever the calculation is in the cell

User · Answer

When you refer the chart to a defined Range  it plots all the points in that range  interpreting  for the sake of plotting  errors and blanks as null values      You are given the option of leaving this as null  gap  or forcing it to zero value  But neither of these resizes the RANGE which the chart series data is pointing to   From what I gather  neither of these are suitable     If you hide the entire row column where the  N A data exists  the chart should ignore these completely   You can do this manually by right-click   hide row  or by using the table AutoFilter   I think this is what you want to accomplish

User · Answer

You can use the function   IF ISERROR A1  0 A1   this will show zero if the cell A1 contains an errore or the real value if it doesn t

User · Answer

This is what I found as I was plotting only 3 cells from each 4 columns lumped together   My chart has a merged cell with the date which is my x axis   The problem  BC26-BE27 are plotting as ZERO on my chart  enter image description here I click on the filter on the side of the chart and found where it is showing all the columns for which the data points are charted  I unchecked the boxes that do not have values  enter image description here It worked for me

User · Answer

There is another much less known solution which has merit in some places   Use the  NULL  error which does not plot and does not create a zero point for the plot   So your plot column is filled with the following construct  Assume real data in column A and that the plotted version is in column C    If Isnumber A2  A2 A2 A3   That space is a little known operator to produce the intersection of the two cell addresses or ranges  If there is no intersection then it produces the message  NULL   Cells containing  NULL  are skipped for plotting purposes   Aside  For Microsoft  just need to get the maths equations to ignore such cells somehow

User · Answer

While this is an old post  I recently came across it when I was looking for a solution to the same issue  While the above solutions do prevent charts from plotting data  when source cells are  N A or made to look blank   it doesn t resolve the issue of the chart data labels themselves still showing a zero label    I had searched and searched and almost given up  when I came across the solution posted online   https   www extendoffice com documents excel 2031-excel-hide-zero-data-labels html  It worked liked a charm  Attached is an image showing an example of how the data  labels  displayed before  Chart 1  and how it displays using this tip chart 2   enter image description here

User · Answer

I found a way to do it   you can do an x y scatterplot   it will ignore null records  i e  rows

User · Answer

Please note that when plotting a line chart  using  NA    output  N A  to avoid plotting non existing values will only work for the ends of each series  first and last values  Any  N A in between two other values will be ignored and bridged

User · Answer

You are correct that blanks    or a string   N A  are indeed interpreted as having values in excel  You need to use a function NA

User · Answer

I was having the same problem   There is a difference between a Bar chart and a Stacked Bar chart  As there is a difference between a Line chart and a Stacked Line chart   The stacked one  will not ignore the 0 or blank values  but will show a cumulative value according with the other legends   Simply right click the graph  click Change Chart Type and pick a non-stacked chart

User · Answer

If you make the vertical scale on your chart  using format axis  run from 0 0001  say  then a value that Excel thinks is zero will not be plotted   Your axis in the chart will still look like it runs from zero upwards

User · Answer

I was having the same issue by using an IF statement to return an unwanted value to     and the chart would do as you described    However  when I used  N A instead of     important  note that it s without the quotation marks as in  N A and not   N A    the chart ignored the invalid data   I even tried putting in an invalid FALSE statement and it worked the same  the only difference was  NAME  returned as the error in the cell instead of  N A   I will use a made up IF statement to show you what I mean    IF A1 gt A2 A3       --- gt  Returned    into cell when statement is FALSE and plotted on chart        this is unwanted as you described    IF A1 gt A2 A3   N A     --- gt  Returned  N A as text when statement is FALSE and plotted on chart        this is also unwanted as you described    IF A1 gt A2 A3  N A    --- gt  Returned  N A as Error when statement is FALSE and does not plot on chart  Ideal    IF A1 gt A2 A3 a    --- gt  Returned  NAME  as Error when statement is FALSE and does not plot on chart       Ideal  and this is because any letter without quotations is not a valid statement

User · Answer

If you have an x and y column that you want to scatterplot  but not all of the cells in one of the columns is populated with meaningful values  i e  some of them have  DIV 0    then insert a new column next to the offending column and type  IFERROR A2   N A   where A2 is the value in the offending column    This will return  N A if there is a  DIV 0  and will return the good value otherwise  Now make your plot with your new column and Excel ignores  N A value and will not plot them as zeroes    Important  do not output   N A  in the formula  just output  N A

[excel] Creating a chart in Excel that ignores #N/A or blank cells

Examples related to excel