Legend Entry

Order of Series and Legend Entries in Excel Charts

Tuesday, December 31, 2019 by Jon Peltier 3 Comments

Tuesday, December 31, 2019 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Chart Series and Legends

In Excel charts, series are drawn in a particular order and legend entries are listed in their own particular order, based on series number, series chart type, the axis a series is plotted on, and other features, like axis category order and whether series are stacked. People often ask how to move series within a chart or within a legend. This article should help explain what is even possible.

Single Chart Type

If a chart has one type of series, all on the primary axis, the series are plotted in the order that they are added to the chart. The legend usually lists them in this order as well.

Line Charts

The line charts below illustrate the layering of series. Whether lines with markers or just lines, the second series in red is always plotted in front of the first series in blue. Even to the extent that the red line in the bottom right chart covers the blue markers. The legends all list the blue series first, then the red series.

An interesting inversion occurs if one series has markers and no lines. The markers only series is plotted in front of the line series (top row of charts below) or in front of the lines with markers series (bottom row). In both right-hang charts, the blue markers of the first series or drawn in front of the red series. The legends still list blue first, then red.

XY scatter charts show the same behavior as line charts, with series plotted in order except in the case where one series has markers and no lines.

Column and Bar Charts

In unstacked horizontal bar charts, legend entries appear in the same order that the series appear in the chart. Below left, red bars and legend entries appear before blue.

Below right, the categories have been reversed in the vertical axis. Now blue bars and legend entries appear before red.

Legend entries in bar charts appear in the same order as the data in the chart. When reversing the axis categories switches the positions of the bars, it also reverses the legend.

This swapping of the legend to match the arrangement of the bars helps with interpretation of the chart. You might expect column charts to show the same alignment of legend entries with plotted points.

In the original chart, below left, the blue series 1 columns and legend entry appear before the red series 2 columns and legend entry. Reversing the categories in the horizontal axis does reverse the positions of the bars, but the legend keeps its original order, so the legend entries are now out of sync with the columns.

Legend entries in column charts don't always appear in the same order as the data in the chart. While reversing the axis categories switches the positions of the bars, it does not reverse the legend.

Effect of Stacking

In stacked charts (area, bar, and column charts), each series is stacked on the previous series, so the chart shows totals of all series at each category. The legends are rearranged to list series in the order they appear in the stack. This helps the viewer interpret the plotted data.

Note: You can also stack line chart series, but it’s not a good idea to do so; stacked line charts can be very confusing.

In the unstacked area chart below left, series 1 in blue is plotted first, and series 2 in red is plotted in front, partially obscuring series 1. The legend lists the series in this order.

In the stacked area chart below right, the blue area is plotted first and the red area is stacked on top. The legend is reversed, so that series 2, plotted at higher Y values than series 1, is also listed higher in the legend.

Order of Stacked Area Chart Legend Matches Order of Stacking

In an unstacked bar chart (below left), the legend lists series in the order they appear: red above blue. In a stacked bar chart (below right), the legend also lists series in the order that they are stacked, red to the right of blue.

Order of Stacked Bar Chart Legend Matches Order of Stacking

In an unstacked column chart (below left), the legend lists series in the order they are plotted: blue before red. In a stacked column chart (below right), the legend lists series in the order that they appear, red above blue.

Order of Stacked Column Chart Legend Matches Order of Stacking

Mixed Chart Types (Combination Charts)

The order in which data is plotted in the chart and listed in the legend becomes more complicated when multiple chart types are used in the same chart. The rules are straightforward, but they aren’t documented anywhere, so people get confused.

Series Order by Chart Type

The order that series are plotted in the chart and listed in the legend follows this order of chart types: Area, Column and/or Bar, Line, and XY Scatter. Changing the plot order (by rearranging series in the Select Data Source dialog or by changing the last argument in the Series Formula) will rearrange series within a type, but will not move series out of their plot type order.

Start with this simple data and insert a clustered column chart (usually the default type). Then right click on any series and select Change Series Chart Type from the pop up menu. Change series “Area 1” to an area, keep “Column 2” as a column, and change “Line 3” to a line. The blue area series is drawn behind the other series and listed first in the legend, the red columns are drawn in front of the blue area and listed in the middle, and the gold line is plotted in front of the others and listed last.

