Comment from ckz
Time: Tuesday, October 28, 2008, 4:36 pm

ok. I am not sure how to get to the right category to ask my question regarding charts. I want to hide labels within a pie chart that have a value of 0. My range is dynamic.

I don’t want the user to have to click on a chart. When they click on a button, the user should be able to see too graphs with the updated data.

Here is the code. I am importing data from other sheets on this page, so the range moves based on where it is on the sheet.

Dim iCount As Integer
Dim chart1Range As String, chart2Range As String
Dim ch As ChartObject, sr As Series
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim chart2LabelRow As Integer
iCount = ProcessPWU_Snack_Information()

chart2LabelRow = Sheets(“Water Table”).Range(“Chart2Label”).Cells(1, 1).Row
‘Copy Chart 1 Data into Chart Range section
chart1Range = “B4:C” & (9 + iCount)
Sheets(“Water Snack Table”).ChartObjects(1).Chart.SetSourceData Source:=Sheets(“Water Table”).Range(chart1Range), PlotBy:=xlColumns
Set ch = Sheets(“Water Table”).ChartObjects(1)
Set sr = ch.Chart.SeriesCollection(1)
sr.Name = Sheets(“Water Table”).Range(“$M$” & (chart2LabelRow – 1))
sr.Values = Sheets(“Water Table”).Range(“C4:C” & (9 + iCount))
sr.XValues = Sheets(“Water Table”).Range(“B4:B” & (9 + iCount))
Sheets(“Water Table”).ChartObjects(1).Chart.Refresh

‘This is where I think I insert the code for the chart labels, but I am not sure how—-

For Each sr In ch.Chart.SeriesCollection(1)
With sr
If ch.Chart.SeriesCollection(1).HasDataLabels Then
nPts = .Points.Count
aVals = .Values
For iPts = 1 To nPts
If aVals(iPts) = 0 Then
.Points(iPts).HasDataLabel = False
End If
Next
End If
End With
Next

‘Copy Chart 2 Data from top section into the Chart Source section
chart2LabelRow = Sheets(“Water Table”).Range(“Chart2Label”).Cells(1, 1).Row
Sheets(“Water Table”).Range(“B4:C” & (14 + iCount)).Copy
Sheets(“Water Table”).Range(“M” & (chart2LabelRow + 1)).PasteSpecial xlValues
Sheets(“Water Table”).Rows((chart2LabelRow + 1) + 5 + iCount).Delete
chart2Range = “M” & (chart2LabelRow + 1) & “:N” & ((chart2LabelRow + 11) + iCount)
Sheets(“Water Table”).ChartObjects(2).Chart.SetSourceData Source:=Sheets(“Water Table”).Range(chart2Range), PlotBy:=xlColumns
Set ch = Sheets(“Water Table”).ChartObjects(2)
Set sr = ch.Chart.SeriesCollection(1)
sr.Name = Sheets(“Water Table”).Range(“$M$” & chart2LabelRow)
sr.Values = Sheets(“Water Table”).Range(“$N$” & (chart2LabelRow + 1) & “:N” & ((chart2LabelRow + 10) + iCount))
sr.XValues = Sheets(“Water Table”).Range(“$M$” & (chart2LabelRow + 1) & “:M” & ((chart2LabelRow + 10) + iCount))
Sheets(“Water Table”).ChartObjects(2).Chart.Refresh

Comment from ckz
Time: Wednesday, October 29, 2008, 10:17 am

Thank you. I used the code, it worked. When I add data to what used to be 0, refresh the chart, the data shows in the pie without the labels. What am I missing?

Comment from ckz
Time: Wednesday, October 29, 2008, 10:38 am

One other note – I blocked off the code as comments so that my labels would return, and they did not. It is odd, I click on the graph and it still has the correct data series and category names selected, yet it they will not appear.

Comment from ckz
Time: Wednesday, October 29, 2008, 10:53 am

The labels are both text and values. Do I add code to the beginning that tells it to add the labels back in, then for the code to remove the labels with 0s?

I thought about using this – .Points(iPts).HasDataLabel = True , but there has to be more to it.

Comment from ckz
Time: Wednesday, October 29, 2008, 11:14 am

Again, thank you for your help. I got an error that said “Compile error – Invalid or unqualified reference” It is highlighting the first “.Points” in the code below.

For Each sr In ch.Chart.SeriesCollection
.Points(iPts).HasDataLabel = True
.Points(iPts).ApplyDataLabels ([Type As XlDataLabelsType = [xlDataLabelsShowValue], [LegendKey], [AutoText], [HasLeaderLines], [ShowSeriesName], [ShowCategoryName], [ShowValue], [ShowPercentage], [ShowBubbleSize], [Separator])])

With sr
If .HasDataLabels Then
nPts = .Points.Count
aVals = .Values
For iPts = 1 To nPts
If aVals(iPts) = 0 Then
.Points(iPts).HasDataLabel = False
End If
Next
End If
End With
Next

Comment from Chris
Time: Saturday, November 8, 2008, 7:06 pm

have no idea how to format primary and secondary vertical axis to show multiple units: example

Primary axis is for Temperature & I want it to look like,

20
(10)

15
(5)

The top number being Celsius and the one in ( ) Fahrenheight.

On the secondary access I would like it to look like(plotting rainfall), the top number is Centimeters and the one in ( ) Inches.

2.3
(4.8)

1.0
(2.5)

I already have all my data plotted on the chart, it’s just the formatting of the axis numbers that’s driving me crazy. Any help would be greatly appreciated.

Comment from John Adams
Time: Thursday, January 29, 2009, 4:46 pm

I am trying to create a chart using Excel in the Home and Student Edition of MS Office 2007. My data for the chart is coming from a spreadsheet located on a worksheet named 2009 SAVINGS CHART, on which the chart is also located. The spreadsheet shows months Jan thru Dec in Col. A cells and a calculated value in Col. B cells for the End-Of-Month balance in a savings account. The values in Col. B are calculated on a spreadsheet located on another worksheet (in same workbook) named 2009 SAVINGS DATA, and I am referencing that worksheet for the data used to populate the spreadsheet associated with the chart. The values in Col. B are thus automatically inserted in the spreadsheet at the end of each month when the data is entered on the 2009 SAVINGS DATA worksheet for that month.

My problem is that my Excel program plots the empty cells for future months to zero. I have tried using several types of =IF formulas in Col. B cells to correct this problem, with no success. One of the formulas I have used is as follows: =IF(’2009 SAVINGS DATA’!E56<>0,’2009 SAVINGS DATA’!E56,NA()). Using this formula returns the calculated values from the source spreadsheet to the associated Col. B cells for months where the data has been added on the source spreadsheet, and #N/A in Col. B cells for months where data has not yet been added to the source spreadsheet. Even with the #N/A in the cells, the chart still plots to zero for the future months.

I have also tried manually entering the data in Col. B instead of referencing it to the other spreadsheet. Even though the cells in Col. B are empty for future months, the chart still plots to zero for all future months.

I have noticed that when I click on “Select Data” for the chart, I see “Hidden and Empty Cells”. When I click on “Hidden and Empty Cells”, I see “Hidden and Empty Cell Settings” and “Show empty cells as:”. The three options are: Gaps, Zero, and Connect data points with line. The Zero selection apparently is the default, as it is already selected for a newly created chart. The problem is that the other two selections are apparently disabled for some reason and are shown dim compared to the “Zero” selection. When I click on “Gaps” or “Connect data points with line”, nothing happens – the “Zero” remains dotted as being selected.

I am beginning to think that my main problem of the empty cells plotting to zero is that the Home and Student Edition of Excel 2007 does not include necessary programming to allow selection of “Gaps” or “Connect data points with line”. Any insight that you can provide on this issue will be greatly appreciated.

Comment from Chris
Time: Thursday, September 24, 2009, 12:37 am

Hi, this is my first time posting to your site. I want to thank you for all the help and interesting charting techniques I found on your site.

