Highlight a data series in a line chart
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
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.
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
Highlight category
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
This article demonstrates how to highlight a group of bars in a chart bar, the techniques shown here works fine […]
This interactive chart allows you to select a country by press with left mouse button oning on a spin button. […]
I discovered this chart from Google Public policy blog and it got me thinking if I could do the same […]
Line chart category
The chart above contains no legend instead data labels are used to show what each line represents. Table of Contents […]
This article demonstrates how to create an animation using a line chart in Excel. The user selects a series in […]
The line chart lets you chart data points as a line, this chart type is useful if you have many […]
Functions in this article
More than 1300 Excel formulas
Excel categories
4 Responses to “Highlight a data series in a line chart”
Leave a Reply
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.
Great Tutorial, opened a great world of Tables to be used in formulas. Drew me in to studying Table References!
chrisham,
thanks!
hello! This is great info. I'm curious once the graph is generated can it be pasted into a Powerpoint slide and will it still be interactive as it is in excel? Will it be able to highlight different data points while keeping the rest greyed out?
Thanks.
Thanks Oscar - Great stuff!
I was getting a second series to plot at 0,0,0,0,0,0,0,0,0,0,0,0
When I used this formula:
=IF($J$2=Table2[[#Headers],[A]],Table1[@A],"")
I was able to remove the 2nd series by changing the formula:
=IF(Chart!$T$12=Table13[[#Headers],[A]],Table1[@A],NA())