Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Putting a Chart Legend On Its Own Page.

Putting a Chart Legend On Its Own Page

by Allen Wyatt
(last updated November 11, 2015)

2

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.)
  3. Figure 1. The Format Data Series dialog box.

  4. Select the No Fill radio button.
  5. At the left side of the dialog box click Border Color.
  6. Select the No Line radio button.
  7. 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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Embedding TrueType Fonts by Default

If you use TrueType fonts frequently, you might want to set Word to embed those fonts by default. Here's how to do it.

Discover More

Transferring Fonts

Do you want to transfer fonts from one computer system to another? It is relatively easy to do, but there is one important ...

Discover More

Two Types of Page Numbers in a TOC

Word, when creating a table of contents, should automatically make sure that the page numbers it shows correspond to the way ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Moving a Chart's Legend

Need to move a chart legend to a different place on the chart? It's easy to do using the mouse, as described in this tip.

Discover More

Turning the Legend On and Off

When you create a chart in Excel, the program may automatically add a legend that explains the contents of the chart. In some ...

Discover More

Adjusting the Order of Items in a Chart Legend

When charting your data, a legend is always a nice finishing touch. You may want to change the order in which items appear in ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 + 4?

2015-11-15 19:11:45

Darren E

Rick K, linked picture would be OK as long as it is not a calculation-intensive workbook. Linked pictures can bring large workbooks to a grinding halt.
For this application I would just copy the chart, and in the copy:
1. Select plot area
2. Shrink it by dragging the bottom left sizing handle all the way to the top right.
3. Make the legend opaque and put it over the top of the plot area.


2015-11-12 09:02:24

Rick K

Another way is to use the camera tool and paste the linked picture to a separate sheet.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.