Author: Oscar Cronquist Article last updated on March 05, 2020

Highlight a data series in a line chart 2

This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list.

The method used here is to have the thinner lines on the primary axis and the highlighted lines on the secondary axis.

A formula then hides all series values except the chart series that is selected in the drop-down list. This is possible without using VBA och UDFs.

Instructions

What you will learn in this article:

  • How to create a drop-down list and populate it using a cell range as a source.
  • Build two Excel Tables and populating one of them with a formula that hides values except for the record that corresponds to the value in the drop-down list.
  • Create a line chart
    • Populate the chart with values from both Excel Tables.
    • Use the first Excel Table on the primary axis.
    • Use the second Excel Table on the secondary axis.
    • Customize chart lines colors.
    • Remove the horizontal and vertical axis from the secondary axis.

Step 1 - Convert data to an Excel Table

Highlight a data series in a line chart create excel table

One of the great things about Excel Tables is that they are easy to reference. If the table grows or shrinks the reference stays the same which makes them really easy to work with.

No need to adjust references like regular cell references in formulas. The correct term is structured references and they work somewhat differently than regular cell references. I will tell you more about it when I explain the formulas below.

  1. Select any cell in the data set.
  2. Press CTRL + T to open the "Create Table" dialog box.
  3. Click checkbox "My Table has headers" if your data set has header names.
  4. Click OK button.

Highlight a data series in a line chart create excel table2

The data set is now converted to an Excel Table, Excel has automatically changed the cell formatting and given it a name.

Click on one of the cells in the Excel Table, go to tab "Table Design" on the ribbon. You can find the Table name in the very top left corner of your Excel screen.

Step 2 - Create a drop down list

A drop-down list is a list that extends when you click on the arrow next to the cell. You must select the cell to see the arrow, see image above.

It is a good idea to format the cell or add a value to the adjacent cell so the user understands that you have to choose a value.

  1. Select cell J2.
  2. Go to tab "Data" on the ribbon.
  3. Click the "Data Validation" button.
  4. Choose List in the drop down list near "Allow:", see image below.
  5. Select cell range B19F19 as source.
  6. Click OK button.

Highlight a data series in a line chart

Step 3 - Copy table

The first table values will populate the primary axis and the second table will populate the secondary axis. The primary axis contains the greyed out lines and the secondary axis contains the highlighted line (chart series).

  1. Select the entire first Excel table named table1 cell range A19:F31.
  2. Copy cells (Ctrl + c).
  3. Paste to cell range H19:M31 (CTRL + v).
  4. Clear the numbers in the second table, it will contain a formula that populates the table based on the selected value . I will build that formula in the next step.

Highlight a data series in a line chart1

Step 3 - Formula in the second Excel Table

This formula will return values if the selected drop-down list value is equal to the column Header and blanks if not.

  1. Select the first empty value (cell I20) in the second table (table2).
  2. Click in the formula bar.
  3. Use the following formula:
    =IF($J$2=Table2[[#Headers],[A]],Table1[@A],"")
  4. Press Enter.
  5. Select cell I20.
  6. Click and drag the black dot to the right, all the way to table column E.
    Highlight a data series in a line chart3
  7. Select cell range J20:M20.
  8. Double click black dot. The formulas are copied to the remaining cells below in the table.

Highlight a data series in a line chart2

Explaining formula

IF($J$2=Table2[[#Headers],[A]],Table1[@A],"")

Cell J2 contains the selected drop-down list value. We use an absolute cell reference meaning it will not change when we copy the formula across the table, the cell reference becomes $J$2.

Table2[[#Headers],[A]] is a structured reference pointing to column header name A in Excel Table named Table2.

$J$2=Table2[[#Headers],[A]]

becomes

"E"="A"

and returns FALSE

The IF function returns the corresponding value from Excel Table Table1, note that the reference changes when you copy the cell to the remaining cells in Table2.

IF($J$2=Table2[[#Headers],[A]],Table1[@A],"")

becomes

IF(FALSE ,[A]],Table1[@A],"")

becomes

IF(FALSE, 1, "")

and returns "" in cell I20.

Step 4 - Insert a line chart

The Line Chart is great for graphing data series, they are easy to create and modify. Use the scatter chart if you want to plot data that contains numbers in both the x and y-axis.

  1. Select Excel Table table1.
  2. Go to tab "Insert" on the ribbon.
  3. Click "Line".
  4. Click "Line chart".

Highlight a data series in a line chart4

A line chart appears populated with lines based on the data in Table1, see image above. Click and hold with left mouse button on the chart then drag to change the location.

Click and hold SHIFT key simultaneously as you drag to move it horizontally or vertically. Click and hold the Alt key while you drag to align the chart border to the cell grid beneath.

To select the chart simply click on it with the left mouse button, size handles appear. Click and hold with the left mouse button and then drag to resize the chart.

Step 5 - Color lines

This step will make the lines more subtle to make the selected line stand out a little more.

  1. Select a data series.
  2. Right click on the selected data serie.
  3. Click "Format Data Series...".
  4. Select "Line Color".
  5. Select "Solid line".
  6. Pick a light gray color.
  7. Click Close button.

Repeat line 1 to 7 with remaining data series.

Highlight a data series in a line chart5

Step 6 - Add the second Excel Table to the second axis

This step will plot the highlighted line on the secondary axis.

  1. Right click on chart.
  2. Click "Select Data...".
    Highlight a data series in a line chart6
  3. Click "Add" button.
  4. Series name: I19
  5. Series values: I20:I31
  6. Click OK button.
    Highlight a data series in a line chart7
  7. Click the "Edit" button.
  8. Select cell range H20:H31.
  9. Click OK button.
  10. Click OK button.
  11. Select item A in the drop down list.
  12. Right-click on data series A on the chart.
  13. Click "Format Data series...".
  14. Select secondary axis.
  15. Click OK.

Repeat above steps 1 - 15 with table column B, C, D and E.

Remove secondary y axis, see picture below.

Highlight a data series in a line chart8

Step 7 - Remove series in first axis from legend

Remove A to E colored gray from the legend. (Click on each character and press Delete)

Highlight a data series in a line chart10

Delete major gridlines

Highlight a data series in a line chart11

Animated image

highlight series in a line chart3