Formatting

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.

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.

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.

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.

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

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

The labels are a bit close together, but matching their text colors to the series colors improves the separation between adjacent 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.

Here is a chart with the legend in the default Right 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.

This legend is at the top of the chart.

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.

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.

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.

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

If the legend entries are about the same width, but the chart itself cannot accommodate them all, Excel wraps them 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.

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

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.

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

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

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.

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.

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

This legend has been resized to a 3 row by 2 column 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.

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.

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.