Area series are plotted in back of other series, line series are plotted in front, and bar and column series are plotted in between.

Big deal, you may think, that’s the order that the data was arranged in the worksheet. Reverse all that, and the line will be drawn first, behind the others, while the area will be drawn last, obscuring the rest.

Below is the data in reverse order and the resulting column chart. Again, right click on any series and select Change Series Chart Type. Change “Line1” to a line, keep “Column 2” as a column, and change “Area 3” to an area. The gold area series is drawn behind the other series and listed first, the red columns are drawn in front of the gold area and listed next, and the blue line is plotted in front of the others and listed last.

So we see that the chart type dictates the order in which series are drawn and listed, regardless of the order of series data within all of the data in the chart.

We can extend this further to show that an XY Scatter series will be plotted in front of all the other series, regardless of where it falls in the chart source data.

Area series are plotted in back of other series, line series are plotted in front (and XY scatter series in front of lines), and bar and column series are plotted in between.

Primary and Secondary Axes

Earlier I wrote that the order of series by chart type was Area, Column and/or Bar, Line, and XY Scatter. I placed Column and Bar together because if your chart contains both column and bar series, they are plotted in the same layer between areas and lines, and they are listed together in the legend. Their precise order depends on which axis each is assigned to.

Below I’ve added a bar chart series to the first combination chart above. A bar chart series cannot be plotted on the same axis group as another chart type, so in the chart below, the area, column, and line series are plotted on the primary axis, and the bar is plotted on the secondary axis, so that the gray bars are in front of the red columns. Note that the legend order is area first, line last, and column and bar in the middle. Because the column series is on the primary axis, it is listed before the bar series.

Next I’ve added a bar chart series to the second combination chart above. I’ve plotted the bar on the primary axis, while the area, column, and line series are plotted on the secondary axis. Note that the gray bars are behind the red columns. The legend order is still area first, line last, and column and bar in the middle. Because the bar series is on the primary axis, it is listed before the column series.

The following chart has no bar series, but area, column, and line series on each of the primary and secondary axes. All areas are listed first, then all columns, then all lines; within each chart type, the primary series are listed before the secondary series.

Series are plotted and listed in the legend in a particular order, based on series type, axis, and other factors.

Legend ordering can be even more intricate. Below is four series of data and a column chart.

In the chart below left, series 2 and 3 have been moved to the secondary axis. Note that the legend lists series 1 and 4 first for the primary axis, then series 2 and 3.

In the chart below right, series 2 and 3 on the secondary axis have been changed to stacked columns. The legend still lists primary axis series 1 and 4 in that order, but lists secondary series 3 and 2 in the order they re stacked.

Earlier I showed how a line chart series with markers and no line will be plotted in front of a later line chart series with a line (with or without markers). This happens if both series are on the same (e.g., primary) axis.

Below left: red series 2 markers plotted in front of blue series 1 line and markers. Below right: blue series 1 markers plotted in front of red series 2 line and markers.

If the series with markers only is on the primary axis and the series with the line (with or without markers) is on the secondary axis, the primary markers will not be plotted in front of the secondary series.

Below left: blue secondary series 1 plotted in front of red primary axis series 2. Below right: red secondary series 2 plotted in front of blue primary series 1.

Legends with Many Entries

Legends can get complicated when there are many charted series and many entries in the legend.

Legends with Multiple Rows and Columns

The following chart has a legend across the bottom, listing all 8 series in a horizontal row.

If you shrink the width of the chart, eventually the legend will no longer fit. Excel converts the legend to two rows. Shrink the chart further, and the legend will change to three rows, then four.

Excel tries to place the same number of entries into each row: 8 entries in the original legend, then 4+4 entries, then 3+3+2, and finally 2+2+2+2.

It’s a little different with a vertically aligned legend. Below left is the same chart as above, with the legend listing the series along the right edge of the chart. Shrink the chart so that the legend no longer fits, and Excel does not convert it to two columns, instead it simply drops items off the list.

