Once you get to a certain point in your career, you realize that you actually spend a lot of time summarizing complicated pieces of work for the managers above. It really becomes a big part of your job.
One area is projects. Although you might be happy working with the detailed task lists, giving an update to management needs a higher-level view of the day to day work.
Something that can easily be copied and pasted into a presentation slide.
With a bit of up front effort it’s possible to use a bar chart in a spreadsheet to give a nice, visual overview of your project timeline. Using a bar chart to create a timeline in Excel is less common than using a scatter plot, but still an option depending on what style you prefer. In this tutorial we'll walk you through how to create it.
This tutorial is written for Excel 2016 for Windows, though the steps are similar for Excel for Mac. We'll walk through steps similar to those outlined in this YouTube tutorial:
Here's what the final result will look like - you can download a spreadsheet example here: Excel Bar Chart Timeline Example (ZIP, 20kb).
First of all, you need to create a table of your actions and events with a column for the date this takes place.
There are a couple of other columns that will be used in the technical set up of the chart.
The Label Height column will determine where our labels are output on the chart. Use equal spacing, with max and min between 20 and -20. You can change this later to get the final chart looking balanced.
The baseline column is always all zeros. This is going to be a visual line with an arrow depicting the timeline.
The two columns will make more sense later.
Once the table is finished, go to the Insert menu on the top menu ribbon. Find the Charts group and select the dropdown for bar charts.
Select the first 2D chart.
This will give you a blank chart on the page. Move it off your data table and then use the handles round the border to make it bigger.
Select the chart with the mouse, go to Chart tools and then Design on the ribbon.
Click Select data.
This dialog box should appear, patiently awaiting your inputs:
Click Add on the left-hand side to add a new series.
On the box that opens up, leave the series name blank and add E6:E15 as the series values (assuming your data table is in the same position).
Click OK to go back to the main series data dialog box.
You need to add the Baseline column as the next series. Click Add.
Again, leave the series name blank and select the values as F6:F15. Click OK.
On the select data source dialog, click edit on the right to add the horizontal X axis labels (your actions and events).
Select the range C6:C15 and click OK.
The data source box looks like this:
Click on the chart title and change to something more relevant to your project.
It doesn’t look like a timeline yet so let’s move on...
Now we’re going to add the baseline series (remember the column full of zeroes?) as a line chart on top of the bar chart. That way we can really highlight the movement of time from left to right.
First of all, click on the visible series (any bar will work) and then right click. Select Format Data Series on the dropdown menu.
The sidebar pops up on the right of the window.
Click on the Series Options dropdown and select Series 2.
Add the series to the Secondary Axis.
With this Series 2 still selected, go to Change Chart Type on the top ribbon menu.
Then select the chart type dropdown on Series 2 and select the first line chart on the menu.
Now to format this line that has appeared on the chart. Right click on it and select Format Data Series. Go to Fill & Line on the sidebar menu.
Choose whatever color works for you. In this example we’ll leave it orange. Thicken the line to 6pts.
Finally, change End Arrow Type to an arrow.
You’ve probably noticed that our dates haven’t been added to the timeline yet. Time to fix that...
Select the chart and then go to Select Data on the Chart Tools, Design ribbon.
Select Series 2, click on edit for the horizontal axis (on the right-hand side of the box).
Select the dates column D6:D15.
Click OK, and then click ok.
Dates don’t show yet, but we’ll get there when we do the final formatting in a later step.
Now you will use error bars to "link" the action descriptions to the timeline. It’ll make sense when you see the outcome!
Click on a blue bar to select all of that series.
Go to top left Add Chart Element, go down to Error Bars, More Error Bars Options.
On the right-hand side bar, change Direction to Minus, and the End Style to No Cap.
And the Error Amount should be set as a Percentage at 100%.
Go to Fill & Line and change the thickness to 3pt. Change the color and dash type if you like.
Now click on a blue bar again. Format Data Series should still be open on the right sidebar. If not, right click on a bar in the chart and select Format Series.
Select No Fill and under Border, select No Line.
This will make the bars disappear, just leaving the error lines behind.
Click the vertical Axis Major Gridlines on the chart and hit delete on the keyboard. This clears the lines from the chart plot area.
Then change the scales on both vertical axes. To do this, right click on an axis, select Format Axis.
Under axis options on the sidebar, change the Bounds to Minimum -20 and Maximum +20.
Do exactly the same with the other axis. This will line them up and you should see your baseline now in the middle of the chart.
If you have more or less actions, then you may need to go bigger or smaller than +20 and -20. All comes down to playing with the Label Height column on your data table to get your spacing looking good.
Now the axes have been corrected, we can remove them from the chart: we don’t need to see them.
Select an axis with the mouse and press delete. Repeat with the other one.
It’s starting to take shape and look like a real timeline. Not much more to do.
You’ll notice the dates are still missing even though earlier we added them as a horizontal axis. Now to make them visible.
Select Series 2 (the orange line on the horizontal axis).
Go to Chart tools, Design on the ribbon.
On the top left, click Add Chart Element, then down to Data Labels followed by More Data Label Options.
This opens the sidebar to format the data labels.
Click Label Options and select Category Name under Label Contains.
Change Label Position to Below.
Now use the dropdown to select Series 1 (the hidden bar chart). With it selected go to Add Chart Element on the top left and scroll down to data labels and More Data Label Options.
This opens in the right-hand side bar.
Go to Label Options and then change Label Contains to Category Name only.
Change the Label Position to Outside End.
Now select the horizontal axis on the chart and hit delete on the keyboard.
You should now see your actions as labels at the end of the lines touching the horizontal line. The dates will show below the horizontal line.
If your dates are a bit squashed, you might need to make the chart longer or angle the text (via format data labels).
And that’s it. The timeline is done. The formatting in the example is very simple but you can play with it to get the right color combination for you.
The downside of this timeline is it doesn’t give a visual idea for the duration between actions. Everything is equally spaced.
But it’s a very simple to use once your template is set up.
And it does give a good overview of the project timeline to include on a slide in a meeting.
As we've seen here, it is possible to create a decent looking timeline in Excel only, but if using a specialized tool is an option, you might want to try our timeline maker tool which makes the process much, much simpler: