Microsoft

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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

3 comments
ppg
ppg

I don't wish to sound negative but there are a number of limitations with this approach. 1) The contents of a text box do not update if you add or remove lines. Therefore if you add more months you will have to update the references. 2) Susan is using the position of the boxes to indicate whether the sales are up or down. However the position of the boxes is "hard-coded" so if the data changes you have to reposition them. It may be just as easy to just write your comments into the text boxes. If you use the references as the values in a legend the graph won't be quite as dramatic but it will continue to be correct if you add or remove rows..

ViCentelles
ViCentelles

Very good tip, Susan. I tried to follow your instracction with my always a bit extrange Mac and 1) I could not find how to create a Text box 2) Inside a form, I could not create the cell reference (steps 2 and 3) Could you help?

ssharkins
ssharkins

I don't run a Mac at all, so I'm unable to check the actual steps. Maybe a Mac user will check in and take pity on us! ;)

Editor's Picks