One of the mysteries of modern life is the order in which series appear in the legend of an Excel chart. People have been driven mad while attempting to reorganize legends in their charts. I’ve spent years working with Excel charts, and I’ve made some observations and generalizations about Excel chart legends. These are only valid for charts in Excel 2003 and earlier, as there seem to be some substantial differences in behavior introduced by Excel 2007.

Summary of Legend Entry Order Rules

Before presenting the rules for legend entry order, a couple of terms need to be defined:

Axis Group – An axis group is the collection of all series plotted on an axis. There is always a primary axis group in a chart, and sometimes a secondary axis group.

Chart Group – A chart group is the collection of all series of a given type within an axis group. For example, there may be a primary line type chart group, or a secondary column type chart group.

When you manually reorder series in a chart, the series available for you to reorder at any given time are all part of a single chart group. You won’t even see other series in the list.

Here are the rules for legend entry order, in vertically listed legends, for charts with horizontal category (X) axes and vertical (Y) axes. The rules are explained and illustrated in the article below.

• Series are listed according to chart type, in this order: Area, Column and Bar, Line, and XY.
• Within each chart type, primary axis series are listed first, followed by secondary axis series.
• Series within each chart group are listed according to plot order.
• If the first chart group listed represents stacked series, series within all chart groups are listed in reverse order.

Effect of Series Type on Order of Legend Entries

Series are listed according to chart type, regardless of their plot order. The order of chart types in the legend is area, then column or bar, then line, and finally XY. This matches the bottom-to-top stacking order of the series in the chart.

Here are two combination charts with the same chart types. The area series is listed first and the line series is listed last, regardless of the plot orders of the series (the number at the beginning of the legend entry label).

Effect of Axis on Order of Legend Entries

Series on the primary axis are listed before series on the secondary axis.

In the left hand chart below, the first two series are on the primary axis and the last two are on the secondary axis. In the right hand chart, this is reversed. In both cases, the primary axis series are listed first in the legend.

This relationship is true as long as all series are of the same chart type (e.g., all are area, column, or line chart series).

Effect of Series Type and Axis on Order of Legend Entries

If the combination chart has series on both primary and secondary axes, the plot order is dictated first by chart type, then within each chart type by axis. A combination of series that share chart type and axis is called a chart group.

The area chart series in the charts below are listed first, whether they are plotted on the primary or secondary axis.

Order of Legend Entries in Stacked Charts

Generally series are listed in the legend in the order they are plotted. However, in vertically stacked charts (line, column, and area), if the legend lists the series vertically, the entries are reversed so they match the stacking order: series one on the botton, series two above series one, etc. In the following examples, the series are plotted in the order one, two, and three.

Here are unstacked and stacked line charts, with normal and reverse legend entry order.

Here are unstacked and stacked column charts.

Here are unstacked and stacked area charts.

In a bar chart, whether clustered or stacked, entries in a vertically aligned legend are listed in the same order as they appear in the chart. The alignment matches the order of the bars in the chart, even if the order of the bars is reversed by plotting an axis in the reverse order.

Effect of Stacking, Axis, and Chart Type on Legend Entry Order

The order of all series within a chart group is reversed if the first chart group listed in the legend is stacked.

In the case where all series types are the same on primary and secondary axes, what matters is the stacking of the primary axis series. Unstacked primary series 1 and 2 in the chart at left are listed first, so no series are listed in reverse order, even though secondary series 3 and 4 are stacked. Primary stacked series 3 and 4 are listed first in the chart at right, so primary axis series 3 and 4 are reversed, as are secondary axis series 1 and 2, even though secondary series 1 and 2 are not stacked.

In the case where there are different chart types on primary and secondary axes, the behavior is dictated by the first chart group listed in the legend. In the chart at left, the secondary area series are stacked, so all chart groups (both the secondary area and primary column series) are listed in reverse order, regardless of whether the columns are stacked. In the chart at right, the secondary area series are unstacked, so no chart groups (neither secondary area nor primary column series) are listed in reverse order, even if the primary columns are stacked.

Unique Horizontal Bar Chart Legend Entry Ordering

Here are a clustered bar chart and a stacked bar chart, with series listed in the order they appear.

Here are a clustered bar chart with the category axis plotted in reverse order, and a stacked bar chart with thevalue axis plotted in reverse order. Again, the series are listed in the order that they appear in the chart.

This reordering of a legend based on a reversal of the order of an axis only appears in a bar chart with a vertically aligned legend.

Related Posts:

• Legend Entry Tricks in Excel Charts
• Legends in Excel Charts – Formats, Size, Shape, and Position
• Clustered-Stacked-Column Combo Chart With Lines
• Double Legend in a Single Chart
• Stack Columns In Order Of Size
• Clustered-Stacked Column Charts with Vertical Separators
• Clustered-Stacked Bar Charts
• Stock Charts in Excel 2007
• US Employment Slump Chart – How To
• Stacked Area Chart Challenge