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
Built-in Charts
Combo Charts
Combined stacked area and a clustered column chartCombined chart – Column and Line on secondary axis
Combined Column and Line chart
Chart elements
Chart basics
How to create a dynamic chartRearrange data source in order to create a dynamic chart
Use slicers to quickly filter chart data
Four ways to resize a chart
How to align chart with cell grid
Group chart categories
How to add lines between stacked columns/bars [Excel charts]
Custom charts
How to build an arrow chartHow to graph a Normal Distribution
How to graph an equation
Build a comparison table/chart
Heat map yearly calendar
Advanced Gantt Chart Template
Sparklines
Win/Loss Column LineHighlight chart elements
Highlight a column in a stacked column chartHighlight a group of chart bars
Highlight a data series in a line chart
Highlight a column in a stacked column chart
Highlight a bar in a chart
Interactive charts
How to filter chart dataHover with mouse cursor to change stock in a candlestick chart
How to build an interactive map in Excel
Highlight group of values in an x y scatter chart programmatically
Use drop down lists and named ranges to filter chart values
How to use mouse hover on a worksheet [VBA]
How to create an interactive Excel chart [VBA]
Change chart series by clicking on data [VBA]
Change chart data range using a Drop Down List [VBA]
How to create a dynamic chart
Animate
Line chart Excel Bar Chart Excel chartAdvanced charts
Custom data labels in a chartImprove your X Y Scatter Chart with custom data labels
Label line chart series
How to position month and year between chart tick marks
How to add horizontal line to chart
Add pictures to a chart axis
How to color chart bars based on their values
Excel chart problem: Hard to read series values
Build a stock chart with two series
Change chart axis range programmatically
Change column/bar color in charts
Hide specific columns programmatically
Dynamic stock chart
How to replace columns with pictures in a column chart
Color chart columns based on cell color
Heat map using pictures
Dynamic Gantt charts
Stock charts
Build a stock chart with two seriesDynamic stock chart
Change chart axis range programmatically
How to create a stock chart
Excel categories
One Response to “How to create a stock chart”
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.
[…] Learn how to create a stock chart in excel […]