locked
excel 2010 -- how can I specify the color to use for each series in a stacked bar chart? RRS feed

  • Question

  • I've created a stacked bar pivot chart in excel 2010. I want to be able to specify the color for each series.  The screenshot below shows the default colors that appear:

    In SQL SSRS, under Series Properties | Fill, I can use an expression to specify Color.  Here's the expression I used to specify series colors for a stacked bar chart in a SSRS:

    =Switch(Fields!aptStatus.Value="KEPT","Green",
    Fields!aptStatus.Value="TEL-CON","Yellow",
    Fields!aptStatus.Value="WALK-IN","Orange",
    Fields!aptStatus.Value="CANCEL","Black",
    Fields!aptStatus.Value="NO-SHOW","Red",
    Fields!aptStatus.Value="BOOKED","Lime",
    Fields!aptStatus.Value="OCC-SVC","Aqua",
    Fields!aptStatus.Value="LWOBS","Pink",
    Fields!aptStatus.Value="PENDING","Silver",
    Fields!aptStatus.Value="S-CALL","Plum",
    Fields!aptStatus.Value="ADMIN","Dark Violet")

    Does anyone know how to do it in Excel 2010?  I've been poking around but haven't found a spot where I can set it up in Excel.

    Christian Bahnsen

    Friday, July 1, 2016 6:43 PM

Answers

  • James,

    Thanks for your reply.  It got me thinking in terms of collections.

    I found a great article at http://peltiertech.com/vba-conditional-formatting-of-charts-by-series-name/ that showed me how to specify the color for each series by name.

    I created the "palette" on a worksheet I named seriesColors:

    Then I created a VBA module with the following code using the Peltier code almost verbatim:

    Sub setColor()
        Dim rPatterns As Range
        Dim iSeries As Long
        Dim rSeries As Range
        
        Set rPatterns = Sheets("seriesColors").Range("A1:A11")
        
        Sheets("chart").Select
        Sheets("chart").ChartObjects("chart 1").Select
        
        With ActiveChart
            For iSeries = 1 To .SeriesCollection.Count
                Set rSeries = rPatterns.Find(what:=.SeriesCollection(iSeries).Name, Lookat:=xlWhole)
                If Not rSeries Is Nothing Then
                    .SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = rSeries.Interior.Color
                End If
            Next
        End With
        
    End Sub
    

    I ran the module and my chart is now using the colors I defined:

    Tuesday, July 5, 2016 4:57 PM

All replies

  • Re:  Chart Series Colors

    Using VBA in an Excel standard module in the workbook containing the chart...
    Try these:
        ActiveChart.SeriesCollection(1).Format.Fill.ForeColor.ObjectThemeColor = 10   'msoThemeColorAccent6  
        ActiveChart.SeriesCollection(1).Format.Fill.ForeColor.RGB = vbBlue

    OR

    Right click a chart column and choose "Format Data Series..." from the popup menu.
    Then select "Fill"

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel programs)


    • Edited by James Cone Thursday, February 2, 2017 1:51 PM Update Link
    Saturday, July 2, 2016 5:29 AM
  • Hi Christian Bahnsen,

    Did you want to change the color for each series in Excel chart?

    If yes, you can refer to the reply of Jim Cone.

    You can right click one of the series and select Fill change the color.

    If you need more help, please let us know and we are glad to help you.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Monday, July 4, 2016 7:46 AM
  • James,

    Thanks for your reply.  It got me thinking in terms of collections.

    I found a great article at http://peltiertech.com/vba-conditional-formatting-of-charts-by-series-name/ that showed me how to specify the color for each series by name.

    I created the "palette" on a worksheet I named seriesColors:

    Then I created a VBA module with the following code using the Peltier code almost verbatim:

    Sub setColor()
        Dim rPatterns As Range
        Dim iSeries As Long
        Dim rSeries As Range
        
        Set rPatterns = Sheets("seriesColors").Range("A1:A11")
        
        Sheets("chart").Select
        Sheets("chart").ChartObjects("chart 1").Select
        
        With ActiveChart
            For iSeries = 1 To .SeriesCollection.Count
                Set rSeries = rPatterns.Find(what:=.SeriesCollection(iSeries).Name, Lookat:=xlWhole)
                If Not rSeries Is Nothing Then
                    .SeriesCollection(iSeries).Format.Fill.ForeColor.RGB = rSeries.Interior.Color
                End If
            Next
        End With
        
    End Sub
    

    I ran the module and my chart is now using the colors I defined:

    Tuesday, July 5, 2016 4:57 PM
  • Emi,

    Thanks for your reply.  I could not/did not want to "hard-code" each series using the Fill approach because not every Status appears each time the chart renders.

    The "palette" I created using the Peltier approach ensured that each possible Status was defined and had a pre-defined color.

    Tuesday, July 5, 2016 5:03 PM