Bar Graph Help (removal of zero values)

  • Thread starter Thread starter Heather Rabbitt
  • Start date Start date
H

Heather Rabbitt

Hi,

I have a series of bar graphs (approximately 20 in total) and each one
is linked to a set of 5 data points (i.e. 5 point data scale). For
some of the points the value is zero and this is showing up
intermittently on my bar graphs - I want to get rid of the zero values
however I am producing approximately 500 reports and don't want to do
it manually. Can anyone help me/ provide and suggestions.

Thanks in advance for your reply.

Kind regards,

Heather
 
I found on article on the Microsoft website that may help but it
applys to %'s and I need it to apply to values - can anyone help in
revising the macro to apply to my situation?


Found here: http://support.microsoft.com/default.aspx?scid=kb;en-us;142132

Macro to Remove 0% Labels from a Pie Chart

SUMMARY
Microsoft Excel creates 0% labels when a pie chart has data that
includes blanks or the value 0 (zero). These labels can overlap other
labels, making it difficult to read the chart. You can manually delete
these labels one at a time. However, when your data changes, Microsoft
Excel puts the labels back.
This example removes the 0% labels from a chart. You can run the
ClearLabels procedure manually or have it run automatically when the
sheet is calculated by using the Auto_Open and Auto_Close procedures.
You can easily modify this macro to work with "Show Label and Percent"
data labels or "Show Percent" data labels.

Use Auto_Open or Auto_Close to have the chart update automatically
with changes in the data. To run the macro manually, click Macro on
the Tools menu, click the ClearLabels macro, and click Run.
Sample Visual Basic Procedure
Sub Auto_Open()
' Change the "Sheet1" to the sheet with your data on it.
Worksheets("Sheet1").OnCalculate = "ClearLabels"
End Sub

Sub Auto_Close()
Worksheets("Sheet1").OnCalculate = ""
End Sub

Sub ClearLabels()
' Change the "Sheet1" to the Sheet with your chart on it and
' "Chart 1" to the name of your chart.
Worksheets("Sheet1").ChartObjects("Chart
1").Chart.ApplyDataLabels _
Type:=xlShowPercent
' Note: In preceding statement, use Type:=xlShowPercent for
charts
' with just percents, if there are none. Use
' Type:=xlShowLabelAndPercent instead for charts with both,
Labels
' and Percents, if there are none.
For Each X In Worksheets("Sheet1").ChartObjects("Chart 1"). _
Chart.SeriesCollection(1).Points
' Use the following two lines for charts with just percents.
If InStr(X.DataLabel.Text, "0%") > 0 _
And Len(X.DataLabel.Text) = 2 Then
' Use the following line instead for charts with labels
and
' percents.
' If InStr(x.DataLabel.Text, Chr(10) & "0%") > 0 Then
X.DataLabel.Delete
End If
Next
End Sub
 
Heather -

A non-macro way to hide zero labels is to use a custom number format for
the labels. Select the labels (so all are selected), and press Ctrl-1
(numeral one) to format them. Click on the Number tab, click on Custom
in the list, and enter a number format like one of these:

0;;;
0.0;;;
$0.00;;;

When multiple formats are provided, separated by semicolons, the first
is used for positive numbers, the second for negatives, the third for
zeros, and the fourth for text values. Since there is nothing entered
for zeros, a zero will not be shown. For the first entry, use an
appropriate format for your data.

- Jon
 
I will assume you want to remove the label and not the bar (because
with the x-axis set at intersect the y-axis at y=zero, there will be no
visible bar).

For two non-programmatic solutions:

(1) Replace all the zeros with empty cells. Needs one global step
(Edit | Replace | Replace All button).

(2) Suppose your data are in A1:A5. Then, in B1, enter the formula
=IF(A1=0,"",A1). Copy B1 down to B2:B5. If you don't already have it,
get Rob Bovey's free and mis-named add-in XY Chartlabeler from
www.appspro.com.

Use this add-in to label your series with the data in column B.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top