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?

Radicel

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? – Sentimentalize

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? – Killingsworth

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? – Radicel

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. – Nonce

You should use =NA(), not "#N/A". – Busiek

Answer 1

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)

Homogeny

Thank you, this solution works! Note: You can use NA() function to get #N/A value. (office.microsoft.com/en-au/excel-help/…) – Shredding

but what about when the cell itself should still appear empty, not displaying a big #N/A ? – Misspell

Sometimes this might not work for histogram. I found the following sequence works: 1) Set invalid values to 0 (2) Change number of bins (3) Set invalid values to NA. Weird glitch in excel – Snare

Answer 2

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.

Durance

Thank you for the detailed answer, David! Unfortunately this solution will not work. The spreadsheet is set up like this (i50.tinypic.com/2zj9v1u.png), with this set of data repeated horizontally across the worksheet. Each of the 25 charts pulls from these sections. So hiding rows/columns is not feasible, as it would affect the rest of the charts. – Radicel

No problem. Your current spreadsheet doesn't allow you to take advantage of Excel's functionality. Since you are using one spreadsheet to hold series data for all 25 charts, and the data is arranged in the way it is, you're not able to leverage this functionality. You could redesign the spreadsheet layout. Or as you've already mentioned, you could used Named Ranges but that may be cumbersome. Or you could assign non-null values to an array in a macro, then assign that array to the series.values. Is there a 1:1 relationship between columns and charts? This would be fairly easy to do in VBA. – Durance

In the image, the first column is the X-axis labels, and the next 4 are the 4 series in the chart. This block is repeated for each chart. Would the macro be automated so that as the series fills up, there's no need to manually change anything? Would that be less cumbersome than creating 25x5=125 named ranges? Thanks again David! – Radicel

In the screenshot, what is the problem? Are you concerned about column 4 where #N/A begins in March 2013, or are you concerned about the #N/A's *after the January 14 XValue for all columns 2-5??? – Durance

The problem is that the chart reads the #N/A cells in the date column on the left, and makes a ton of #N/A labels on the X-axis instead of ignoring them. Does that make sense? – Radicel

Yes. just change your formula with an IFERROR condition to force the #N/A values to blanks, then the chart should not display these. – Durance

I am using IFERROR to generate the #N/A, but if I change it to output "" instead, Excel does not consider that a blank. Is there a way to truly output a blank cell? – Radicel

I don't think you understand what is happening. Please describe EXACTLY what functionality you want in the chart, for the example data worksheet that you posted. – Durance

Answer 3

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.

Uropygium

Answer 4

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

Eichhorn

Answer 5

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.

Takeoff

Answer 6

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.

Hampshire

Thanks Nicolaesse, but I need zeros to display in the chart as well. So this would not allow me to differentiate between real zeros that should be in the chart, and "error zeros". – Radicel

Answer 7

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.

Extraditable

Answer 8

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

Welterweight

Answer 9

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

Amalburga

Answer 10

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?

Advertise

Sorry - it seems the #Null differs across platforms and my example was a log graph - ignore – Advertise

Answer 11

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.

Ovipositor

Answer 12

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

Burgrave

Answer 13

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.

Intendant

Answer 14

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

Doordie

Answer 15

I found a way to do it.

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

Impanation

Answer 16

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.

Exercise

Answer 17

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.

Greenwell

This answer could be cleaned up a little to make it scan easier. Maybe some line breaks and formatting so we know what is input and what is instruction? – Zinc

Welcome to Stack Overflow! While this might be a valuable hint to solve the problem, a good answer also demonstrates the solution. Please edit to provide example code to show what you mean. Alternatively, consider writing this as a comment instead. – Hemianopsia

Answer 18

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

Janellajanelle

Answer 19

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)

Viscometer

#15014411 – Viscometer

Answer 20

To make a dynamic chart that automatically skips empty values, you can use dynamic named ranges created with formulas. When a new value is added, the chart automatically expands to include the value. If a value is deleted, the chart automatically removes the label.

https://exceljet.net/charts/dynamic-chart-ignore-empty-values#:~:text=To%20make%20a%20dynamic%20chart,chart%20automatically%20removes%20the%20label.

This is very good approach, quick, clean, and to the point.

Mythos

Answer 21

Here is another easy way to do it! (Might need to make a new table for this). Key here is to use the NA() formula for each cell, which will correctly make a gap anywhere in the line graph:

=IF(reference cell="#NA",NA(),reference cell)

Loner

Answer 22

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

Manipulator