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

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.

Peltier Tech Chart Utility

Comments

  1. Hey- like your posts!
    I’ve been trying to figure out since a long time but how do we get away with legend and use labels instead? When I use data labels in chart wizard(/options) the labels get attached to each data point instead of how neatly they’re displayed in your chart…

    Any help?

    thanks

  2. Hi SpeedBall –

    Thanks for the comment. In answer to your question, I use a very simple routine to label the last point of each series in the chart. It can be found in two places, in my blog at Label Each Series in a Chart, and on my tutorial site at Label the Last Point in an Excel Chart’s Series, where there is more discussion about the technique and development of the code.

  3. Jon,

    I have found difficulty in ordering legend entries via automation in VBA, especially if the chart has a dual Axis. I have macros that update the legend if an option box is selected to display the series. It may be a limitation of Excel 2003, but the series for one axis will be grouped first, then the series for the second axis. When selecting a series object, and trying to re-order the series on the Series Order Tab in the Format Data Series dialog box, only series for the axis in which it is ascribed to can be re-ordered. Perhaps this is a feature that I should learn to embrace.

    I utilize your techniques in setting up Dummy Series in the construction of charts, I wish there was a way to select an option in a dialog box to tell Excel to omit these series from Chart Legends. This would make macros that customizing legends easier, especially in dual-axis charts.

    Thank you for sharing in all of these posts, and for personally helping me out in other posts (http://www.dailydoseofexcel.com/archives/2008/08/15/my-coding-technique/).

  4. Hi Jerry –

    Working with legend entries in VBA is a challenge, because there is not a linkage between a series and its legend entry. We have suggested this for future developments of Excel by Microsoft. A legend object model linked to the series collection would be outrageous.

    Reordering is actually only possible within a chart group, which is a collection of series of a given type and on a given axis. This is more constrained than you mentioned, but if you’ve never tried this on a combination chart, you would not have run up against it.

    I talk about chart groups in a future post, the one talking about legend entry order. Wednesday, I think.

  5. Thanks a lot Jon!! you rock!

  6. My data required me to put the labels at the start of the series (the series are converging in the end) which made the data labels to overlay on themselves.

    I’ve added following bit of code for it to decide on its own what is the best place to put labels (start or end)…decision is based on stdev of the first and last data values.

    Dim xrange1, xrange2 As Variant
          
          If ActiveChart Is Nothing Then
            MsgBox "Select a chart and try again.", vbExclamation, "No Chart Selected"
            Exit Sub
          Else
            
            k = 0
            tot = ActiveChart.SeriesCollection.Count
            ReDim xrange1(1 To 1, 1 To tot)
            ReDim xrange2(1 To 1, 1 To tot)
            
            For Each mySrs In ActiveChart.SeriesCollection
                k = k + 1
                xrange1(1, k) = mySrs.Values(1)
                xrange2(1, k) = mySrs.Values(mySrs.Points.Count)
            Next mySrs
            
            stdev1 = WorksheetFunction.StDev(xrange1)
            stdev2 = WorksheetFunction.StDev(xrange2)
            
            For Each mySrs In ActiveChart.SeriesCollection
              bLabeled = False
              With mySrs
                ptcount = .Points.Count
                If stdev1 > stdev2 Then
                    startpt = 1
                    endpt = ptcount
                    xstp = 1
                Else
                    startpt = ptcount
                    endpt = 1
                    xstp = -1
                End If
                
                For iPts = startpt To endpt Step xstp
     
  7. SpeedBall –

    Great idea. I may just post that as a complete routine when I have a chance.

  8. Hi,

    The legend on my chart does not show/list all the data series which I have plotted in the chart. Initially I thought it was a size issue, and I’ve tried to resize it, but that didn’t work. Please help.

    Thanks!

  9. Shadowfax –

    If you have a great many series, you may need to change the height of the legend, to display more rows of legend entries, as well as the width of the legend, to display more columns.

  10. I may have missed this point in your discussion, but I have found that once a legend is created, it can be dragged anywhere in chart/plot area. In particular, when I have data whose plots gives space in the plot area, I like to drag the legend into the plot area and increase the plot area to fill the entire chart area. This is especially useful when space requirements limit the size of a chart.

  11. Larry –

    Good point. The article mentioned in passing that you can move the legend, but now it explicitly states that the legend can be clicked on and manually dragged to a new location.

  12. Jenni Killmer says:

    Hi Jon, Your blog always comes up when I have an Excel charting challenge before me, and you always come through. This page describes exactly what I was looking for, but in myxcel 2010, I find that “cell” boundaries do not show up when I resize the Legend box in my chart. Are you aware of an equivalent to this feature in Office 2010?

  13. Jenni –

    You just have to guess based on how the individual legend entries behave.

  14. Hi Jon,

    I’m struggling to understand how legend objects resize themselves automatically. I’ve been trying to use VBA to position my legend, but I have noticed that before I move the legend it has a certain width, and that width changes after I move the legend without me changing it (the legend.width).

    Is there some setting that I’ve missed to turn this “feature” off?

    I’ve also noticed that sometimes the plot area is also changed after I change some property of the chart or chartobject object in VBA. I checked out events, but could see nothing that might apply.

    Thanks for any help / comments / suggestions,

    Eric

  15. I must be missing something very basic, but I’m trying to find out how to change the text in the legend to reflect the actual content of the data (instead of “Series1″, “Series2″, etc.) I know I’ve done this in the past – earlier version of Excel? – but I’m just not seeing it in 2010. Can you point me? Thanks.

  16. To change the legend entry for a series you have to change the name of the series. If you select the series, you’ll see something like this in the formula bar:

    The easiest way is through the Select Data dialog.

    Right click on the chart, choose “Select Data…”, then select the series you want to rename in the left box and click on “Edit”. In the “Edit Series” dialog box you can change the name of the series, which will change the entry for it in the Legend.

  17. Whoops… forgot the series formula:

    =SERIES(,, )

    If you’ve got just “Series 1″ or the like for a series, then that entry will most probably be blank.

  18. I have a variable line graph with Legend. Its reading from H11:S16 (12 months data).
    For H16:S16, this row’s data might varies. A radio button is available to select either Product A or Product B.

    I have no problem to read and display the Product A data as it will show all 6 lines of data. But when I select Product B, it will be 1 line lesser than Product A.

    When selecting Product B, my graph is showing a “na” in the legend. How can I / How do I need to not display the “na” when I select on Product B ?

  19. You mean “#N/A” the error message, which is different from “na”, some arbitrary text, right? Does your chart still contain two series, but one is hidden using #N/A for the plotted data? This means you always have two legend entries in the legend. Replace the legend with data labels on the last point of both series. When a series is not plotted, its label is not shown. A legend with one entry is pretty much useless anyway.

  20. Yes, 2 series. Its not showing #n/a in the legend. Just na only. Cos that row 16 will display #n/a as i used this to hide( =na() ). Sorry, i dun quite.understand ‘Replace the legend with data labels on the last point of both series. ‘ Can u show me a screen shot pls?

  21. If you used =NA() in a formula, the result will be #N/A, not “na”. Check the formula.
    To see what I meant by labels, check the last two images before the subheader “Legend Placement” above.

  22. this was not very helpful in finding what i was looking for, it was to long and overdrawn

  23. Sidi –
    What were you looking for?

  24. Thank you very much for your helpful postings.

    Using Pivot Chart graphics as an example, what if you have 300 data values in your legend instead of only 6? It does not seem to be possible to enlarge the legend box’s perimeter enough to view all of them. Is there a way to be able to view all of them or scroll through them somehow?

    In Pivot Tables, one can double-click on a cell and learn what data is feeding into that cell. Is there a similar way to click on one of the illustrative lines in the Pivot Chart and see what data it is composed of? (This might also be an alternate way to view the data values that are not visible in the legend as described above.)

    Thank you very much for trying to help with this!

  25. That’s a huge legend. Is the chart even legible?
    You can’t scroll a legend. You can mouse over the plotted data as see some information about the points.
    I don’t use pivot charts much. I don’t even know if double clicking drills down into the data the way double clicking in the pivot table does.

  26. All I want to do is reverse the order of the legend. Is there a simple way to do this? I’ve looked everywhere. I’m using Excel 2010.

  27. Reversing the plot order of the data will reverse the legend, at least within series of a given chart type. Unfortunately it is often desirable to reverse the legend but keep the data in the same order. No easy solution.

  28. Is there a way to align the legend of a plot in Excel to the gridlines?
    I asked this question on [superuser](http://superuser.com/questions/629119/how-to-snap-legend-to-grid-in-microsoft-excel-2010-graph) and was pointed here.

  29. There is no automatic way to align a legend with anything.
    You can manually align it, and if the chart changes you are likely to have to realign it.
    Click on the legend and drag it with the mouse so the top of the legend (or bottom) lines up with a gridline. Then click on one of the resizing handles on the perimiter of the legend, and stretch the legend so that the legend entries are aligned between gridlines.
    I presume this is what you meant.

  30. When placing the legend on the chart, you can locate the legend boundaries at the midpoints between grid lines then adjust the transparency of the legend so that the grid lines show faintly behind the legend.

  31. My legend entries have too much white space between them on a single line. There are six entries and they should all fit nicely on one line, but the legend insists on wrapping them into two unless I make the chart outline enormous. I have changed the font all the way down to 8 pt, but it makes no difference.

  32. Carol –
    Make sure there aren’t extra space characters at the ends of the series names.

  33. Hello,

    How do I get the Legend to sit in FRONT of a shape in my chart? I have a 50% translucent gray rectangle that I want to put BEHIND the Legend, but the Legend remains behind the shape so that the shape contniues to partially obstruct the Legend.

    Thanks!

  34. Any shape in a chart floats in a layer in front of the other chart elements. You can’t put the shape behind the legend.

  35. Why does my 2010 Excel Graph Legend font size change after I have saved it to the size font I want?
    Example: Legend Font Size 4.4 – Changed to 8.0 and Saved – Reverts back to Font Size 4.4 after several days.
    Anybody have any ideas…………This is driving me CRAZY ! ! ! !

  36. Denise –
    That’s bizarre. Font size 4.4 is pretty small, isn’t it? Does Excel get drunk on the weekend and change everything?

  37. I have the same issue as Denise – very frustrating!

  38. Denise,

    I have been having the same issue with Excel 2010. Did you find a solution? VERY frustrating!

  39. I am trying to figure out how to remove numbers from a chart legend. I have Googled and asked my classmates with no luck.

  40. Where do the numbers come from? The legend displays the series names, usually taken from cells in the worksheet. You can change the contents of those cells, or you can click Select Data on the Chart Tools > Design ribbon tab, then select a series in the Select Data Source dialog that appears, click Edit, and type in a different series name or link to a different cell.

  41. “The best legend is no legend.”

    Surely you’re joking.

  42. Rose – Not joking at all. Labeling the points directly is much better than using a legend.

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.