I use the code below to create a stacked bargraph and I delink the graph from the values. everything works exactly how I want it to. I created this in excel 2007 and windows vista. When I ran the macro on a different computers, one computer operating on windows media (which I know is a very poor system) and another operating on windowsXP, the macro created the stacked bar graph and delinked it just fine but when I went to select it the chart and move it it would move the first time I selected it but all the writing or text in the chart would leave an imprint on the sheet. The chart would move and look normal but where the chart use to be would be all the text from the chart and I could not figure a way to delete this left over text, it was not in cells it was sort of enbedded in the sheet, that is the best I can explain it. Also, when I would go to select the chart again I would not be able to select it at all or delete it. The chart would just be a part of that sheet. This is very confusing to me and the only thing, I can think of, that was different on the computers was the operating systems. Have you seen this before? I would greatly appreciate any feedback as I am completely lost on the matter. Thank you in advance for your time.

Option Explicit

Sub CreateGraph()
Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim LabelRng As Range
Dim iColumn As Long
Dim mySrs As Series
Dim i, j, k, l, m, n, p As Integer

 ' define chart data
 Set rngChtData = Sheets("Graph").Range("D5:IY25")

 ' define chart's X values
 With rngChtData
   Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
 End With

  ' add the chart
  Set myChtObj = ActiveSheet.ChartObjects.add _
  (Left:=250, Width:=800, Top:=75, Height:=500)

  With myChtObj.Chart
    .Legend.Delete
    .Axes(xlValue).MinimumScale = 0
    .Axes(xlValue).MaximumScale = 1
    .Axes(xlValue).MajorUnit = 0.0416666666
    .Axes(xlValue).TickLabels.NumberFormat = "[h]:mm"
    .Axes(xlValue).TickLabels.Orientation = 55
    .Axes(xlCategory).CategoryType = xlCategoryScale
    .Axes(xlCategory).ReversePlotOrder = True
    .ChartType = xlBarStacked
    .HasTitle = True
    .ChartTitle.Characters.Text = Sheets(1).Range("A1") & Chr(10) & "DUTY TIME RECORD FOR WEEK ENDING" _
      & " " & Format(Sheets(20).Range("D24"), "mm/dd/yyyy") & Chr(10) & Chr(10) & Sheets(20).Range("B3") _
      & Chr(10) & "EMP ID - " & Sheets(20).Range("B2")
    .ChartTitle.Text = .ChartTitle.Text
    .ChartGroups(1).GapWidth = 0

    ' remove extra series
    Do Until .SeriesCollection.Count = 0
      .SeriesCollection(1).Delete
    Loop

    ' add series from selected range, column by column
    For iColumn = 2 To rngChtData.Columns.Count
      With .SeriesCollection.NewSeries
        .Values = rngChtXVal.Offset(, iColumn - 1)
        .XValues = Array(Format(Range("D6"), "dddd - mm/dd/yyyy"), "", "", Format(Range("D9"), _
         "dddd - mm/dd/yyyy"), "", "", Format(Range("D12"), "dddd - mm/dd/yyyy"), "", "", _
         Format(Range("D15"), "dddd - mm/dd/yyyy"), "", "", Format(Range("D18"), "dddd - mm/dd/yyyy"), _
         "", "", Format(Range("D21"), "dddd - mm/dd/yyyy"), "", "", Format(Range("D24"), "dddd - mm/dd/yyyy"))
        '"=Graph!D6:D25"
        'Array(Range("D6"), Range("d9"))
      End With
    Next

    ' set all bar colors to blue
    For i = 1 To .SeriesCollection.Count
      With .SeriesCollection(i)
        .Interior.Color = RGB(114, 137, 234)
      End With
    Next

    ' set flight time bar colors to yellow
    For j = 1 To .SeriesCollection.Count
      For k = 2 To .SeriesCollection(j).Points.Count Step 3
        With .SeriesCollection(j).Points(k)
          .Interior.Color = RGB(248, 240, 86)
        End With
      Next
    Next

    ' set even numbered series to transparent
    For l = 2 To .SeriesCollection.Count Step 2
      With .SeriesCollection(l)
        .Interior.ColorIndex = xlNone
      End With
    Next

    ' add labels to graph
    Set LabelRng = Sheets("Graph").Range("D48")
      .SetElement (msoElementDataLabelNone)  'clears chart labels
        For m = 1 To .SeriesCollection.Count Step 2
          For n = 1 To .SeriesCollection(m).Points.Count Step 3
            With .SeriesCollection(m).Points(n)
              .ApplyDataLabels
              .DataLabel.Text = LabelRng(Int(n / 3) + 1, m).Text
            End With
            With .SeriesCollection(m).Points(n + 1)
              .ApplyDataLabels
              .DataLabel.Text = LabelRng(Int(n / 3) + 1 + 8, m).Text
            End With
          Next n
      Next m

    ' delink chart
    On Error Resume Next
      For Each mySrs In .SeriesCollection
        With mySrs
          .XValues = .XValues
          .Values = .Values
          .Name = .Name
        End With
      Next mySrs
  End With
