multiple legends in excel chart

Question

Is there a way in Excel to have 2 legends for a single chart? I have an excel chart with many series. Let's take the following structure.

X   AY1 AY2 BY1 BY2 CY1 CY2
1   2   3   4   5   6   7
3   11  2   5   23  45  65

X column represents the horizontal scale the rest of the columns represent the vertical scales. A, B and C are something like a category, and Y1 and Y2 are series for each category. Now, Y1 and Y2 have the same line style, no matter what category, but the category gives the color. So in this example Y1 is dashed and Y2 is dotted. A is blue, B is red and C is green. This leads to:

AY1 - blue dashed, AY2 - blue dotted, 
BY1 - red dashed,  BY2 - red dotted,
CY1 - green dashed,CY2 - green dotted.

Currently my legend is AY1, AY2 and so on, with the dash style and color. Because I can have more than 10 categories with more than 2 series for each of them, I will end up with a legend of no. of categories * no. of series, which is not necessary. Instead I would like a legend like: Y1 - dashed, Y2 - dotted(each series line type only once) and then a legend with the color for each category( A- red, B - blue, C - green).

Is this possible in Excel?

Thanks!

Can you post a sample file with data and chart and then mock up what you want to achieve? It's not really clear. — teylyn

teylyn teylyn · Accepted Answer · 2015-10-20T09:54:14

Excel only does one legend per chart. Each series will have an entry in the legend. You can manually select a legend entry and delete it.

You could manually construct a text box or similar, and place that on the chart to achieve the effect you describe.

So, if I understand correctly, I'd add two dummy series to the chart with 0 values and assign them dashed and dotted respectively, with a neutral gray color. Then delete every series but the gray ones from the legend.

Then create a drawing with the colors for the real data series and place it on top of the chart as a secondary legend.

Edit: thinking about this a bit more, here is another approach. Use dummy series purely for the purpose of showing in the legend. Consider this screenshot:

Your original data table has been charted and the series have been formatted as to your instructions above.

Then another set of data was added to the chart as new series.

Y1 and Y2 have been formatted with gray and dashed / dotted respectively A, B and C have been added with their respective colors.

The next step is to click, select and delete the legend entries for AY1, AY2 etc., and keep only the legend entries for the helper series. Finally, edit the source data for the helper series and set all data to 0 or 1, so nothing gets plotted in the chart. The result looks like this:

You could even change the chart type for the colored helper series to a dot, so the legend will show a dot instead of a line:

multiple legends in excel chart

1 Answers