For business users of Microsoft Excel Free guides and templates

Excel Charts

How to Create Dynamic Chart Legends in Excel

When you combine conditional formatting with charts you can create detailed chart legends that match the relative positions of the lines in your chart.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Two images of the same chart, where the legends have shifted with changes in the data.
These two charts show different versions of the same random data. Notice that as the order of the chart series changes, the legend adapts automatically.
When I saw a chart with a legend like this in the Wall Street Journal, I knew I had to add a similar chart to my Swipe Files…but in Excel, we need dynamic chart legends!

What’s A Dynamic Chart Legend?

Dynamic chart legends change as your data changes.

For example, this legend automatically adjusts so that the information about the top line also is at the top of the legend bar, and the information about the bottom line is at the bottom of the bar, and so on.

Also, this legend includes the current value for each data series. You can add any other data you want, of course.

Because this chart shows random data, the legends automatically adjust each time I recalculate my workbook.

The Key: Conditional Formatting

I can think of two ways to set up a legend like this.

One way would be to use a Camera object. If it weren’t for one problem, I probably would have used this approach. The problem is that using more than a few Camera objects slows calculation time considerably. Therefore, it’s always a good idea to limit your use of Camera objects to only when you really need them.

The other way is to use Conditional Formatting. This method calculates quickly and produces great results.

Getting Started

There’s nothing special about the data. It’s just a display that shows 13 months for five different categories. Here, of course, the categories are product lines:

The data for the charts with dynamic legends.

Row 4 shows the two range names I used. That is, I named the range of products Product, and the range of current values Current.

The chart is on a separate worksheet:

The chart with dynamic legends, with its controlling data.

To create the chart, let’s start with the control area.

Set Up the Legend Controls

I discuss the key values and formulas below. Unless I say otherwise, copy the last formula in a column down its column as needed.

L3:   1

Column L just contains the numbers 1 through 5.

M3:   =INDEX(Current,$L3)+ROW()*0.0001

Column M returns data for the current month from the data worksheet…with one addition. We’re going to use the RANK function to rank these results from 1 to 5. However, the RANK function returns duplicate results whenever it ranks duplicate values. To make sure that no two values are duplicates, we add a unique-but-inconsequential value to each value we rank. (Here, we’re adding the row number multiplied by .0001.)

N3:   =RANK(M3,$M$3:$M$7)

The formulas in column N rank the results found in column M.

O3:   1
O4:   =O3
O5:   =O3+1

Column O returns 1,1, 2, 2, and so on. Copy cell O5 down the column as needed to generate these values.

P3:   =MATCH(O3,$N$3:$N$7,0)

Formulas in column P automatically sort the results. Cell P3 returns the row number for the highest-ranked result. Cell P4 returns the row number for the second-ranked result, and so on.

Finally, you can set up the formulas that return the labels and values:

I3:   =INDEX(Product,$P3)
I4:   =INDEX(Current,$P4)

Copy this pair of formulas down the column as needed.

Now that the formulas and values are in place, we can set up our conditional formats.

Set Up Conditional Formats

Select the range H3 through H12. Then, with cell H3 active in New Excel (Excel 2007 or above), choose Home, Styles, Conditional Formatting, New Rule, Use a Formula to Determine Which Cells to Format. Enter the formula shown here:

The conditional formatting dialog.

Then choose the Format button and choose a theme color you want for the Series 1 line. Repeat this process four more times so that the five formulas look like this:

=$P3=1
=$P3=2
=$P3=3
=$P3=4
=$P3=5

For each value, choose a different theme color. After you do so, your spreadsheet should display ten cells with five fill colors, as shown in the chart figure above.

To visually divide each group of colored cells, assign a medium-thick white border around each group of two cells.

Complete Your Report

Using standard charting techniques, create the line chart. Select SERIES 1 and change the color of the line to the color you assigned to the first conditional format. Change the SERIES 2 line color to the second color and so on. Then format the chart area as you want.

Swipe File 34 has a completed version of the chart with dynamic chart legends and the custom color theme.

 

Free Excel Dashboards

Click to see testimonials from readers

Charley's SwipeFile charts

Click to see who uses Excel dashboards.