End Sub 

Comment from Chris
Time: Thursday, September 24, 2009, 12:02 pm

Thank you for your reply. So this is more likely due to an earlier version of excel and would not have any issues with what operating system the computer operates on? How would I determine which version of excel I am running, I did not know there were various versions? How would i set the ultimate position of the chart? “p” should not be in my code above, but you are suggesting I should be declaring i, j, k,l ,m, n as variants and not integers?

Again thank you for your information.

Comment from Chris
Time: Thursday, September 24, 2009, 6:17 pm

Thank you for the quick replies, I guess I am at a loss to what the issue is but my last question is (and I apologize if this is a duplicate I thought I posted this but I do not think it posted) Does the computers operating system affect how excel works at all?

Comment from chris
Time: Thursday, September 24, 2009, 8:29 pm

Thank you for all the information, and once again I really appreciate your website, I would not be able to do most of what I have done with charts without this site!

Comment from Chris
Time: Monday, November 16, 2009, 2:06 am

I have created some charts for my small business from your website and you have been a great help and I wanted to thank you for that, but my question is I am trying to check the value of my points of series collection 1, and if the value is “24:00″ then I want the bar color to be red. the code I have works if the cell values are values and not formulas but I was wondering why it does not work if the cells are formulas that = “24:00″

here is the piece of my code:

‘ set days off series to red
With .SeriesCollection(1)
PointsValues = .Values
For p = 1 To UBound(PointsValues)
If PointsValues(p) = 1 Then
.Points(p).Interior.Color = RGB(234, 87, 58)
End If
Next p
End With

Thank you for any help.

Comment from Chris
Time: Monday, November 16, 2009, 1:47 pm

If the value is “24:00″, which is equal to 1, I want the bar to turn red. This works if I change the formulas to values first, but if I leave them as formulas, which still read “24:00″ it will not work?

Comment from Chris
Time: Monday, November 16, 2009, 3:36 pm

=IF(ISBLANK(F33),IF(ISBLANK(F31),”24:00″,IF(LARGE(F31:BC31,1)-LARGE(F31:BC31,2)>(E31+”23:59″)-LARGE(F31:BC31,2),(LARGE(F31:BC31,1)-(E33+”0:00″)),”24:00″)),IF(ISBLANK(F31),”0:00″,IF(LARGE(F31:BC31,1)-LARGE(F31:BC31,2)>(E31+”23:59″)-LARGE(F31:BC31,2),(LARGE(F31:BC31,1)-(E33+”0:00″)),”0:00″)))

I think you are right with the text comment, 24:00 in quotes will be read as text?

Comment from Chris
Time: Monday, November 16, 2009, 3:55 pm

That makes perfect sense, thank you again for your advice!

Comment from callie
Time: Thursday, February 11, 2010, 2:26 am

Hi, do you know how to put different correlation plots into one graph, which don’t have the same x or y value. For example, three drugs treat tumors. Each of them have different concentration and resulted in certain inhibitory effect. The dose looks like correlate with the tumor reduction, but three drugs have different correlation curve. How do I put all three correlation plot in one excel chart? Or excel can’t do it? thanks a lot.

Comment from callie
Time: Thursday, February 11, 2010, 2:08 pm

Thanks a lot, Jon. I tried this way before, but I didn’t select the chart. It comes out a bigger version of selection window and I couldn’t get anything out. Now I select the chart first, then it came out right.

Comment from Julien
Time: Saturday, May 1, 2010, 9:49 am

Hello Jon,

I have a problem regarding embedded charts on a worksheet and the activate/deactivate events:

If you have on a worksheet an embedded chart selected and then you select another workbook, no Workbook_Deactivate()/Activate() event are triggered.

As I create/delete toolbars and menus based and those events, it is then a issue if they are raised properly.

After a lot og googling, I could see that many people have encountered the same problem but nobody posted any solution.

Do you know any solution or workaround?

Thanks in advance and best regards,

Julien

Comment from Julien
Time: Sunday, May 2, 2010, 5:15 am

Thanks a lot Jon.
However, I have already a SP3 release… or do you mean there is another bug fix release since then?
I have looked the Description of Office 2003 Service Pack 3 with respect to Excel, and I couldn’t see any fixe related to my topic
http://support.microsoft.com/kb/923618/en-us

Also, in your answer, you mention the worksheet events, although I mean the workbook event.

To avoid any misunderstanding due to my bad english, let’s consider the following scenario:

-you have two workbooks: Book1 & Book2
- you have an embedded chart on Book1
-In Book1′s “thisworkbook” you have the following code
Private Sub Workbook_Deactivate()
MsgBox “bye bye”
End Sub
- well, my experience is:
-> when book1 selection is, for instance, a range and then you select Book2: you get the dialog box “bye bye”
-> when book1 selection is now the embedded chart and then you click on Book2: you don’t get any dialog box “bye bye”

What’s wrong with that?

Best regards,

Julien

Comment from Julien
Time: Monday, May 3, 2010, 6:40 am

Thanks again Jon.
This behaviour on my Excel is really strange then. I tried with Excel SP3 version at home, at work, operating with XP Pro, Vista, Windows 7. Still the same thing.

Actually, I just found on the net an old post (2004) of someone who had the same problem and already at that time, this guy requested some help from you :-)
http://www.mofeel.net/79-microsoft-public-excel-charting/6162.aspx

Your answer was at that time:
“The embedded chart is part of a worksheet in the workbook, so activating
it does not deactivate the sheet or the workbook. You could either
assign a macro to the shape that contains the chart (right click on the
chart and choose Assign Macro), or you could use a class module to
capture chart events, which include Activate”

Beside the SP3, I do not know what has changed so that it works with you now.
Anyway, I will act according to this old post as long as I don’t get my Excel working like yours… :-(

Best regards,

Julien

Comment from John
Time: Tuesday, July 13, 2010, 7:09 am

Hi Jon.

I’ve a few short questions that will not task your coding ability (a welcome relief?) nor overly task (it is hoped) your time.

1. Is there an EASY way to get raw data from a provider (dtn comes to mind) porting said from the computer attached to the internet and then on to a sandboxed second computer from which one can then build real time charts in Excel? My experience with such matters is limited.

2. Are there any good and easy to use excel realtime charting packages whose ownership is NOT affiliated with any bank/brokerage/trading concern (medveds quote tracker comes to mind).

As you can likely tell, I’m new to whole concept of washing raw data at my end rather than relying on pre-packaged software (other than excel of course). I’m simply fed up with establishing intelligent indicator workarounds to the software that some trading firms masquerading as ‘brokerages’ provide for charting….only to have the workaround curiously fail to work the next business day.

Any suggestions/pointers/direction to good sources you can offer would be much appreciated…as would knowing if anything you’ve done in the past for other clients would help me.

Comment from John
Time: Wednesday, July 14, 2010, 4:36 am

Thanks for your time Jon. It was a shot in the dark anyway.

I just need to be able to get raw data from a data provider, and run moving averages off price with a displacement factor of ‘x’ number of bars. If I can get excel to do this…then I can find a tech at my end to handle the externet and infranet issues. The problem is finding an excel pkg that was not designed by the wrong crowd, limited to using data of specious quality from their providers. So, I will have to have one made.

‘Did you know that future behavior mimicks past behavior’.
Ovid…the end of a thing is established in its beginning. Funny that he was banished by an Emperor shortly after stating that. ;~) As to the market…Beware the Ides of July.

John

Comment from John
Time: Friday, July 16, 2010, 3:04 am

OK, I’ll find someone who can code excel for me. Thanks for your input.

Comment from ken
Time: Monday, September 6, 2010, 6:08 am

