Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Category reversing for stacked chart (2003)

    This one has me truly stumped.
    I have 'Accounts' down the side, and 'Months' running across with various amounts.
    When I try to graph this using a stacked column my categories get reversed (see attachment)
    I know I can: > format data series > reverse the series order
    But it is a hassle given the number of charts & catrgories I have.

    Anyone with a quick fix solution?

    Rlay

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Category reversing for stacked chart (2003)

    The order in your chart is the "normal" one. In a worksheet, the default direction is "down": row 2 is below row 1 etc., whereas in a chart, the y axis goes "up". You could format the y axis to display categories in reversed order, but that is probably not what you intend. You might reverse the order of the source data instead, i.e. in your example Travel would be in row 2 and Staff in row 6. It would be possible to create a macro to accomplish this.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Category reversing for stacked chart (2003)

    I assume that the "> format data series > reverse the series order" is a newer feature of XL (it does not seem available for XL97)
    I can reverse the "categories" in the X, or reverse the values in the y (though this puts "0" at the top). I found no way to reverse the series order, except to manually move them one at a time (or is this what the question really is?)

    Here is a macro to reverse the "Plot order" on the activechart:
    <pre>Option Explicit
    Sub ReversePlotOrderChrt()
    Dim cht As Chart
    Dim iSC As Integer
    Dim x As Integer
    Set cht = ActiveChart
    iSC = cht.SeriesCollection.Count
    For x = iSC To 1 Step -1
    cht.SeriesCollection(iSC).PlotOrder = iSC - x + 1
    Next
    Set cht = Nothing
    End Sub</pre>


    If you wanted to loop thru all the charts and chartobjects in the book sheet by sheet, you could adapt it to take a chart object as a parameter. If doen this way you do not need the above code:

    <pre>Option Explicit
    Sub ReversePlotOrderAll()
    Dim wks As Worksheet
    Dim cht As Chart
    Dim chtObj As ChartObject
    For Each wks In ActiveWorkbook.Worksheets
    For Each chtObj In wks.ChartObjects
    ReversePlotOrder chtObj.Chart
    Next
    Next
    For Each cht In ActiveWorkbook.Charts
    ReversePlotOrder cht
    For Each chtObj In cht.ChartObjects
    ReversePlotOrder chtObj.Chart
    Next
    Next

    Set chtObj = Nothing
    Set cht = Nothing
    Set wks = Nothing
    End Sub
    Sub ReversePlotOrder(cht As Chart)
    Dim iSC As Integer
    Dim x As Integer
    iSC = cht.SeriesCollection.Count
    For x = iSC To 1 Step -1
    cht.SeriesCollection(iSC).PlotOrder = iSC - x + 1
    Next
    End Sub</pre>


    In either case the series order of each of the charts is reversed. If you only want to reverse the series order on particular charts, you could use an array of names to work on (or to ignore) or to use an IF to only do certain charts.

    The code goes thru each worksheet and changes sends each chartobject "chart" to be worked on by the macro, then works on all the chart sheets. On a chart sheet it does the chart itself and each chartobject which may be on the chart sheet.

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Category reversing for stacked chart (2003)

    Excel 2002 (XP) doesn't have an option to reverse the order of the data series either, I assumed that Rlay meant manually moving series up/down in the Series Order tab of the Format Data Series dialog.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Category reversing for stacked chart (2003)

    Thanks, I wasn't sure (I don't know enough about all the new features).

    If Rlay was looking at a way to do avoid doing it manually, then the code should work...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •