Order of Legend Entries in Excel Charts

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).

Legend Order in Combination Chart with Areas, Columns, and LinesLegend Order in Combination Chart with Areas, Columns, and Lines

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.

Legend Order in Chart with Primary and Secondary AxesLegend Order in Combination Chart with Primary and Secondary Axes

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.

Legend Order in Combination Chart with Primary Areas and Secondary ColumnsLegend Order in Combination Chart with Secondary Areas and Primary Columns

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.

Legend Order in Line ChartLegend Order in Stacked Line Chart

Here are unstacked and stacked column charts.

Legend Order in Column ChartLegend Order in Stacked Column Chart

Here are unstacked and stacked area charts.

Legend Order in Area ChartLegend Order in Stacked Area Chart

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.

Legend Order in Chart with Primary and Secondary AxesLegend Order in Combination Chart with Primary and Secondary Axes

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.

Legend Order in Combination Chart with Secondary Stacked Areas and Primary ColumnsLegend Order in Combination Chart with Secondary Areas and Primary Stacked Columns

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.

Legend Order in Bar ChartLegend Order in Stacked Bar Chart

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.

Legend Order in Bar Chart with Reversed Category AxisLegend Order in Stacked Bar Chart with Reversed Value Axis

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.

Peltier Tech Chart Utility