John,
i have a question regarding excel 7. i am trying to create excel charts to paste into word tables. i know the dimensions of word table,and am trying to adjust print margins on excel page, however, excel chart does not adjust. ability to center horizontally and vertically are disabled. any suggestions.

thsk
ken

Comment from Michael
Time: Thursday, September 9, 2010, 10:20 am

Good morning,
A Google search for “pie chart remove zeros” led me here
().
The code you corrected for ckz (posted – 10/28/2008) works…but…
For me, the data is entered in as integers and the pie chart needs to be desplayed as percentages. Seems like a modification *should be* straightforward, but I’m either reinserting the 0′s or geting numbers like 5000%.

How do I untangle this?

Many thanks,
Michael

Comment from priyadarshani
Time: Wednesday, April 13, 2011, 2:38 pm

Thanks for the information provided here…how to plot correlation chart For example : 21 drug compounds have different biological activity….now i want to correlate these with different descriptors with different value for 21 drug compounds
multiple linear regression Y=m1X1+m2X2+m3X3……..+C
I WANT TO PLOT A SINGLE CHART …

biological activity d1 d2 d3 d4 d5 d6 d7 d8 d9 d10 d11 d12 d13 d14 d15

1 – – – – – – - – - – - – – - – – – – – -
2
3
4
5 VALUE FOR BIOLOGICAL ACTIVITY IS DIFFERENT FOR 21
6 DRUG COMPOUND & THERE IS DIFFERENT 15 DESCRIPTORS
7 WITH DIFFERENT VALUE FOR 21 DRUG COMPOUND
8
9
10
11
12
13
14
15
16
17
18
19
20
21————————————————————————————-

PLEASE REPLY

Comment from Amjed Mohammed
Time: Thursday, May 26, 2011, 1:53 am

Hello Jon
Thank you for the posted solutions. I have tried one of your earlier solutions on the subject of 2D histograms and it didn’t work. I think I have done exactly as you posted
at that time
http://groups.google.com/group/microsoft.public.excel.charting/browse_thread/thread/5979a4e8f55e5735

If I use the 1st formula then I get !VALUE because the denominator is calculated correctly but not the numerator. Using the 2nd formula I get !DIV/0.

Many thanks and kind regards

Amjed

Comment from Amjed Mohammed
Time: Thursday, May 26, 2011, 3:23 am

Sorry about my previous email. I just copy pased the data wrongly.
Many thanks for the great formulas

Comment from Don Gloo
Time: Friday, June 3, 2011, 3:51 pm

Jon: Thank you for maintaining such a thorough, and thorougly educational, blog. I’ve added it to the other Excel blogs that I follow, such as Chandoo and Excel Hero.

If you can spare a moment, I’d love your input on a hurdle that I’ve encountered in my Excel work.

I have a table of data in one tab. Each columnn corresponds to a city, and the rows are attributes about the cities, such as population, area, density, etc. There are approximately 800 rows of attributes.

I’m trying to set up a graphing tool in another tab so that a user selects one of the attributes from a drop down list, and Excel uses the “Cell Link” from the drop-down control, combined with the =OFFSET function, to find the correct row and graph it. More precisely, I have a single row table next to the graph that uses =OFFSET to bring in the appropriate row of data from the main data table so it can be graphed. Because I’m using this interim table, the =SERIES formula in the graph always refers to the cells. Only the contents of these cells changes.

The problem I’m having is that the various rows in the source data can have a variety of number formats–percents, currency, numbers with 0 decimals, and numbers with 2 decimals. While the =OFFSET function brings in the data beautifully, it doesn’t bring over the cell formats. As a result, the data in the interim table referenced by the =SERIES formual are always unformatted, and, thus, so are the data labels and the axis labels in the graph, since the graph number formats are “linked to source”.

Any thoughts you might have for making the original cell formats translate over to the graph would be most appreciated. Some thoughts I’ve considered: trying to insert a dynamic cell reference into the =SERIES formula, but I’ve had no luck tinkering with the =SERIES formula.

Thanks.

Comment from RAM
Time: Thursday, November 17, 2011, 12:41 pm

Great blog!
I have a question, how to change the style of the end of the line
I need capstyle.square, look here

http://office.microsoft.com/en-us/excel-help/format-object-line-style-pane-HA010124523.aspx

Regards