“Can I get an overview of the new reporting project? This afternoon...”
A phrase you may hear from your manager… a little too often. I’m guessing something like this is said in thousands of offices around the world every day.
Because you're down in the weeds working through the detail of the project, you have a very long and comprehensive list of tasks. Perfectly set out for day to day work. Definitely not designed for slides in a meeting with management – you won’t be able to read the text in the back row, probably not even in the front row.
So, you need a high-level summary of the main actions and events that need to happen to make the project a success. Something you can easily update (this won’t be the only review) and looks good enough to put on a slide.
Let me show you how we can pull this together as a project timeline in Excel, using a scatter chart. It's also possible to create a timeline using a bar chart, though this scatter plot method is more common. This guide is for Excel 2016 on Windows, but the steps are similar for other versions of Excel, including on Mac.
Also, if you do have an option to use a web-based timeline software, check out our service, Preceden Timeline Maker, which will save you a lot of time and help you create a much better-looking timeline than Excel is capable of. But if your only option is Excel, you're in luck - there's still a way to do it.
Here's the timeline we'll be creating in Excel - you can download the spreadsheet here (ZIP, 22kb).
First of all, create a very simple data table with the high-level actions and events that you want to track on the timeline.
In the first row there is an action called Today. This will be used to create a line on the chart marking today’s date.
The rest of the rows are your events.
In the second column add the start date of the task and the third column the planned duration in days.
In the example, I’ve used a simple formula to get the correct start dates based on how long I expect the actions to take. In a couple of cases I also run tasks in parallel so wind up making some manual adjustments to the date.
Last Task Start Date + Last Task Planned Duration
You don’t have to do this – especially if you have a lot of things running in parallel. Manually type the dates in and do the math in your head for the start date. I find a formula helps me get it all aligned first time.
In the fourth column, % Complete, make an estimate for the amount of work already completed.
The next column, Completed, is a calculation of how many days of work is complete:
Planned Duration x % Complete
The final column, Position, is used to get everything looking nicely placed on the chart. We’ll come back to this later but for now just fill with numbers with some even spacing (mine have already been adjusted for the final chart – that’s why there are a few larger gaps).
That’s the data table completed. Now onto the chart…
This is where we start to get the information from the data table into something like a timeline. It won’t look polished immediately, so don’t worry – we’ll work on formatting in a later step.
Select Insert on the ribbon and in the charts group, go to the dropdown for scatter charts.
Select the first scatter type in the menu. This one doesn’t join data points with any lines.
This will give you a completely blank chart. Move it somewhere where it’s not covering your data table and if it looks small, stretch out the sides with the handles.
Now to get some something into the chart.
Select the chart and on the ribbon go to Chart Tools, Design tab and click select data.
Then click Add a data series.
Now we need to add the cell references for the chart. Keep in mind that on your own spreadsheet, your table may cover different ranges (and may have more or less rows depending on your action / event list).
Select D4 as the name – “Planned Duration” column header.
For the X series select C6:C14 – this is the “Start Date” column.
For the Y series select G6:G14 – this is the “Position” column.
You might notice we didn’t take the row that has our date “Today”. We will add this as a separate series later.
When the Select Data Source dialog is ready to go it looks like this:
The chart is now populated with some data. Select the title on the chart and update it to something more meaningful.
The chart has some data but doesn’t look like much of a timeline yet. The next two steps will work some magic.
Time to get our timeline looking like an actual timeline.
First, select the X axis (that’s the one on the left) with the mouse and hit delete on the keyboard – we don’t need it.
You see the large gridlines covering the chart plot here? We want to remove those as well.
Select the Vertical axis major gridlines and hit delete.
Do the same with the Horizontal axis major gridlines – select and hit delete on the keyboard.
That gives us a nice clean background.
Next, we’ll add the description of the actions and events to the chart as data labels.
With the whole chart selected, go to the Chart Tools, Design ribbon and then click Add Chart Element on the top left.
Go to Data Labels and select the Left aligned labels.
You’ll notice the height number came up as the data label. Next step is to manually change these. It’s a bit painful as they have to be done one by one (and one of my pet-peeves with scatter charts).
Select your first data label with the mouse. You’ll see all data labels highlighted. Click again on the same data label and only that data label will be selected.
Now type = and then select the cell where the action / event is. You could also click into the formula bar and do the same.
Go through each data label in turn (I know, it’s tedious but at least you’ve dynamically linked them to your table – if you make changes to the action name, it’ll update automatically on the timeline).
You should be left with something like this…
Continuing with the formatting, we’re going to improve the look of the markers for the start dates of the actions.
To begin with, click on any data point in the chart to select the whole series.
Right click on that point and select Format Data Series from the dropdown menu.
Select the Fill & Line on the sidebar box and then select Marker.
Under Marker Options select Built In and change the type to diamond (or use whatever you prefer – no hard and fast rule so play around with different styles).
Also increase the size to around 10.
You can change the color under the Fill menu. I’m sticking with the default blue in my version.
If you want, you can then change individual markers. I’m going to do this for my key milestones (i.e. the events that have duration of 0 days in my overview table).
Select a data point then click again so it is the only one selected. Right click and select Format Data Point. Follow the same process for changing the data point marker shape, size and color as you did for the series.
I’m going to color the milestones that are in the past (and are completed) in green and future ones in black. Every time I present this timeline, I can manually update the color of these milestone markers.
It still doesn’t look like a proper project timeline as we’re missing any indication of how long the actions should take. With some clever use of error bars this step will knock things into shape.
Select the chart.
On Chart Tools, Design on the ribbon menu, go to Add Chart Element and then scroll down to Error Bars. At the bottom of this submenu select More Error Bar Options.
This brings up the sidebar.
On Error Bar Options there’s a dropdown arrow. Click this and select Series “Planned Duration (days)” X Error Bars
Go to the Error Bar Options in the sidebar (the chart icon).
Then select Direction as “Plus” and End Style as “No Cap”
The Error Amount should be set to Custom and then select Specify Value.
This will bring up a dialog box.
For the Positive Error values select D6:D14. This is the Planned Duration column. Leave Negative Error Value as it is (it won’t display on the timeline).
Lines will now extend out of the markers from left to right. The length is equal to the duration of the task.
To make them look a little more polished, select the bars, go to the Fill & Line menu on the right.
Change the color if you want but I’m keeping the default grey. Make the lines thicker. I find 3pt or 4pt works well.
Now we will use Y error bars to add a line that drops down to the horizontal axis. This makes it easier to see the start date of tasks.
With the Format Error Bars sidebar still open, select the dropdown arrow on Error Bar Options. Then select the Series “Planned Duration (days)” Y Error Bars (just like the way you selected the X error bars).
Change the Direction of the bars to "Minus" and the End Style to "No Cap".
Change the Error Amount to Percentage and set it as 100%.
Lines will drop to the horizontal axis. They don’t look wonderful, so let’s format them.
Keep the Y error bars selected and go to the Fill & Line option on the sidebar.
Change the line to solid line, color to whatever you want (I’m going blue), make them a little wider (2pt is good) and then change Dash Type to the dotted line. Experiment with what looks best on your chart.
At this point, you might notice that where you have multiple actions and events with the same start date, the dotted (or dashed line) looks solid as they overlap.
To change this… remember the Position column in the data table? This is where we can tweak the numbers in this column to adjust the spacing and get everything looking ok. Takes a bit of trial and error.
Once everything is lined up, the timeline is really starting to look good…
A project timeline overview is more useful when we can see the progress against the planned duration. The data table has input for % completion of each action and we can use that as a new data series over the top of the existing data.
Select the chart and on the Chart Tools, Design, click Select Data.
Add a series.
The Series Name is cell F4 – the column headed “Completed”
The X values are C6:C14 – the “Start Dates”
The Y values are G6:G14 – the “Position”
Then click OK again.
This overlays a data series which looks like it’s messed up the previous formatting. Don’t worry, we’re going to fix it.
With the just added series selected, go to the Marker options on the sidebar. Select "None".
(If the sidebar isn’t open, right click on one of the data points of the new series and then select Format Data Series).
If the Line is showing, go to the Line Options and select "No Line".
You probably have some data labels showing as well. Go to the Add Chart Element top left of the screen, go down to data labels and select "None".
To make the Completion % actually show on the chart we need to use error bars in the same way as we did for the task duration.
With the data series still selected, go to the top left Add Chart Element, Error Bars, More Error Bar Options.
In the side bar select the Series “Completed (days)” X Error Bars.
Set the Direction as "Plus" and the End Style is "No Cap".
In Error Amount go to "Custom" and then Specify Value.
Then for Positive Error Values select the column with the Completed (days), in my case F6:F14.
Leave Negative as it is. Hit OK.
Now we need to adjust the formatting.
With the error bars still selected, go to the Fill & Line and change the color. Green is the obvious choice as this is what we’ve completed.
Then change the width to something thicker than the grey error bars that you added earlier. If you went with 4pt then 7pt works well.
You’ll notice that there are some Y error bars displaying on this data series. We don’t need them, so you can select them with the mouse and press delete on the keyboard.
You might remember that the first row in the data table was for an event called Today. So far, we haven’t used it.
In this step we’ll add that row onto the timeline as a line marking today’s date.
Very similar set up as before so this should be starting to sound very familiar…
Select the chart and then Select Data and Add a new data series.
The Series Name is B5 – “Today”
The X value is the Start Date in C5 and the Y value is the Position in G5.
Hit OK, then OK again.
You should see a label next to the newly added data series. Select the label twice and then type = and then select the cell C5.
On Add Chart Element menu up on the left, scroll to Data Labels and change the position to "Above". Bold or Italicize the label if you like, to make it stand out.
With the series selected go to the sidebar and on the Marker Options, select "None". If there’s a line, do the same on the Line Options.
Now go to Add Chart Element and select More Error Bars Options.
On the sidebar click the Error Bar Options drop down and select Series “Today” Y Error Bars.
Change the Direction to "Minus" and the End Style to "No Cap". In Error Amount set the percentage to 100%.
Go to Fill & Line to format the line how you want. I’m going to change the color to red and thicken to 3pts.
We don’t need the X error bar, so you can click it with the mouse to select and hit delete on the keyboard.
And there you have it. The final project timeline in Excel:
For the formatting of the overall chart, I’ve gone for a light blue background but make it stand out for presenting.
With a bit of time, you can set up this timeline as a template for reuse in all of your projects. Then every time you get that call into the boss’s office for an update, you can quickly pull your overview together.
If you want to try an easier way of creating a timeline, check out our timeline maker tool. You can create timelines like this one using our easy-to-use web app which you can then download, share, and more: