Excel: creating a movable hairline date marker on a time series chart

If you want to add a marker line to a one-off chart it is probably easiest to use Excel's drawing tools (Shapes).

This article looks at a more complex situation. We want the position of the marker line to move automatically based on selections in the dashboard.

For example we may use a line chart to show a time series. A hairline marker could then be used to highlight a date of interest. The position of the marker should then move in line with currently selected values or changes to the data.

This can be set up in Excel relatively easily and without having to resort to any VBA code. The method shown here creates the hairline from a column data series aligned to a secondary axis. I devised this method myself - rather than borrowing it from any books or websites - so this may be the first time it has been described anywhere. Although given the ingenuity of Excel users,  I quite expect to find that others will also have invented it, or something like it

The illustration is a generalized chunk of a dashboard. The idea is that the user selects a week of interest using a drop down list. The data displayed is automatically updated to match the chosen week. The position of the hairline marker on the line chart also  moves to indicate the position of the chosen week, And the date displayed above the hairline is also updated to give the correct detail

I am not going to explain here how to update the table of values in line with the selected week. You will be able to do this in various ways including  using VLOOKUP() and/or INDEX()/MATCH(). The details will have to be tailored to your individual situation, in particular where you are getting your data from and how you plan to refresh it.

Neither am I going to explain how to create a line chart with a 'shadow band' effect covering a range of older data - I have covered this is an earlier article
http://bi-dashboard-design.blogspot.com/2018/05/the-problem-of-too-many-lines-on-time.html

The focus here will be on how to create the movable hairline. The main features are:

• The hairline is simply a standard Excel column data series with the properties set to make it very narrow
• The chosen week is displayed by being the only week that is not hidden.
• Hide all the other weeks by making their value calculate to #N/A when they are not chosen
• Use the secondary vertical axis to ensure that the height of the hairline remains the same whatever happens to the values in the line series
• Set the hailrlne data label by pointing it at a cell with the date in

None of this is difficult


Alternatives?

Another method from elsewhere is to use a scatterplot with an error bar