Putting a Chart Legend On Its Own Page

Lorna wonders if it is possible to put the legend associated with a chart onto a different page than the chart itself. She is submitting a paper to a journal that wants them separated.

The short answer is that Excel has made the legend and the chart integral to each other and there is not a quick and easy way to separate the two. There is a way to trick Excel into thinking that both elements exist, however. You start to do this by making an additional copy of the chart:

1. Select the chart.
2. Make sure the Home tab on the ribbon is displayed.
3. Click the Copy tool (in the Clipboard group) or press CTRL+C. Excel copies the chart.
4. Click in the area you want the legend to appear.
5. Click on the Paste tool or press Ctrl+V. Excel pastes the chart.

You now have two copies of the chart. One will be used for the actual chart (Chart 1) and the other chart (Chart 2) will be the legend. Hiding the legend from Chart 1 is simple and can be accomplished as follows:

1. Single click on the legend within the chart.
2. Right-click and select Delete or press the Delete key on the keyboard.

The legend no longer appears with the chart. But you have only solved half of the problem. Now you need to work with Chart 2 and isolate the legend on its own page. There are a couple of options to do this.

One option is to use screen capture to obtain a separate image of the legend. For this method, you will need to use a graphics program to trim the image (so it contains just the legend) before placing it back into Excel. Create the chart as usual, then follow these general steps:

1. Use whatever method you prefer to create a screen capture of the legend portion of the chart.
2. Using a graphics program, such as Paint or Adobe Illustrator, trim the image so it contains only the legend.
3. In Excel, make sure the Insert tab on the ribbon is displayed.
4. Click on the Picture tool, in the Illustrations group. Excel displays the Insert Picture dialog box.
5. Select the file you created in step 2.
6. Click Insert. The image appears on your worksheet.

At this point, the legend is a simple picture and can be moved to any location you desire.

Another option involves "hiding" the chart so the only visible item in Chart 2 is the legend.

1. Click on Chart 2 so it is selected.
2. Single click on the X-axis numbers.
3. Press the Delete key. The axis is removed.
4. Single click on the Y-axis numbers.
5. Press the Delete key. The axis is removed.
6. Select the gridlines.
7. Press the Delete key. The gridlines are removed.

Some of the chart elements are now gone, but you are only partially done. How you proceed from this point depends on the version of Excel you are using. If you are using Excel 2007 or Excel 2010, then follow these steps:

1. Right-click the data series and choose Format Data Series from the Context menu. Excel displays the Format Data Series dialog box.
2. At the left side of the dialog box click Fill. (See Figure 1.)



Figure 1. The Format Data Series dialog box.

3. Select the No Fill radio button.
4. At the left side of the dialog box click Border Color.
5. Select the No Line radio button.
6. Click Close. Excel has "hidden" the chart.

If you are using Excel 2013, then these steps are for you:

1. Right-click the data series and choose Format Data Series from the Context menu. Excel displays the Format Data Series task pane at the right side of the chart.
2. Click the Fill icon (it looks like a bucket spilling paint). Excel displays two options: Fill and Border.
3. Click the Fill option. It expands to show different ways you can have Excel fill the data series.
4. Select the No Fill radio button.
5. Click the Border option. It expands to show different ways you can have Excel render the border of the data series.
6. Select the No Line radio button.
7. Close the Format Data Series task pane. Excel has "hidden" the chart.

The only element visible at this point is the legend. Keep in mind that the chart is still there, it has just been "hidden" by changing its appearance. This is a long workaround but it gets the job done.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8066) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Putting a Chart Legend On Its Own Page.