Omit $0.00 When Creating a Chart

[GarySmith]

I've searched high and low for this one. Can you help? I don't want to include the $0.00 values when charting this list. Idea?

Here a screen shot of the sample list:

Here's the data range. Is this where I edit to get the range to omit a zero value in the list?

 

[XL&ME]

See if this thread does what you want. http://www.mrexcel.com/forum/excel-questions/542035-chart-hide-zero.html

 

[GarySmith]

Sounds right? but, am confused as to how to write it if the charted cells are referenced from other Worksheets?

From Example above:
B1 =Worksheet1!A1
B2 =Worksheet3!A1
B3 =Worksheet9!A1
B4 =Worksheet6!A1

Where or how would the formula read? =IF(A1=0,NA(),A1)

 

[Piktro]

I can't seem to figure this out...

One option I have for you, is to run a macro that hides the rows containing the 0 values, then you can disable the option to show hidden rows (but the data would still be there for calculations, ranges, etc). You can find that on the Select Data prompt, bottom left side.

Here's the hide rows macro for B:B

Sub HideRows()
Dim cell As Range
    For Each cell In Range("B:B")


 If UCase(cell.Value) = "0" Then
    cell.EntireRow.Hidden = True
 End If
 
Next
End Sub

 

[GarySmith]

That worked, but when data is later added to the range, the row is hidden and the graph does not reflect the new range data. Can the range determine if data is has been entered and "auto-show" the correct graph? See Video

I got the hidden cells to show again with: Shift/Ctrl + 9 (manually)

 

[S.H.A.D.O.]

Isn't this part of the same project as http://www.mrexcel.com/forum/excel-questions/700661-timer-lock-workbook.html??
This could get quite confusing!

 

[GarySmith]

SHADO, I accidentally posted a reply to the wrong thread! Sorry about that...Please disregard ... I've asked the BOARD Admin to take it down, because the forum moderators have set the "Edit This Thread" to only a few mins and I couldn't edit the comment out myself.

 

[S.H.A.D.O.]

No problems!

 

[GarySmith]

Not a problem here either, thanks. Do you have any ideas on helping solve the thread topic, S.H.A.D.O.?

 

[oldbrewer]

do you want just 5 bars on your chart without gaps caused by zero values?