Highlight a data series in a line chart

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.

Select any cell in the data set.
Press CTRL + T to open the "Create Table" dialog box.
Press with left mouse button on checkbox "My Table has headers" if your data set has header names.
Press with left mouse button on 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.

Press with mouse 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 press with left mouse button 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.

Select cell J2.
Go to tab "Data" on the ribbon.
Press with left mouse button on the "Data Validation" button.
Choose List in the drop down list near "Allow:", see image below.
Select cell range B19F19 as source.
Press with left mouse button on OK button.

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).

Select the entire first Excel table named table1 cell range A19:F31.
Copy cells (Ctrl + c).
Paste to cell range H19:M31 (CTRL + v).
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.

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.

Select the first empty value (cell I20) in the second table (table2).
Press with left mouse button on in the formula bar.
Use the following formula:
=IF($J$2=Table2[[#Headers],[A]],Table1[@A],"")
Press Enter.
Select cell I20.
Press with left mouse button on and drag the black dot to the right, all the way to table column E.
Select cell range J20:M20.
Double press with left mouse button on black dot. The formulas are copied to the remaining cells below in the table.

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.

Select Excel Table table1.
Go to tab "Insert" on the ribbon.
Press with left mouse button on "Line".
Press with left mouse button on "Line chart".

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

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

To select the chart simply press with left mouse button on it with the left mouse button, size handles appear. Press 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.

Select a data series.
Press with right mouse button on on the selected data serie.
Press with left mouse button on "Format Data Series...".
Select "Line Color".
Select "Solid line".
Pick a light gray color.
Press with left mouse button on Close button.

Repeat line 1 to 7 with remaining data series.

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

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

Press with right mouse button on on chart.
Press with left mouse button on "Select Data...".
Press with left mouse button on "Add" button.
Series name: I19
Series values: I20:I31
Press with left mouse button on OK button.
Press with left mouse button on the "Edit" button.
Select cell range H20:H31.
Press with left mouse button on OK button.
Press with left mouse button on OK button.
Select item A in the drop down list.
Press with right mouse button on on data series A on the chart.
Press with left mouse button on "Format Data series...".
Select secondary axis.
Press with left mouse button on OK.

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

Remove secondary y axis, see picture below.

Step 7 - Remove series in first axis from legend

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

Delete major gridlines

Animated image

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form