Transcription
I've seen people spend 2 hours in PowerPoint making timelines when Excel can do it in 10 minutes. Yeah, this in 10 minutes. Plus, it's dynamic. Make adjustments. It's going to pull through. You just need to set it up once, and you've got your template to reuse.
Actually, you don't even need to set it up because you can download the file with a link below. But I do recommend that you watch the video because you might learn a trick or two.
Over here in Excel, I have a column for Date and another for Milestone. Now, notice this is formatted as an Excel Table. We can see that tiny icon here. If I go to Table Design, the table is called Table Time. This is good practice so that if we add new rows, it's going to pull over to our timeline chart.
Now, Excel doesn't have an inbuilt timeline chart, so we're going to take one of its existing charts and tweak it to look like a timeline. But here's the thing: any chart in Excel is going to require numbers. Data is going to be my x-axis. I need some numbers. I'll add a new column called Level. This is going to be my helper column. Just as a starting point, I'm going to fix these to any number. I'll just put 10. We're going to be tweaking this later on, but let's just start off simple.
So now, I'm going to select this column, hold down Ctrl, select the Level column, go to Insert, click on the Line Chart here on this dropdown, and select the one with markers — so Line with Markers.
Now, let's go and click on this plus sign. Place a check mark for Error Bars. Now go to More Options here. For Vertical Error Bar, select Minus, No Cap. And for the percentage, we're going to go with 100%. And we get this effect. So, Error Bars are in Excel so that you can show the range of error associated with each data point. In this example, we're going to be using it to get this effect where we get the connection between the X-axis and our markers.
Now, let's go to Color Options. Adjust the color of the lines. I'm going to go with a slightly lighter color and make them slightly thicker. Now, let's select our line series. Go to Marker, Marker Options. Adjust the marker size if you want. I'm going to go with a slightly bigger size. You can also adjust the marker type as well and the marker color. So, this blue is not bad. Maybe I'll just go with a bit of a darker blue. Now, let's go to Line and select No Line because we don't need to see that line.
So, notice the Level 10 here is basically the distance of each point from the axis. This is not going to work the way it looks, right? Because if I add these descriptions here, they're going to be overlapping, and this is going to be impossible to read. What we want to do is to have some of these positive, some of these negative. We want to have them at different heights so that the text is going to be easier to read.
Now, of course, you can add these manually, right? I could put -30 here and have this go down and then have this as -40, and so on. But you could also use a formula that does this consistently, right? A formula like RANDBETWEEN is going to be random. You want something that's consistently consistent. For that, I'm going to use this function combination. So, I'm going to start off with CHOOSE. Then, inside it, I'm going to use the MOD function. Use ROW. This is going to give me the current row. So, in this case, this would give me the number four. And for the divisor of MOD, I'm going to go with four. I'm going to use four different combinations. Now, MOD of four with a divisor of four is going to return zero, and the CHOOSE function starts from one. So, I'm going to do +1. Now, I'm going to give it four different values to choose from. Let's say the first one is 10, the second one is -10, the third one is 12, and then -30. Let me make sure I overwrite all the cells in this column with the formula. And that's the MOD function, right? So, it basically goes through this pattern, and it repeats it over and over. You can then play around with these numbers and adjust them in a way that fits your dataset.
Now, let's make some adjustments to our chart. So, notice our first date is 8th of January, but I don't want my axis to start from the 8th of January. I want it to start from the 1st and end on the 31st of December. So, select the axis, go to Axis Options. For Minimum, let's change this to the 1st of January. Press Enter. Change this to the 31st of December and Enter.
Now, I also want to remove these tick marks. So, let's scroll down. Tick Marks, Major Type, select None. I'm also going to remove the labels because I want to add the dates to my markers. So, let's expand Labels. For Label Position, select None. Now, don't click away yet. Let's go to Color Options. Adjust the color of the axis if you want. I'm going to keep it this color, but I'll increase the width. I'll go with four points. For Begin Arrow Type, I'll select a circle, and the End Arrow Type is going to be this. Notice we get this nice effect.
Now, let's remove the stuff that we don't need. I don't need these grid lines. Let me select them, press Delete. I don't need to show anyone these levels. Let me just delete those. Let's quickly add a proper title to this.
Now, it's time to add the data labels. So, select the markers, right-mouse click, Add Data Labels. I want to add both the date and the milestone. Now, to give this a bit more room so that it's all easier to read, I'm going to bring this down here. And let me just expand this.
Now, select the data labels. Go to Label Options. Place a check mark for Category Name. This is going to bring over the dates. Uncheck the value. Now, we want to bring over our milestones. Place a check mark for Value from Cells, and select this column, and OK this. Now, you're going to notice that this looks a bit crowded. What we want to do is split the date to be on a new line. So, we're going to go to Separator and select New Line. Now, let's also left-align these. So, I'm going to go to Home, Left Align.
Now, notice these ones look good, but these ones are a bit squashed. That's because my plot area is going all the way to the end here. So, I'm going to grab this plot area and push it a little bit to the side to give these data labels a little bit of breathing room. And look at this. Let me get rid of this chart border. Select the chart, go to Format, Shape Outline, No Outline.
The cool thing is that this is super flexible. So, if you want one of these markers to be a different color because it's an important milestone, you can color it differently. Let's say User Training is very important. I'm going to select the marker and select it again so that only this data point is selected. Then go to Marker Options. Change the size of the marker if you want. Adjust the shape as well. You can change the color. I'm going to go with this orange and select No Border.
And also, remember we're using Excel. So, if this date happens to switch to the 7th of August, all we have to do is adjust the date in our table. So, I'm going to switch the month from 7 to 8, and this jumps automatically. You can also remove milestones that you don't need or add new ones, and they're going to pull through automatically to your timeline.
You can also play around with this function and use different numbers or even increase the number of choices that you have. So, if I change this to MOD 5, I can add a fifth number. Something, though, that you might want to do to keep this really consistent is to adjust this bit in the formula because currently, my table starts from row 3, right? That's where the headers are, and the first row in the table is row 4. So, this results in a 4. If you have your table starting from another row, you might get a different result. So, you might not start with the first number. You might start with the third number or the fourth number. To make sure you always start with the first one — so basically, you always have this order — you could adjust this function to something like this: so, I'm going to take the current row and minus the row where the headers are. So, this is always going to give me a 1. So, in this case, it's 4 - 3. Now, I want the starting point to always be zero, so I'm going to do -1. This just makes sure that you consistently start with the first number here no matter which row this table happens to sit on. Okay.
So, this is how you can create professional timelines in Excel. And remember, you can download this template from the link in the description.
Now, if you want to learn to make a whole library of professional charts like this — clean, minimalistic charts that tell a story and actually get noticed in meetings — I've got a full training on that. It's called Professional Excel Charts for Business Reports. You're going to learn how to create impressive charts, and you're going to walk away with a library of templates ready to use with your own data. If you're interested, check out the link in the description below.
I hope you found this tutorial helpful. Thank you for dropping by, and I'll catch you in the next video.