In its automatic legends, Excel doesn’t like the entries to get too close together, but you can manually change the height of the legend, and more entries will fit. In the chart below left, the chart is the same size, and so is the font, but I’ve slightly stretched the legend and all eight entries appear. You can even shrink the legend, and push the legend entries together. (Shrink it too much, and again, you will lose items off the bottom of the list.)

Alternatively, you can widen the legend, and Excel will add a column of legend entries (below right).

If your series names have different lengths but they fit in one row, Excel will position legend entries so the spaces between them are equal.

When you shrink the chart so that the legend reverts to multiple rows, Excel gives all legend entries the same amount of room. Note the wide spaces between the short legend entries below. I’ve used a light gray border on the legend to help illustrate this behavior.

This effect continues with more rows of legend entries.

Partial List of Series

I was working on a research project management dashboard with a client, and he was showing a stacked area chart that showed data about his various projects over time. Like many analyses of this sort, most of the total was due to a small number of items. He wanted to show a list of the top N projects in the legend. He was able to do it once, but then couldn’t remember how he did it.

In my mock-up below, I have eight “projects” stacked up, and I want to show the largest four contributors to the total. These two charts have all eight series listed in the legend.

In the next two charts, I have reduced the sizes of the legends so that half of the legend entries have disappeared. The left chart has the vertical list I want, except it shows the four smallest series. The right chart shows the four largest series, but they are in a horizontal list. But I can start with the horizontal legend to produce the vertical list I want.

Below left, I have moved the legend to the top right corner of the plot area. Below right I have made the legend taller and narrower to force Excel to list the entries in one column.

Finally a little cleanup and I have the desired list of the four largest series.

The client was thrilled when I showed how he could reproduce his top ten list of projects by starting with his legend at the bottom of the chart instead of the right side.

Slim Down Those Fat Legend Keys

Monday, April 30, 2018 by Jon Peltier 9 Comments

Monday, April 30, 2018 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

My Big Fat Legend Keys

A client had a combination chart in his report, with some columns and some lines, and was complaining about the legend. It seems that when he added the lines, the column entries in the legend had a wide rectangle showing the series color, rather than a square, so the legend was taking up way too much space. The left hand chart below shows this fat-legend problem, while the right hand chart has no problem because I’ve fixed it.

Fat and Thin Legend Keys

Normally, the fill-type of series (column charts, area charts, bar charts) use a simple square for the legend key, the colored part of the legend entry that helps the user determine which series is which.

Fill-Type Charts with Thin Legend Keys

In contrast, line charts have a wider legend key, which allow for the display of a marker and a line.

Line Charts with Wide Legend Keys

The problematic charts are mixed types. Wider legend keys are needed to show the lines for the line chart types, and to accommodate the added width, the squares for the fill-type charts are widened.

Mixed Charts with Wide Legend Keys

And I thought, wouldn’t we all like to be a little thinner? So I played around a bit, and I’ve found a way to fix this.

First, I noticed that if the line charts had markers but no lines, the legend keys were only as wide as the markers.

Marker Charts with Thin Legend Keys

So I played a bit more, and discovered that I could format the whole line series to have no lines, but then format the individual points to have lines, and the legend would only show the whole series format, not the individual point format.

Fix the Fat Legend Keys Manually

I’ll show you the manual protocol first, then I’ll share some VBA that will remove the drudgery of it all.

We’ll start with a combination chart with columns and one line series (top left).

The first step is to format the line chart series with markers but no lines (top right).

We select the first point, and format it to have a line, which does not appear, because it connects the first point to the point before that, which doesn’t exist (middle left).

We then select the second point, and format it also to have a line, which is now visible (middle right).

We select the third point and repeat (bottom left).

Finally, we repeat this for the rest of the points (bottom right).

Protocol for Line and Marker

That wasn’t so hard, was it?

What if the line chart has no markers? Well, it’s almost the same. Again we start with a combination chart with columns and one line series (top left).

The first step is to format the line chart series, adding markers but removing the lines (top right).

We select the first point, and format it to have a line but no marker. Neither the line nor the marker appear (middle left).

We then select the second point, and format it also to have a line but no marker (middle right).

We select the third point and repeat (bottom left). See where this is going?

Finally, we repeat this for the rest of the points (bottom right).

Protocol for Line Only

