highlight series in a line chart3

Here is how I did it:

Step 1 - Create a drop down list

  1. Select cell J2
  2. Go to tab "Data"
  3. Click "Data Validation" button
  4. Allow: List
  5. Source: B19:F19

Highlight a data series in a line chart

Step 2 - Copy table

  1. Select first table (table1: A19:F31)
  2. Copy (Ctrl + c)
  3. Paste to H19:M31
  4. Clear values

Highlight a data series in a line chart1

Step 3 - Formula in duplicated table

  1. Select the first empty value (cell I20) in the second table (table2)
  2. Click in 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 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

Step 4 - Insert a line chart

  1. Select table1
  2. Go to tab "Insert"
  3. Click "Line"
  4. Click "Line chart"

Highlight a data series in a line chart4

Step 5 - Color lines

  1. Select a data serie
  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

Repeat line 1 to 7 with remaining data series.

Highlight a data series in a line chart5

Step 6 - Add the duplicated table to the second 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
    Highlight a data series in a line chart7
  7. Click "Edit" button
  8. Select cell range H20:H31
  9. Click OK
  10. Click OK
  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

Download excel *.xlsx file

Highlight series in a line chart.xlsx

Functions in this post

IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE