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, 20052014
The Father of Spreadsheet Dashboard Reports


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:
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:
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
uniquebutinconsequential 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 highestranked result. Cell P4
returns the row number for the secondranked 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:
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
mediumthick 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.
