Do Not Display Zero Values in a Chart

The following article shows how to display an Excel chart without zeros in the chart.  The traditional method for excluding zero values is to use NA() with a condition see Remove Chart Series.  This method is described in the article  Here.  The following is a slightly different method which uses named ranges for the graphs and an on Change event to sort the data as the Chart's underlying data changes.  It works well if you are expecting zero values to muddy a well thought out Chart.

Add a value to any of the items under Stock which is zero and it will be added to the chart.  Place a zero against any of the items with a value and it will be automatically removed from the Chart.  The secret behind it is the two named ranges which feed the chart.  





There is a VBA on Change Event which fires every time data between D15 and D30 is changed.  

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D15:D30")) Is Nothing Then
Range(Cells(15, 3), Cells(30, 4)).Sort Key1:=Columns(4), Order1:=xlDescending
End If
End Sub

The length of the Range is not dynamic in the example above but has more cells than is required so further Fruits can be added to the list.  Use any of the many dynamic range examples if you wish to extend the range.