For business users of Microsoft Excel Free guides and templates

Windows & Mac Excel

Charley's Swipe File #51

This workbook relies on a two-line macro and a dropdown list box to let you choose from five designs for your chart legends.

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

Charley's Swipe File #51This Swipe File uses the most sophisticated legend I’ve offered so far.

First, like Swipe File #34, the legend automatically sorts in the sequence of the most-current value in the chart. In the top chart, for example, the red line is at the top; and in the bottom chart, the red line is at the bottom.

To make these changes easier to see, the Excel database contains random data, which you can, of course, replace with your own data. And the support sheets for the legends use INDEX-MATCH formulas to return data from the database.

Additionally, five legend formats are available, which you can choose using a dropdown list box to the right of the chart. The list box is supported by Excel’s Data Validation List feature and another INDEX-MATCH formula. Three of the options are shown here.

Although VBA isn’t needed to change legends (we use dynamic range names instead), this file does include a two-line macro to help you format the legends.

Specifically, the five legend choices use a different number of columns of different widths. But in total, the legends need to fit within the same space in the report. So I use a User Defined Function (UDF) to return the width of each legend area. This allows us to manually change the columns to make legends the same width.

The Dynamic Camera Tool

The chart uses Excel’s little-known Camera tool linked to a dynamic range name to support the ability to change legends. Here’s how it works: If you select the chart legend, you’ll see in the formula bar that it references the LegendChoice range name. That name is defined as:

=CHOOSE(LegendNum, Legend1!s.Legend, Legend2!s.Legend, Legend3!s.Legend, Legend4!s.Legend, Legend5!s.Legend)

The LegendNum range name (which is in the Control sheet) returns the numbers one through five, depending on the legend option you choose. So if you choose Legend2, for example, the value of LegendNum becomes 2 and the LegendChoice range name returns a reference to the second item in its list, the s.Legend range in the Legend2 worksheet.

(The “s.” in front of the name merely tells us that the name refers to a range in one of the Legend sheets, where “s” stands for “support”. At first, I used “l” for “legend”. But it looked too much like the number 1, which was confusing.)

The Automatic Sorting

Each Legend sheet automatically sorts its data. It does this by...

(Continued in the documentation.)

Order Details

Excel Versions:

Excel 2007 and after, for either Windows or Mac


Instant download of zipped Excel and PDF files. If you ever need another download, you can get it at any time.


One full year, unconditional.


All prices are in US Dollar currency.


Two copies: One for work plus one for home.

Credit Cards:

Credit Cards

Only $14.95

New Excel