Comments

  1. Brilliantly explained as usual Jon, thanks. Makes (almost) perfect sense now you’ve explained the ground rules, why can’t Microsoft document their own products to this degree?.

  2. Hi Jon,

    Are going to cover Trend lines in another article?

    About 4 years ago I tried to create a addin that would allow users to select which items would appear or be hidden in the legend. But I gave up in the end as the combinations you have describe were just too convoluted to code and get reliable results.

  3. Yeah, trendlines mess up the legend, and I forgot about that. I think they (and error bars) also mess up the series indexes and the series sequence, but I don’t recall exactly how.

    I’d like to see a simple property added to the Series object: AppearsInLegend. Even better, how about making the LegendEntry object a member of the Series class, then adding .Visible to the LegendEntry. Unfortunately for 2007, they removed more than they added to Excel charting. I do not have high hopes for improvements in Excel 14.

  4. Either of those suggestions Jon would be a useful addition to the OM.

  5. Great research and explaining.

    The order in which the automated Microsoft legend appears is one of the reason I usually create them manually in PowerPoint charts. A bit more work but 100% in my control.

  6. What should I do with a combination chart (column and line) that does not seem to follow your rules? I have 6 legend entries, 3 are column and 3 are line. I cannot seem to sort the legend so that it comes up as:

    — North Plan [ ] North Actual
    - – South Plan [ ] South Actual
    ->- West Plan [ ] West Actual

    No amount of fiddling I do with the series order will get it to line up nicely. All I get is:

    — North Plan [ ] North Actual
    - – South Plan [ ] South Actual
    [ ] West Actual ->- West Plan

  7. Indigo -

    When I make a chart such as you describe, I get three column series in the legend followed by three line series. When I set up the legend to show three rows and two columns of entries, the top two and the first in the middle row are column series. There’s no way to get the lines in one column of legend entries and the columns in the other. You could make a two-row legend, with all the column series in one row.

  8. Thanks for this informative post. It really helped me get a chart looking the way I wanted it to. My problem was that I wanted to show only a subset of the legend, so I need the ones I wanted to show near the top of the legend. I couldn’t do this because I had a secondary vertical axis, and one of the legends that I wanted near the top was on this axis. I ended up creating a small phantom series on the primary vertical axis with the legend entry that I wanted to show. This phantom series was tiny, in my case a really short line segment, but I gave it the right series name and line type to give the legend entry I wanted.. Now its entry is near the top of the legend like I want, but you can’t see the actual series on the chart. Problem solved.

  9. John -

    Good job. Just because it doesn’t seem like Excel can do something, doesn’t mean you should stop trying to think of other ways to do it. I like your phrase “phantom series” better than the “dummy series” I’ve been using all along. I might have to adopt it as my own.

  10. Jon – You’ve got a great site, an excellent gift to the world. However, I’m afraid it won’t be long before I won’t be able to go to your site at work because my IT people will probably block it — Everytime I go to your site, your site meter trips my Trend Micro OfficeScan, which complains about the link to htpp://i.copygator.com/?cg=4a310, which it regards as a “malicious site” and duly notifies my IT department.

    I don’t know if others are getting this as well, and I don’t know if there’s anything that you can do (or care to do) about it, but I thought you might like to know that it may prove to be a significant issue to some people (such as me).

    Cheers

  11. Marvin -

    That URL only links to an image file, so I’ve stored the image on my host server. If that messes up how copygator works, I’ll hose that altogether.

  12. Great explanation, shame the Microsoft documentation didn’t cover this, but more of a shame that the programmers thought this was an acceptable solution – renders Excel Charting totally useless for my purposes!
    Thanks again.

  13. Very informative.

    Is there anyway either (direct or rigging) to change the order of legend entries displayed in legend key?

    I have graph with actuals and forecast entries for 1st 3 months displayed from left to right then just forecast entries for next 8 months and would like to display legend key with actual legend entries on left and forecast on right to match graph display.

    Any insight/direction you can provide on this would be greatly appreciated.

    Thanks,
    Pete

  14. Found a way using your related posts. Very helpful.

    Pete

  15. Pete: please provide a link or more details.

  16. Found it — Here’s how to change the order of the data series:
    Right click on the data series (the plot on the chart) and select “Format Data Series,” then click “Series Order.”

  17. Sorry if this has been covered, I didn’t read everything in detail…

    I had several line charts (max, avg, min on the main/left vertical axis) and a bar chart (std dev on the secondary/right vertical axis) on the graph but somehow the bar chart ended up on top of the legend. This makes sense according to what is written above, where bar charts take precedence over line charts. However, if you change the bar chart to a line chart you can move it to where you want and then change it back to a bar chart, and the legend will look the way I wanted it, with the three line charts on top and the bar chart on the bottom.

    I’m sure it’s unnecessarily tetchy and will reorder itself if and when I make some other change, but it’s good for now.

  18. Bob -

    Thanks for confusing the issue,

    I wrote up this tutorial based on what I’ve observed over many years in Excel 97 through 2003. There were consistent rules which were always followed. 2007 came along and broke some of these rules, usually when you least expected. Then 2010 fixed some rules and broke a few others.

  19. Hi Jon,
    I am running excel 2010 and the file has a marco so it is an .xlsm but the marco is unrealated the chart details.

    I have a graph which has 5 stacked areas on the main axis, 5 xy line-points on the secondary axis, and then another series of xy points on the secondary axis. I only want to have the stacked areas and the xy points on the legend as the xy line-points have their labels on the chart.
    Following what you suggest around the rules I moved the xy point series to place 11 (this did not change the legend) but I saved it, closed and reopened and it came through correct, Awesome! I close the sheet and come back later to copy the graph into a report and it has reverted back to returning one of the xy line-points as the 6th category :( with the xy points still in position 11. I don’t know why this behaviour is happening, do you?

  20. I’ve seen inconsistencies in the order of legend entries between Excel 2003, 2007, and 2010. I haven’t used 2013 enough to find more, but I’m sure they exist.

  21. When I initially commented I clicked the “Notify me when new comments are added” checkbox and
    now each time a comment is added I get three emails with the same comment.
    Is there any way you can remove me from that service? Thanks!

  22. Your email does not appear in the list of subscribed comments for this or any post.

  23. How do I change one letter of a word in a figure legend so that it can be a symbol? I have the correct writing of it, but it converts the symbol back to the regular letter and you can’t select just one letter to change font. I need to put TGFb1 with the b as a the symbol ‘beta”.

  24. Tammy -
    Since you can’t select one character at a time for formatting, you need to hunt through the upper characters of your font for a suitable symbol. You may need to use another font that has a greater variety of these symbols. For example, there are fonts designed for chemists that incorporate superscripted and subscripted numerals, greek letters, and other technical symbols.

Subscribe without commenting

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites