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

Sequential Page Numbers Across Worksheets

How do you want your page numbers to appear on your printed worksheets? Chances are good that you want them to be sequential, ...

Discover More

Getting Rid of Section Breaks, but Not Section Formatting

Word allows you to change the character of how your pages are designed by using multiple sections in a document. If you want ...

Discover More

Displaying the File Tab of the Ribbon by Default

When you first start Word, it displays the Home tab of the ribbon. If you want to display a different ribbon tab by default, ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Formatting the Border of a Legend

When you create a chart, Excel often includes a legend with the chart. You can format several attributes of the legend's ...

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

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
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. Maximum image size is 8Mpixels. 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 6 - 3?

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.