Fix the Fat Legend Keys Programmatically

So the protocol isn’t too hard, but you can just tell it’s going to get tedious when there are a lot of charts to process. So I’m here to streamline the process with a little VBA.

The core procedure inputs one line chart series and handles it as above. It first formats the entire series with a marker and no line. If the series originally had no marker, it temporarily uses a square; otherwise it uses whatever marker was present. Then it formats each point in turn to have a line, and if it originally had no marker, it removes the marker from that point.

Sub RemoveLegendLinesFromSeries(srs As Series)
  Dim iPt As Long, nPts As Long
  Dim bNoMarkers As Boolean
  
  nPts = srs.Points.Count
  Select Case srs.ChartType
    Case xlLine, xlLineStacked, xlLineStacked100
      bNoMarkers = True
    Case xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100
      bNoMarkers = False
    Case Else
      GoTo ExitSub
  End Select
  
  If bNoMarkers Then
    srs.MarkerStyle = xlMarkerStyleSquare
  End If
  
  srs.Format.Line.Visible = msoFalse
  
  For iPt = 1 To nPts
    With srs.Points(iPt)
      .Format.Line.Visible = True
      If bNoMarkers Then
        .MarkerStyle = xlMarkerStyleNone
      End If
    End With
  Next
  
ExitSub:

End Sub

The next procedure inputs an entire chart, doing each line chart series and ignoring the others.

Sub RemoveLegendLinesFromChart(cht As Chart)
  Dim srs As Series
  
  For Each srs In cht.SeriesCollection
    Select Case srs.ChartType
      Case xlLine, xlLineStacked, xlLineStacked100, xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100
        RemoveLegendLinesFromSeries srs
      Case Else
        ' nope
    End Select
  Next
End Sub

We have two entry-point procedures: the first handles the active chart.

Sub RemoveLegendLinesFromActiveChart()
  RemoveLegendLinesFromChart ActiveChart
End Sub

The second entry-point procedure figures out what the user has selected, and acts accordingly. If the user has selected a series, a point, a set of data labels, or a single data label, the procedure processes just the applicable series. If the user has selected a chart (i.e., there is an active chart, but none of the previously listed elements are selected), the procedure takes care of the whole chart. If the user has selected multiple charts, then all charts are handled. In fact, since this procedure handles an active chart, the previous procedure isn’t needed after all.

Sub RemoveLegendLinesFromSelection()
  Dim sh As Shape
  
  If Not ActiveChart Is Nothing Then
    Select Case TypeName(Selection)
      Case "Series"
        RemoveLegendLinesFromSeries Selection
      Case "DataLabels", "Point"
        RemoveLegendLinesFromSeries Selection.Parent
      Case "DataLabel"
        RemoveLegendLinesFromSeries Selection.Parent.Parent
      Case Else
        RemoveLegendLinesFromActiveChart
    End Select
  ElseIf TypeName(Selection) = "DrawingObjects" Then
    For Each sh In Selection.ShapeRange
      If sh.HasChart Then
        RemoveLegendLinesFromChart sh.Chart
      End If
    Next
  End If
End Sub

I selected the mixed charts from the screenshot in the first section above, and ran this last procedure, and in an instant, the legend keys were brought down to size.

Mixed Charts with Thin Legend Keys

Posted: Monday, April 30th, 2018 under Formatting.
Tags: Legend, Legend Entry, Legend Key, VBA.
Comments: 9

Microsoft MVP Logo

Order of Legend Entries in Excel Charts

Friday, February 13, 2009 by Jon Peltier 30 Comments

Friday, February 13, 2009 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

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 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 Axes Legend 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 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 Chart Legend Order in Stacked Line Chart

Here are unstacked and stacked column charts.

Legend Order in Column Chart Legend Order in Stacked Column Chart

Here are unstacked and stacked area charts.

Legend Order in Area Chart Legend 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 Axes Legend 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 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 Chart Legend 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 Axis Legend 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.

Posted: Friday, February 13th, 2009 under Formatting.
Tags: Legend, Legend Entry.
Comments: 30

Microsoft MVP Logo

Legend Entry Tricks in Excel Charts

Wednesday, February 11, 2009 by Jon Peltier 65 Comments

