A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Bar Graph Help (removal of zero values)



 
 
Thread Tools Display Modes
  #1  
Old September 25th, 2003, 10:27 PM
Heather Rabbitt
external usenet poster
 
Posts: n/a
Default Bar Graph Help (removal of zero values)

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
  #2  
Old September 26th, 2003, 12:29 AM
Gromit
external usenet poster
 
Posts: n/a
Default Bar Graph Help (removal of zero values)

Have you tried the options at

Tool - Options - Chart ....?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

  #3  
Old September 26th, 2003, 03:49 PM
Heather Rabbitt
external usenet poster
 
Posts: n/a
Default Bar Graph Help (removal of zero values)

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 he http://support.microsoft.com/default...b;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
  #4  
Old September 27th, 2003, 04:46 AM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Bar Graph Help (removal of zero values)

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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Heather Rabbitt wrote:
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 he http://support.microsoft.com/default...b;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


  #5  
Old September 27th, 2003, 11:41 AM
Tushar Mehta
external usenet poster
 
Posts: n/a
Default Bar Graph Help (removal of zero values)

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

In article ,
says...
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.