Change excel chart legend colours without affecting series

Question

I have the below bar chart in Excel that has two series representing RAG ratings. I would like to change the legend to display the two series icons in grey, with the second one in a hashed pattern, as the pattern is what differentiates the series on the chart.

Whenever I try to change the legend it updates the charts, breaking the colour formatting. Adding dummy series to produce replacement legend entries shifts all the bars to the left, even if there isn't any data in the dummy series.

Is there a way around this problem?

Answer 1

Since you want your legend to display independent of the data in the chart, the legend has to be disassociated from the chart data (but not the chart object).

In order to do this:

1. Delete the chart legend that is available as an option in Excel
2. With the chart selected, from the Format ribbon, use Insert Shapes and select a rectangle. Then draw the rectangle on the chart. This associates the shape with the chart object (but there's no association with the data itself).

3. Adjust the format of that rectangle shape to White Fill, Black Text, Font Size=9, No Outline.
4. Enter your legend text on two lines, as shown.

5. Now create two small squares and fill with gray, and set one of the squares with the pattern fill you desire.
6. Lastly, select both gray squares and the text box, then Group these three shapes (to make it easier to move around if necessary).

Now you can change colors or add data series or anything else, and your "legend" will remain unchanged.