Wednesday, February 11, 2009 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

This is a continuation of Legends in Excel Charts – Formats, Size, Shape, and Position, which started a discussion about the legends in Excel charts. That article talked about legend formatting, and how the best legend is no legend at all: it’s more effective to put the text labels right on the data. But sometimes you just need a legend. The article also showed the default legend configurations, and showed how to change these to suit.

This article continues the discussion, showing how to use the legend components–legend entries and legend keys–to access more advanced legend functionality.

Legend Entries

Legend entries are treated somewhat independently of each other. You can select a legend entry with two single clicks, one to select the whole legend and the other to select the entry itself.

Legend Entry

You can format the text of the legend entry. I’ve bolded the legend entry “two” and to enhance the highlighting of this series I’ve used a thicker line in the chart.

Format the Legend Entry

You can selectively remove a legend entry by selecting it and deleting it, while leaving the series in the chart.

Deleting the Legend Entry Removes it From the Legend

In order to restore any legend entries to the legend, you must delete the whole legend, then add the default legend back to the chart, and reapply any custom formatting.

Don’t select the legend key before pressing the Delete key . . .

Legend Key

. . . because you will delete the series itself, not just the legend entry.

Deleting the Legend Key Deletes the Series

However, you can select the legend key then format it, which in turn formats the series in the chart.

Formatting the Legend Key Formats the Series

This last section does not apply to Excel 2007. You cannot select the legend entry in a chart in Excel 2007. I don’t know why this was changed, because it seemed a nice alternative way to access a series when you otherwise could not select it.

Multiple Legends in a Chart

Sometimes people want to split the legend into different parts. This is not possible. However, like many other visual effects in Excel charts, you can fake it. Here I have two charts, my working chart on the left, and a dummy chart on the right. In the working chart, I have removed the entries I don’t want in one of the legends. In the dummy chart I have removed the entries that are in the working chart’s legend.

Half of a Legend Other Half of a Legend

I then used a screen capture program (I use SnagIt) to copy the legend from the dummy chart, then I selected the working chart, and pasted this image into the chart, and moved it to where I wanted it.

Dual Legends

This is not a dynamic solution: if a label or series format changes, the static image which comprises the second legend may have to be refreshed. This is why I make a separate dummy chart as above. Both charts use the same data and series formats, so I only need to repeat the copy and paste to update my dual legend chart.

Follow up posts in the blog: Order of Legend Entries in Excel Charts, which describes some of the intricacies of legend entry order, and Double Legend in a Single Chart, which shows a dummy series trick to add multiple legends to a single chart.

Posted: Wednesday, February 11th, 2009 under Formatting.
Tags: Legend, Legend Entry, Legend Key.
Comments: 65

Microsoft MVP Logo

Legends in Excel Charts – Formats, Size, Shape, and Position

Monday, February 9, 2009 by Jon Peltier 44 Comments

Most charts use some kind of legend to help readers decode the charted data. Excel charts have legends by default, and these legends have a great deal of flexibility (as well as some frustrating constraints).

The best legend is actually no legend at all. A legend by its very nature is somewhat separated from the chart, so a reader must move his eyes back and forth to decode the chart. Placing labels directly on or adjacent to the plotted data is more effective, but it can be more work, and for charts with a lot of data that’s entangled together, data labels may not be practical.

Legend Formatting

The default formatting for the borders in a chart is black. The plot area is also filled with a muddy gray color, but I’ve removed that already.

Legend with Default Black Borders

That’s so many dark lines that I forget I’m supposed to look there for some data. This can be improved by removing the outermost border and using a light gray for the plot area and legend borders.

Legend with Gray Borders

In fact, that’s still too many visible lines, but I’ll use the light gray in this article so the outlines of the plot and legend are clear.

Without any extraneous borders at all, the data stands out in the chart. Ah, now that’s why we made a graph, to show the data.

Legend without Borders

The problem with a legend is that it’s somewhat removed from the data. You can move the legend so it’s more integrated into the chart. Just click on the legend, and drag it by its edges to a new location.

Legend Closer to the Action

You could even tweak the colors in the legend, to make the legend blend in.

Legend with Colored Text

