Share via

Can I Reverse Series Order in a Chart Legend without Changing the Series order in the Chart (or vice-versa)?

Anonymous
2014-10-08T15:16:13+00:00

I'm plotting several series in a stacked chart.  This first series appears on the bottom of the stack, and the last series on the top of the stack.  This is how I want the chart.  However, the first series is listed last in the legend.  I want to list the first series first in the legend.  I know I can go to select and reorder the legend entries there, but if I do this, it will also flip the order of the stack (and in this case, that just wouldn't look right). Is there a way to change either the legend order of the stack order without changing the other?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2014-10-08T19:08:58+00:00

    There are no easy ways to do this.  I can think of one way, documented below.  It's very manual but could be automated with VBA.

    Eric

    Step 1:  Start with your original data for the stacked chart as shown below.

    Step 2:  Create some dummy data (mirror image of original data with all zeros)

    Step 3:  Create a stacked chart

    Step 4:  Reformat the chart so series with same name have same formats

    Step 5:  Individually select and delete the top four legend entries, leaving the bottom 4

    23 people found this answer helpful.
    0 comments
Answer accepted by question author
  1. Anonymous
    2014-10-08T20:55:27+00:00

    Hello,

    I have done this with clustered charts, but it can be done with stacked charts, too.  You can find a detailed article here: http://www.teylyn.com/articles/excel-articles/sort-legend-items-in-excel-charts/

    It is a similar approach to what E.J. GUN has posted. For a stacked chart you can simply stack zero value series on top of your real data, set their color and hide the legend for the real data.

    For a clustered column chart, the zero value series need to be sent to the secondary axis, since even with 0 as values they take up space horizontally.

    You only need one data point for each of the zero value series. That will be sufficient to get them to show in the legend.

    2 people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-12-16T08:28:46+00:00

    You have to go to Legend Options, and then, under Legend Position, you need to select LEFT or RIGHT to produce a vertical listing in the same order as the stacked column.

    An easier option for the problem that is posted in the Microsoft forum.

    36 people found this answer helpful.
    0 comments
  2. Anonymous
    2017-01-16T05:19:58+00:00

    Is there still no 'official' solution to this?

    Seems like a pretty convoluted workaround for a fairly common issue.

    An oversight maybe?

    9 people found this answer helpful.
    0 comments
  3. Anonymous
    2014-12-16T14:13:13+00:00

    This is not what the original poster was asking for.  He was asking for a method to reverse the normal direction of the series listing in the legend.  You cannot get Excel to do that "naturally", so the two suggestions above show how to do it manually.

    1 person found this answer helpful.
    0 comments