Is there any way to make pivot charts look better

[MJK]

I've been expermenting with pivot charts. They work great, but all you can get when using a bar chart is a blue bar. I tried reformatting the chart, changing the bar colors, adding values - until it looks great. But once i refresh the data - I lose everything and am left with the default blue bars. Is there a work around ?

 

[MJK]

Vane and John

Absolutely outstanding.

Thanks for the help
MJK

 

[nyim]

Easy default Pivot Chart

In Excel 2003

You should be able to format your chart
Right click the chart
Choose > Chart Type
In the bottom left corner, check the button for
Set as Default Chart

This will make it default back when you refresh your data.

 

[Jon Peltier]

Unfortunately that imposes the chart type on every new chart you create.

 

[MJK]

I have another quick formatting question. I want to use different colors for certain bars, for example. the months in 2004 would be blue while the months in 2005 would be green.

I recorded the following macro to test changing the colors on a could of bars. I would like to incorporate it into Jon's code. Do I place it as a separate case ?

ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 27
.Pattern = xlSolid
End With
ActiveWindow.Visible = False
End Sub

Thanks for the help
MJK

 

[Jon Peltier]

Are the years aligned by category or by series? There are a few formatting macros at the bottom of this article that you could make use of:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=390

 

[MJK]

Jon

I checked out the link, and I'm stuck.

What I'm trying to do is color past years and 2005 months Jan - Oct a different color then forecasted years / months (i.e.; Jan - Dec 2004 and Jan - Nov 2005 would be blue, while Dec 2005 and Jan - Dec 2006 would be green).

I tried to tie this into the code you provided to Vane and myself a couple of months ago - I wasn't successful. Point (1) is Jan-04 and also the 1st bar in the series.

Sub ColorChartSeries(cht As Chart)
Dim intSeries As Integer
Dim intPoint As Integer
With cht
For intSeries = 1 To .SeriesCollection.Count
With .SeriesCollection(intSeries)
Select Case intSeries ' may need to extend cases
Case 1
.Interior.ColorIndex = 4
Case 2
.Points(1).Select
.Interior.ColorIndex = 6
Case 3
.Points(2).Select
.Interior.ColorIndex = 6
End Select
.ApplyDataLabels
.DataLabels.Position = xlLabelPositionInsideBase
.DataLabels.HorizontalAlignment = xlCenter
.DataLabels.VerticalAlignment = xlCenter
.DataLabels.Orientation = xlUpward
.DataLabels.NumberFormat = "$#,##0"
.DataLabels.Font.Size = 8
.DataLabels.Font.Bold = True
For intPoint = 1 To .Points.Count
.DataLabels(intPoint).Top = .DataLabels(intPoint).Top - 20
Next
End With
Next
End With
End Sub


Thanks again for the help
Mike

 

[Jon Peltier]

If your X data is made up of dates (that Excel recognizes), you can use this approach. It queries each category value, and applies a color based on which year the date falls within.

Sub ColorByYear()
    Dim iPoint As Long, nPoint As Long
    With ActiveChart.SeriesCollection(1)
         For iPoint = 1 To .Points.Count
             Select Case Year(WorksheetFunction.Index(.XValues, iPoint))
                 Case 2001
                     .Points(iPoint).Interior.ColorIndex = 6 ' Yellow
                 Case 2002
                     .Points(iPoint).Interior.ColorIndex = 5 ' Blue
                 Case 2003
                     .Points(iPoint).Interior.ColorIndex = 3 ' Red
                 Case 2004
                     .Points(iPoint).Interior.ColorIndex = 13 ' Purple
                 Case 2005
                     .Points(iPoint).Interior.ColorIndex = 46 ' Orange
                 Case 2006
                     .Points(iPoint).Interior.ColorIndex = 4 ' Green
                 Case Else
                     .Points(iPoint).Interior.ColorIndex = 8 ' Cyan
             End Select
         Next
    End With
End Sub

If you have plain old text labels that contain the four digit year as part of the string, you could use this version:

Sub ColorByYear2()
    Dim iPoint As Long, nPoint As Long
    Dim sCategory As String
    With ActiveChart.SeriesCollection(1)
         For iPoint = 1 To .Points.Count
             sCategory = WorksheetFunction.Index(.XValues, iPoint)
             If InStr(sCategory, "2001") Then
                 .Points(iPoint).Interior.ColorIndex = 6 ' Yellow
             ElseIf InStr(sCategory, "2002") Then
                 .Points(iPoint).Interior.ColorIndex = 5 ' Blue
             ElseIf InStr(sCategory, "2003") Then
                 .Points(iPoint).Interior.ColorIndex = 3 ' Red
             ElseIf InStr(sCategory, "2004") Then
                 .Points(iPoint).Interior.ColorIndex = 13 ' Purple
             ElseIf InStr(sCategory, "2005") Then
                 .Points(iPoint).Interior.ColorIndex = 46 ' Orange
             ElseIf InStr(sCategory, "2006") Then
                 .Points(iPoint).Interior.ColorIndex = 4 ' Green
             Else
                 .Points(iPoint).Interior.ColorIndex = 8 ' Cyan
             End If
         Next
    End With
End Sub

 

[MJK]

Jon

A couple of quick questions:

In 2005 / 2006 - what would be the best approach to show different colors for past vs forecasted months. If we use 2005 for an example, the chart would have Jan - Nov as blue bars and Dec as green (or Forecasted). I plan to do this in 2006 - as months close, the bar color would change. Can I taylor the code to work on months instead of years ?

Also, how do I incorporate this sub routine into the code you provide to Vane and myself. BTW - that code was Spectacular...! Do I put it into a separate module or append it to the original code?

Thanks again for the help
MJK

ps - ever hear of "Preto Charts" ?

 

[Jon Peltier]

In 2005 / 2006 - what would be the best approach to show different colors for past vs forecasted months. If we use 2005 for an example, the chart would have Jan - Nov as blue bars and Dec as green (or Forecasted). I plan to do this in 2006 - as months close, the bar color would change. Can I taylor the code to work on months instead of years?

Now you only need one if/then:

  If WorksheetFunction.Index(.XValues, iPoint) > Now Then
    ' Future, so it's a forecast
    .Points(iPoint).Interior.ColorIndex = 4 ' Green 
  Else
    .Points(iPoint).Interior.ColorIndex = 5 ' Blue 
  End If

Also, how do I incorporate this sub routine into the code you provide to Vane and myself. BTW - that code was Spectacular...! Do I put it into a separate module or append it to the original code?

Basically, you put the code where it makes the most sense within the entire project.

BTW, this can also be done without code, in a more automatic fashion, if you're not afraid of a few formulas and a few extra series in the chart:

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

ps - ever hear of "Preto Charts" ?

Yeah, a Pareto chart is a sorted histogram, often with a line series showing the cumulative sum of the columns.

 

[MJK]

I got the following error:

Runtime error 91
Object variable or or with block variable not set

VBE Stops at With ActiveChart.SeriesCollection(1)

Sub ColorChartSeries2()
Dim iPoint As Long, nPoint As Long
Dim sCategory As String
With ActiveChart.SeriesCollection(1) For iPoint = 1 To .Points.Count
sCategory = WorksheetFunction.Index(.XValues, iPoint)
If InStr(sCategory, "Jan '05") Then
.Points(iPoint).Interior.ColorIndex = 6 ' Yellow
ElseIf InStr(sCategory, "Feb '05") Then
.Points(iPoint).Interior.ColorIndex = 5 ' Blue
ElseIf InStr(sCategory, "Mar '05") Then
.Points(iPoint).Interior.ColorIndex = 3 ' Red
ElseIf InStr(sCategory, "Q1 '05") Then
.Points(iPoint).Interior.ColorIndex = 13 ' Purple
ElseIf InStr(sCategory, "Apr '05") Then
.Points(iPoint).Interior.ColorIndex = 46 ' Orange
ElseIf InStr(sCategory, "May '05") Then
.Points(iPoint).Interior.ColorIndex = 4 ' Green
Else
.Points(iPoint).Interior.ColorIndex = 8 ' Cyan
End If
Next
End With
End Sub