Replace default Excel chart legend with meaningful and dynamic messages

With just a bit of work, you can replace Excel's default chart legends with messages that are meaningful and dynamic.

Excel does an adequate job of creating a chart legend that identifies each series. If you find it rather bland, you can replace that default legend with one that identifies the series and important data. It's an easy way to make a point!

Let's consider the default graph shown below. It compares monthly commission earnings for four employees during 2011. The default legend identifies each employee. There are many ways to improve this chart, but we'll concentrate on enhancing the legend. In this case, we'll delete the existing legend and add a message for each employee via a text box.

What's the message

The first step, of course, is to decide on your message. What do you want to convey? The possibilities are endless but keep the message simple and to the point. For instance, in this case, we'll use the text box legends to compare 2011 earnings to 2010. Doing so will require a bit of setup in the data range, and that's why I recommend keeping things simple.

Our sheet contains totals for 2011 and 2010, so comparing the yearly earnings is easy. Row 15 contains SUM() functions; row 16 contains literal values (just to simplify the example). A few simple formulas return the actual data that we'll display in the text box legends:

C17: =ABS(C16/C15-1)

C18: =IF(C$16>C$15, " is down ", " is up ")

C19: =C$2 & C$18 & TEXT(C$17,"0%")

(Don't forget to copy the formulas to D17:F19.)

The formula in C17 returns a percentage that reflects the difference between the two years. (Be sure to format that cell appropriately.) C18 returns the string " is down " or " is up ", accordingly. Lastly, the formula in C19, concatenates the result of these formulas with the series title, which in this case, is each employee's last name. Now, you're ready to add the text box legends to the actual chart, as follows:

  1. Return to the chart and delete the default legend by selecting it and pressing [Del]. The chart will expand to fill in the area.
  2. Click the Insert tab and insert a text box control.
  3. Click inside the Formula bar and enter the cell reference for the first series, Smith. In this case, that's =2011Commission!C19. Press Enter.
  4. Adjust the size and format of the text box. Be sure to match the right series color when coloring the text box.
  5. Repeat steps 1 through 4 for each series, making sure each legend matches its corresponding series color.

There are many ways to simplify this technique. I chose to use individual formulas to return each message component so you could easily follow the process. You can lump everything into one concatenating formula. You're free to position and format the text box legends in any way you like.

The result is a message that's meaningful and dynamic. If you update values in the data range, the messages in the text box legends will update accordingly.