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

Written by Allen Wyatt (last updated February 29, 2024)
This tip applies to Excel 2007, 2010, and 2013


5

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

Easily Running a Program as the Administrator

In order to run some programs properly in the Windows environment, you'll need to do so using administrator privileges. ...

Discover More

What Changes Did I Make In that Template?

When you make changes that affect a template, Word usually asks you if you want to save those changes when you exit the ...

Discover More

Hiding Macros

Don't want a particular macro to be visible in the Macros dialog box? Once you understand the criteria that Word uses ...

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)

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

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 ...

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 ...

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}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. 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 five more than 3?

2021-10-07 10:50:09

Ronmio

I should have also mentioned that you can use the Windows Snipping Tool to capture and save the legend for pasting anywhere.
(see Figure 1 below)

Figure 1. 


2021-10-06 10:08:50

d

I don't know about the other people, for me I wanted to copy the legend with the icons on them. The screenshot works better but I have to work with a white background of it. However, the last method is not really useful since when you put every bar in a no-filled mode then you automatically get a nothing legend, with only words. I wanted to put only one legend for different graphs but I want a better place for it, not only inside one single chart.


2020-07-20 11:48:26

Ronmio

There is a much easier way that can be done entirely in Excel in two steps.

Select the chart and use the Copy dropdown on the Home menu to select "Copy as Picture ..." and click on OK to select the defaults. Then paste the picture of the chart where you want it and select Crop on the Format tab* of the Picture Tools to trim away all but the legend. Done.

*That's where Crop is in Excel 2013. In Excel 2019 or Excel 365, the Crop tool will be found under the Picture Format menu.


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.