In Interactive Multiple Line Chart I showed how to make an interactive chart that allows the user to select from among a number of series using a listbox, and the selected series is displayed in a chart.

A similar technique is described in Interactive Parallel Coordinates Chart on my web site, and I used this technique in a workbook that supports my blog post Re: Abortion Ratios 1980-2003.

My colleague Dermot Balson read the post and wondered why I was doing it the hard way. Well, he’s right. I built a set of dynamic names that change based on the listbox selection, which is certainly a valid approach. However, for something as “simple” as plotting a column or row of data, it’s overkill. In this post I will describe an easier approach.

Data by Columns

The worksheet has data for multiple companies in columns, with each row representing the respective values on a given date. I made up some sine functions for the company data; a portion of the data is shown below.

For many situations a popular visualization approach is to plot one series in a contrasting color, and the rest of the data set is plotted in the background. I plotted this data on a single chart, and formatted all series to use a light gray line. This chart comprises the background.

We need to define a couple names to make this work. First, the column headers, the range of cells containing the Company names, is named “Companies”.

The listbox we will add requires a columnar (vertical) list, and the company names are in a horizontal list. I selected a range one column wide and 12 rows high (there are 12 companies in the table), typed this formula

=TRANSPOSE(Companies)
 

and held down CTRL+SHIFT while pressing ENTER to make it an array formula. When an array formula is correctly entered, Excel wraps it in curly braces:

{=TRANSPOSE(Companies)}
 

I named this range “CompanyList”, then selected another nearby cell (cell O2 in the shot below) and named it “SelectedItem”.

I added a listbox from the forms menu to the worksheet, and formatted it to use CompanyList as the input range and SelectedItem as the cell link. This means the list in CompanyList is displayed in the listbox, and the index of the selected item is displayed in Selecteditem.

The listbox looks like this:

A column adjacent to the data range is used to hold the data for the selected company. Here is a pilot’s eye view of the worksheet (for a better view, download the workbook from the link at the bottom of the page).

The original data is in A23:M79, and the added data, highlighted in blue, is in N23:N79. Cell N23 has this formula:

=OFFSET(A23,0,SelectedItem)
 

and the formula is filled down to N7.

Now the selected series can be added to the chart. Copy the blue range, select the chart, and use Edit menu > Paste Special to add the data to the chart as a new series. Format to suit.

To create a chart without the background series (like the one shown at the top of this post), simply make the chart using column N for the Y values and column A for the X values.

Here is the chart, showing all series in the background gray and the selected Company A highlighted in blue.

Clicking another item in the list changes the highlighted series, to Company L below.

By Row

For this example I made up data for multiple companies in rows, with each column representing the respective values on a given date; a portion of the data is shown below.

I plotted this data on a single chart, and formatted all series to use a light gray line. This chart comprises the background.

We need to define a couple names to make this work. First, the row headers, the range of cells containing the Company names, is named “TheCompanies”. I selected another nearby cell and named it “TheSelection”. (The names are different than in the first example, because both examples are in the same workbook, and I wanted to avoid naming conflicts.)

I added a listbox from the forms menu to the worksheet, and formatted it to use TheCompanies as the input range and TheSelection as the cell link. This means the list in TheCompanies is displayed in the listbox, and the index of the selected item is displayed in TheSelection.

The listbox looks like this:

A row adjacent to the data range is used to hold the data for the selected company. Here is a pilot’s eye view of the worksheet (for a better view, download the workbook from the link at the bottom of the page).

The original data is in A23:M35, and the added data, highlighted in orange, is in A36:M36. Cell A36 has this formula:

=OFFSET(A23,TheSelection,0)
 

and the formula is filled right to M36.

Now the selected series can be added to the chart. Copy the orange range, select the chart, and use Edit menu > Paste Special to add the data to the chart as a new series. Format to suit.

To create a chart without the background series (like the one shown at the top of this post), simply make the chart using row 36 for the Y values and row 23 for the X values.

Here is the chart, showing all series in the background gray and the selected Company A highlighted in orange.

Clicking another item in the list changes the highlighted series, to Company L below.

Sample Workbook

Download a workbook that contains these two examples: EasierInteractiveLines.zip

Related Posts:

• Interactive Multiple Line Chart
• Gas Prices – Interactive Time Series
• Sample Parallel Coordinate Chart
• Display One Chart Dynamically and Interactively
• Dynamic Chart with Multiple Series
• Interactive Charts with Checkboxes and Formulas
• Chart Event to Highlight a Series
• Physics Lesson
• Accordion Chart for Jorge
• Chart Event Class Module to Highlight a Series