This article walks you through simple steps to create a dynamic, interactive line chart that can highlight only one chosen series at a time. (A sample screenshot attached)
Let’s get started!
Check our YouTube video on this here:
Step 01:
To build this chart, consider sample sales data across months for a year for four different regions.
Select all the data, and insert a 2D line chart:
This creates a 2D line chart with multiple lines like this:
Step 02:
To highlight a user-chosen series, we need to add another series (one that’s chosen) and a drop-down for the input.
Let the additional series be called Highlighted. In an empty cell (say cell I2), include a drop-down as shown:
Choose a list and the source will be the column headers in our data:
Highlight the input cell to differentiate easily and the created drop-down looks like this:
In our highlighted column, based on the input from the drop-down, the values from the corresponding series have to be updated.
This can be easily achieved by using a simple SWITCH function.
=SWITCH($I$2,”North”,C5,”East”,D5,”West”,E5,”South”,F5)
This creates our additional series:
Drag the formula to all the required cells to populate the same.
Step 03:
Let’s format our existing chart before adding the additional series created in the previous step.
Open the format pane and do the following for each of the series:
To make the lines lighter in comparison to the highlighted series that we’ll include in the next step, format the four series with a lighter color and lesser width as shown:
Apply the same steps for all four series, with this our chart looks like this:
Step 04:
It’s time to include our highlighted series. (1) Right-click on the chart, (2) go to Select data and add the series and the horizontal axis as shown below:
With this, our chart with the additional highlighted series looks like this:
Step 05:
Format this new series to achieve the desired chart. Since this is a highlight, let’s modify the color accordingly.
(To open the format pane, right-click on the series and click Format Data Series)
To differentiate this further, add markers as shown below:
With this, our interactive line chart with highlights looks as shown below:
With a change in the drop-down, the chart highlights the chosen series only.
Step 06:
Lastly, let’s move the legend to the top and make it dynamic, to do this let’s understand that the value “Highlighted” is chosen from the headers of our data. Modifying this to point to the drop-down will ensure that the legend is also updated automatically.
So in cell G4, instead of “Highlighted”, include
=I2 (to point to drop-down).
With this, our chart is almost ready:
Since the focus is only on the highlighted line, remove all other legends by clicking on them individually and deleting them. With this, we have our interactive line chart with one highlighted line ready for analysis and as a great addition to your dashboards.
Do check our blog on creating multiple series line chart with detailed explanations on various chart formatting options.
If you have any feedback or suggestions, please post them in the comments below.
