Add a Time Bar to your chart

Line charts are a great way of showing the trend of information over time. I often use them to show both the historic actual, but also the forward forecast.

Sometimes, however, it’s not always clear looking at a chart where your ‘actual’ ends and your forecast begins.

I like to add an indicator showing me where we are now.

There are a few ways you could achieve this. One approach is to have your data separated and use different colours for your lines, but this can get messy if you have several series of data.

An alternative is to add a time-bar indicating the current date.

Excel doesn’t have a built-in method of doing this, but it’s fairly simple to achieve the effect. What we’re effectively doing is adding an additional series to the data, and then applying formatting to the chart.

The first step is to add an extra column to your dataset. If your line/data is static and won’t need to change dynamically, just enter 1 for the entry you want to show the line for, and 0 for all other entries.

If you want your line to ‘move’ over time, you’ll need to use a formula in this column to place the 1 / 0 in the relevant rows – such as the one below. The end effect should be similar to the above, but you’ll likely need to amend the formula to suit your dataset

Now, build your chart as normal, but include this extra field as a series. At first, your ‘CurDate’ series may not show up due to the scaling (the value 1 on a chart showing a max of 10,000 won’t register). If this is the case, temporarily override your ‘1’ value with a higher one, eg 10000.

The marker should then show clearly on your chart

Right-Click this line/series and select ‘Change Series Chart Type’

Select ‘Column à Clustered Column à OK‘

This should turn you’re your line into a single column

Right Click this column, and select ‘Format Data Series‘

From the ‘Series Options‘ menu, increase ‘Gap Width‘ to 500% and ‘Plot Series On‘ to ‘Secondary Axis‘

From the ‘Fill‘ menu, select ‘Gradient fill‘

From ‘Direction‘ select ‘Linear Left‘ (hover over each box to see the name)

Adjust your Gradient Stops so you have only 2 stops (to remove one, select it, and then click the red cross to the right).

If you want to change the colour from the default, select the first stop then select ‘Color‘ from below it and select a colour.

Position should be 0%, Brightness 0%, Transparency 0%

Now select your second stop. Change the colour to White.

Position should be 35%, Brightness 0%, Transparency 70%

By all means play around with these values to suit your preference – we’re basically trying to make the bar as thin as possible.

Once you’re done select ‘Close‘ and return to your chart – you should now have the line on it.

But, we’re not quite done.

On your chart, right click the right-hand (Secondary) axis and select ‘Format Axis‘.

From the ‘Axis Options‘ menu, change the ‘Maximum‘ value to ‘Fixed‘ with a value of 1.

Change the ‘Major tick mark type‘ to ‘None‘, the ‘Minor tick mark type‘ to ‘None‘ and ‘Axis Label‘ to ‘None‘

From the ‘Line Color’ menu, select ‘No Line’

Click ‘Close‘. You can now go back and restore your ‘1’ value / formula back (undoing where we over-rode it to 10000).

And that’s it done!

Hope you find that one useful

David