Omit $0.00 When Creating a Chart

GarySmith

New Member
Joined
Apr 13, 2013
Messages
30
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:

Test_Chart_2-e1367628779350.jpg




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

Test_Chart.jpg
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

GarySmith

New Member
Joined
Apr 13, 2013
Messages
30
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

Board Regular
Joined
Mar 19, 2013
Messages
56
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

Code:
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
 
Last edited:

GarySmith

New Member
Joined
Apr 13, 2013
Messages
30

ADVERTISEMENT

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)
 

GarySmith

New Member
Joined
Apr 13, 2013
Messages
30

ADVERTISEMENT

SHADO, I accidentally posted a reply to the wrong thread! :eek: 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.
 

GarySmith

New Member
Joined
Apr 13, 2013
Messages
30
Not a problem here either, thanks. Do you have any ideas on helping solve the thread topic, S.H.A.D.O.?
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
do you want just 5 bars on your chart without gaps caused by zero values?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,222
Messages
5,594,899
Members
413,950
Latest member
solve22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top