Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals brought to you by CPA Australia.
Neale Blackwood CPA:
About timeline charts in Excel 0:17
Welcome to the Excel Tips podcast. My name is Neale Blackwood, and in this episode, I wanted to talk about timeline charts in Excel. Now, Excel doesn't have a built-in timeline chart, but we can take a scatter chart and hack it a little bit to create a timeline chart. So what is a timeline chart? Well, a timeline chart shows you events or things over a timeline period. So the horizontal axis on the chart has dates on there.
All of the data points have a vertical line down to the horizontal axis, so you can see where they land on the timeline. Now, timeline charts can be good for looking at historical things, so you can have a historical timeline to see where things happened in the past, or you can use it as a planning tool so that you can see where things are going to happen in the future. And you might need to change things based on where you see the lines landing. Some lines might be too close to each other, so you might want to change an event and change the date of it.
Hacking the scatter chart to make a timeline chart 1:33
As I mentioned, Excel doesn't have a built-in timeline chart, but the scatter chart can be hacked so that you can create those vertical lines from each of the data points down to the horizontal axis. There's also a new feature that's been added to Excel that you can use in this technique. So it's a new feature that is part of Excel's charts where you can link data labels to a range in Excel, so you can have all of your detailed labels listed in that range, and then they will appear as data labels on the chart.
I highly recommend you check out the companion video for the podcast and the article. It goes through all of the steps to convert the scatter chart into the timeline chart. Some of the steps are a little bit fiddly, so seeing how they work in a video is quite useful. There's also a companion file that you can download from the INTHEBLACK website where the article is featured.
Formatting dates using the range 2:43
So one of the things that you strike when you build this timeline is that the formatting of the dates affects how that appears on the horizontal axis. Now, to be honest, when you format the axis, it's actually hard to get the correct format for the dates that you might want. So in general, it's better to format the range on the Excel sheet than the axis on the chart. If you change the format on the Excel sheet, that will actually flow through to the chart, and so changing the format on the sheet is much easier than trying to change it through the formatting task pane for the chart.
Universal formatting shortcut 3:32
Now, there is a universal formatting shortcut key in Excel, so this will format anything that you've got selected. It's Control, so hold the Control key and press the number 1. Now, you need to press the number 1 on the keyboard. So that's the one on the left-hand side of the keyboard, not the one on the keypad. So the numerical keypad, which is on the right-hand side of a full-sized keyboard.
Don't press that one because that doesn't work. You need to press the one that is above the Tab key on the normal keyboard. So whatever you've got selected, when you press Control + 1, that will open up the formatting dialogue for that thing. That's a useful shortcut to use.
Working with the data series and points 4:27
Something to pick up with charts as well, when you're working with a data series in Excel, when you click on the series once, you are selecting the whole series. If you click one of the data points again, so it's like a double click, you actually select just that data point.
Now, when you're working with charts, that can be a problem. So just be very, very careful when you are clicking on charts, just to click things once unless you are trying to select a particular data point on a chart. Just be very careful when you are clicking on the chart. Some of the things can be fiddly to click as well because one of the things that we hack on the scatter chart is something called error bars.
And there's a horizontal error bar and there's a vertical error bar. And it can be a bit fiddly to click each of those because we use the vertical error bar to link the data point down to the axis, but the horizontal error bar is not used, so we actually delete those.
The structure of the data to build the chart 5:40
Now, the structure that I've used to build the chart has three columns in a table. So I've got a date, which is the date of the event. I've got a value, which is a number. I'll explain that in a minute. And then I've got a description. Okay, the value. What the value does is that positions the label at a height. So I use the numbers from eight down to two.
And so basically, on the timeline chart, the highest point was the first event. And then as the time went by, the events got closer and closer to the horizontal date line, if you like. And so that's what the number does. The value will position the height of a particular event for the data label.
Now, depending on how many labels you want to apply or how many dates you want to display, you may need to go above and below the axis for the date.
Now, that works in this technique because you can use a positive value to go above the line and a negative value to go below the line. You could even use the positioning to mean something. So as an example, you could have the actual value above the line and maybe the budget value below the line. So you might be able to show when something actually happened compared to the budget for that thing, and you can show them above and below the lines.
Linking the title to the cell 7:21
The example video also shows you how you can link the title of a chart to a cell, which is as simple as selecting that chart title, clicking in the formula bar, typing equals, and then just clicking on the cell that you want to link to. So that's a fairly common technique.
The date format used by Excel and keyboard shortcuts 8:07
One of the issues that you might find working with dates in Excel, and this happens when you look at the axis formatting for the timeline chart. There's a minimum and maximum values there. So they're like a number 46,000 or something. And that relates to the dates. Now, I think I have probably talked about this before in podcast, but in Excel, a date is a number. There's an underlying number for every single date in Excel. So day one was the first of the first 1900.
Every day since then has been given a sequential number.
Now, that's the type of number that will appear in that formatting for the axis. If you ever want to find out what that number is, all you do is you type the number in a cell and then just format it as a date, and that will show you what the date looks like. The opposite way, if you have a date and you want to know what the number or the underlying number is, then you can use a keyboard shortcut.
So type the date into Excel and Excel will automatically format dates when it recognises a date. To get rid of the format, just have that cell selected and press in sequence Alt + H + E + F. So don't hold the keys down. So Alt + H + E + F. And that removes all of the formatting, which includes the date formatting. So if you want to see what the underlying number for a date is, that's a quick way to do it. In the video, I also shared a technique to insert today's date, which is hold the Control key and press the semicolon, and that will insert today's date.
Timeline charts overview and recap 9:29
So timeline charts are quite useful for either historical events or for future events. You can use them as a planning device. Have a look at timeline charts and check out the video because as I said, there's lots of useful techniques in there. Okay, I hope you found this useful. Thanks for listening.
Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode. And we hope you can join us next time for another episode of Excel Tips.
