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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|