45
votes

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?

20
can't you as part of the chart (like in a pivot chart) tell it to ignore blanks as part of the data selection for the chart?xQbert
Do you have the same problem with blank cells as you have #N/A cells? I believe that excel ignores blanks by default (see 'Hidden and empty cells' submenu under 'Select Data Source'). As for the #N/A, can you just make your chart from a derived column, where you use IFERROR to change #N/A cells to blank?Tim
Yes, I can set it to ignore blanks, but the problem is that I don't know how to make the cells truly blank. The cells have a formula in them that can output "#N/A" or "", and the chart doesn't consider "" as blank. Is there a way to output a truly blank value from a formula?James Skidmore
Excel 2013 allows you to filter a chart's data without messing with the worksheet. In this case you'll be able to block plotting of the category with the errors. I don't know if you can do it dynamically, since the UI for it has boxes for you to check. No help if you're stuck with 2007/10.Jon Peltier
You should use =NA(), not "#N/A".Pedro77

20 Answers

32
votes

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>A2,A3,"")  
---> Returned "" into cell when statement is FALSE and plotted on chart 
     (this is unwanted as you described)

=IF(A1>A2,A3,"#N/A")  
---> Returned #N/A as text when statement is FALSE and plotted on chart 
     (this is also unwanted as you described)

=IF(A1>A2,A3,#N/A)  
---> Returned #N/A as Error when statement is FALSE and does not plot on chart (Ideal)

=IF(A1>A2,A3,a)  
---> 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)
21
votes

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.

Chart with #N/A values as null

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.

Chart hidden/empty cell options

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.

Chart with hidden source data rows ignores hidden data

14
votes

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.

Example Plot Here

7
votes

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().

6
votes

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.

2
votes

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.

2
votes

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.

1
votes

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

1
votes

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
1
votes

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?

1
votes

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.

0
votes

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>A2,A3,#N/A) allows the chart to be plotted but correlation of X & Y fails.

I solved this by =IF(A1>A2,A3,FALSE)

The FALSE can then be removed using conditional formatting or other tricks

0
votes

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.

0
votes

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

0
votes

I found a way to do it.

you can do an x,y scatterplot. it will ignore null records (i.e. rows)

0
votes

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.

0
votes

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.

0
votes

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

0
votes

I found the following solution:

The Excel charts adjust automatically to the correct axis ends if all the date series cells which should not be included in the axis timeframe contain "". So, you can make a helper column and write something like this into the Date series helper column cells:

Helper Column Datecell = If( iserror (ValueCell);""; Datecell)

On the other hand, if you want to resize your chart to a given timeframe you can write something like this into the helper column date series cells:

Helper Column Datecell = IF(  OR(ISERROR(ValueCell); DateCell>DateInput)   ;"";DateCell)

DateInput is a cell where you input a date to resize the chart.


There is another topic here:

Regarding that if you make a helper column to a value series cell giving an NA() Error the value series adjusts to your series as well: you could even make your value series cells dependent on your given input cell. I think this is also needed.

Helper Column Value Cell: if( condition that date cell is too big or bigger than a given input cell; NA(); ValueCell)
-1
votes

Best way is use Empty

Dim i as Integer
For i = 1 to 1000
    If CPT_DB.Cells(i, 1) > 100 Then
       CPT_DB.Cells(i, 2) = CPT_DB.Cells(i, 1)
    Else
       CPT_DB.Cells(i, 2) = Empty //**********************
    End If
Next i