Of course, the best legend at all is no legend. Identify the data instead with labels attached to the data series.

Legend Removed in Favor of Data Labels

The labels are a bit close together, but matching their text colors to the series colors improves the separation between adjacent labels.

Legend Removed in Favor of Colored Data Labels

Legend Placement

The Excel 2003 Format Legend dialog is shown below, with the Placement tab visible. There are five predefined placements, and the default placement is Right.

Format Legend Dialog (Excel 2003)

Here is a chart with the legend in the default Right position.

Default Right Legend Position

This chart has its legend in the awkward Left position. If the value axis were on the opposite side of the chart, this would not be so awkward.

Left Legend Position

This legend is at the top of the chart.

Top Legend Position

This legend is at the bottom of the chart. This is probably less effective than the top position, because the horizontal axis comes between the legend and the chart it describes.

Bottom Legend Position

This legend is in the least useful corner position. Even Excel knows this is an untenable position. When this position is selected, the chart scrunches up into the opposite corner, and the chart’s text autoshrinks to 5.5 pt. When you change the font to a legible size, like 8 pt, the legend moves to near the right position and the chart itself expands to its original size.

Corner Legend Position

The default placements, at least right and top, are okay. But Excel leaves too much space around the legend and between the legend and the rest of the chart. I start with a default legend position, then reposition and resize the legend to suit my tastes.

Automatic Resizing and Rearranging of the Legend

Let’s return to the the Top position to see a few characteristics of the legend. The legend entries are evenly distributed across the legend.

Top Legend Position

Suppose the labelsin the legend entries different lengths. All of the entries move to maintain a nice distribution.

Top Legend Position

If the legend entries are about the same width, but the chart itself cannot accommodate them all, Excel wraps them to fit.

Legend Resized to Fit

Manually Resizing and Rearranging the Legend

When you select the legend, its edges and corners are highlighted with the familiar handles that allow resizing. Drag the legend by its edges to move it to a new location.

Legend Resizing

When you begin resizing the legend, the boundaries around the legend entries are displayed.

Legend Resizing

As this legend is made narrower, the first legend entry wraps to two lines. The other legend entries assume the same dimensions as the largest one, and to accommodate the taller entries, the last one is pushed below the bottom edge of the legend.

Legend Resizing

When the mouse is released, only five entries are visible.

Legend Resizing

The legend has to be stretched downward to provide room for the last entry.

Legend Resizing

Now they all fit, but the spacing doesn’t look very nice. Unless all legend entries have to wrap, it’s probably better to make the legend wide enough to prevent wrapping.

Legend Resizing

As soon as the legend is being manually resized, all legend entries assume the same dimensions. The other five entries in this legend forget that they had resized themselves to accommodate the longer “one hundred” label.

Legend Resizing

This legend has been rearranged into a 2 row by three column grid.

2 by 3 Legend Grid

This legend has been resized to a 3 row by 2 column grid.

3 by 2 Legend Grid

By default, the entries are listed in order, filling rows first, then columns. For example, the entries for one and two are side by side.

Legend Resizing

If you’d like the entries to be listed in order filling columns then rows, with one above two above three, you need to rearrange the order of series in the chart. Series one is plotted first, four second, two third, five fourth, and so on.

Legend Resizing

Follow up posts in the blog: Legend Entry Tricks in Excel Charts, which will feature formatting tricks enabled by the individual entries in a chart legend, Order of Legend Entries in Excel Charts, which will describe some of the intricacies of legend entry order, and Double Legend in a Single Chart, which shows a dummy series approach to adding multiple legends in a chart.

Posted: Monday, February 9th, 2009 under Formatting.
Tags: Data Labels, Legend, Legend Entry, Legend Key.
Comments: 44

Microsoft MVP Logo

Legend Entry

Chart Series and Legends

Single Chart Type

Line Charts

Column and Bar Charts

Effect of Stacking

Mixed Chart Types (Combination Charts)

Series Order by Chart Type

Primary and Secondary Axes

Legends with Many Entries

Legends with Multiple Rows and Columns

Partial List of Series

Other Posts About Legends

My Big Fat Legend Keys

Fix the Fat Legend Keys Manually

Fix the Fat Legend Keys Programmatically