Timeline Charts are charts or graphs that depict the chronological execution of partial events of a much bigger event. For numerous reasons, users need to create a timeline chart in Excel. There are multiple ways to create a timeline chart depending on event types.
Let’s say we have tasks of a Project Timeline divided into weeks, required Working Hours as well as ongoing Progress. We want to create a timeline chart using the following data.
In this article, we use 2D Line, Bar Chart and 2D Line with Progress Bar to create a timeline chart in Excel.
Download Excel Workbook
Watch Video – Create a Timeline Chart in Excel
3 Easy Ways to Create a Timeline Chart in Excel
Method 1: Using 2D Line to Create a Timeline Chart in Excel
For this method, we will use a 2D line as the horizontal axis and multiple vertical lines for representing the project phases. We will also alter the direction of lines in the vertical axis to avoid data collision and being the chart too long. For that reason, we will need a Helper Column that has only 2 types of data, 1 and -1. The steps to create such a timeline diagram are described step by step below.
🔶Steps:
- First, we will need to insert a new field beside project progress called Helper Column.
- Then insert data into the cells by altering them like the following.
- Next, we will select the Weeks column data (cell range B5–B13) and Helper Column data (cell range G5–G13) together while pressing Ctrl on the keyboard.
- Now go to the Insert tab in the Ribbon and select Insert Column or Bar Chart under the Charts section.
- Select Clustered Column chart type among the chart types.
A chart will appear as soon as we select the type.
- After that, click on the bar chart, we will see a ‘+’ sign beside the chart. It’s called the Chart Elements. Click on Chart Elements and disable Gridlines. Also, in the Axes option, disable Primary Vertical.
- Again, click on the Chart Elements button and select the arrow beside Error Bars Select More Options.
Format Error Bars side panel will appear on the right side of the workbook.
- In this panel, change the Direction to Minus and change Error Amount to Percentage and set Percentage to 100.
After this step, close the panel by clicking the cross icon (x) in the top right side of the panel. Then we will have our chart looking like the image below. We can see a line has been inserted into those bars.
- Now we will need to remove those bars. So, select those bars like the following and go to the Format tab in the Ribbon.
- In the Format tab, click on Shape Fill under the Shape Styles section and select No Fill.
.
After this, our chart will look something like this image below.
- Now we will insert the Data Labels or the Project Phases at the end of each vertical line. So, we will select the lines and from the Chart Elements, select More Options under the Data Labels option arrow.
Format Data Labels side panel will appear at the right side of the workbook.
- In the Label Option, select the Value From Cells in the Label Contains A small box will appear asking for ranges of values for the data labels.
- Click in the box and select all the Project Phase cell range (C5–C13) from the table and click on OK.
We will see the project phases will be inserted into the chart along with the helper column data.
- Then, to remove the helper column data, untick the Value option under the Label Contains section in the Format Data Label panel on the right side. And select the Label Position as Outside End and exit the panel by clicking on the cross (x) button in the top right area of the panel.
Then we will have our chart like the image below.
- Finally, we will add an axis arrow in the horizontal axis just to show the workflow. So, we will select the horizontal axis and go to the Format tab in the Ribbon.
- Select the Shape Outline option under the Shape Styles section and click on Weight. Change the Weight to 3 pt.
- Again, go to the Shape Outline option and select Black color for better visibility of the axis while keeping the horizontal axis selected.
- Finally, again go to the Shape Styles section and select Shape Outline. Click on Arrows and select an arrow to show the workflow.
Finally, we will have our timeline chart ready with 2D lines like the image below.
- In the end, add a suitable chart title by clicking on it to finish creating a timeline chart in Excel.
Method 2: Creating a Timeline Chart Using a Bar Chart
This method is quite straightforward as it doesn’t need a helper column and a lot of customizations. The image below gives us an overview of the output we can expect from this method.
One advantage of this method is that we can give additional information like the Scheduled Hours and Worked Hours along with the workflow as well. The steps are below.
🔶Steps:
- First, we will select the cells containing Weeks, Project Phases, Scheduled Hours, and Worked Hours as well (cell range from B4–E13).
- Now in the Ribbon, go to the Insert tab and in the Charts area, select Insert Column or Bar Charts and select the first chart type, Clustered Columns.
We will get a chart like the following.
- Click on the chart, select the Chart Elements, and then disable the Primary Vertical and Gridlines.
- Again, click on the chart and select Chart Elements and enable Data Labels.
Now we will have our chart as follows.
- And to give the work direction, now we will insert a line in the plot. So select the horizontal axis and go to the Format tab in the Ribbon. Select Line Arrow from Insert Shape section.
- Draw an arrow line in the chart.
- Select the arrow and go to the Shape Format tab in the Ribbon. Select Shape Outline under the Shape Styles And select Black color for better visibility.
- Again, select the arrow and in the Shape Format tab in the Ribbon, select Shape Outline and click on Weight. Change the weight to 3 pt. This will make the arrow more visible.
Finally, our chart will look something like the image below.
- Finally, we will add a suitable chart title by selecting and editing it to end creating the timeline chart in excel.
Method 3: Using Combination of 2D Line and Bar Plot
So far, we have seen two methods where in one method, we had shown only a line chart and only a bar plot. But in this method, we will use both the bar plots and the 2D line plot to show a line chart and additional information with it.
The steps for this method are below.
🔶Steps:
- Since this method is also using both sides of the vertical axis, we need the Helper Column So, we will insert the helper column as we did in the first method like the below image.
- Next, insert data into the Helper Column as before.
- We will multiply our Scheduled Hours and Worked Hours with Helper Column data since we need to show them in the directions of the helper column. So, we will insert 2 new sub-columns named Scheduled Hours and Worked Hours under a new column named Helper Data.
- For getting data in the Helper Data field, we will use formulas. For Scheduled Helper Data, click on cell H6 and type the following formula in the formula bar and press Enter.
We will get the result like the following image.
- Now drag the Fill Handle from cell H6 vertically to fill up the rest of the cells.
- We will do the same for Worked Hours Helper Data. First, insert the formula in cell I6 and press Enter.
We will get results like the following image.
- Again, drag the Fill Handle vertically to fill up the rest of the cells as well.
- Now we can create the timeline chart since we have all the data needed. So, we will select the Weeks data (cell range B6-B14) and Helper Data (cell range H6-I14) while pressing the Ctrl button for the chart like the image below.
- Then we will go to the Insert tab in the Ribbon and in the Charts section, select Insert Column or Bar Charts and click on Stacked Columns chart type.
As a result, we will get a chart like in the below image.
- Here, at first, we will disable the Gridlines and Primary Vertical axis from the Chart Elements So, we will click on the chart, select the + sign beside the chart and then untick the Gridlines. Also select the arrow beside Axes option and disable Primary Vertical option as well.
- Now click on the horizontal axis and go to the Format tab in the Ribbon. In the tab, click on Shape Outline under the Shape Styles section and select Black color for better visibility.
- Again, go to the Shape Outline option in the Format tab and select Weight. Change the Weight to 3pt.
- Again, we go to the Shape Outline in the Format tab in the Ribbon and select Arrows. Select any type of arrow that you want to show your workflow.
Finally, our chart will look like the below image after adding the arrow line.
- Since the writing in the axis is not that much visible, we will reduce the bar color depth to overcome that problem. So, we will select the blue bars and then go to the Format tab in the Ribbon. In the Shape Fill option, we will select a lighter color to clearly visualize the writing.
- Again, select the red color and repeat the same with the bar as well.
After these steps, the chart will look like the following.
- Click on the chart, select Chart Elements or the ‘+’ button beside the chart. Enable the Data Labels.
- We will not need the labels in the blue bars. So, select the label, right click on them, and delete them.
- Now select the data labels in the red bars and drag them on the top of the bar one by one.
After dragging all the labels out, the chart will look like the following.
- Here we can see there are some connecting lines that are connecting the red bars with the labels. So, we will delete them since they are not necessary. So, select all the bars by clicking on one of them, right click on them and then click on Delete. This will remove all the connectors.
- Now we will change the name of these bar plot series in the legend. Select the legend and right click on it. Click on the Select Data option.
Select Data Source box will appear.
- Next, click on Series 1 in the box and click on Edit. Edit Series box will appear.
- In the Edit Series box, give the series name Scheduled Hours as series 1 or the blue bars represent Scheduled Hours in the Helper Data Then click on OK.
- Repeat the same procedure with Series 2 as well.
Change the series name to Worked Hours and click on OK and again click OK in the Select Data Source box and exit it.
- We will see the legend is updated now.
- Finally, we will need to change the Data Labels. For that, we will click on Chart Elements sign and select More Options under the Data Labels command.
- That will open the side panel named Format Data Labels in the right side of the workbook. Here we will select Values From Cells in the Label Contains section.
Now Excel will ask us to input the data label range cells in the Select Label Range section.
- We will select the cell range C6–C14 as they contain the Project Phase names and click on OK.
We will immediately see the project phase has been written on the bar plots on relevant weeks.
- Now in the Format Data Label panel, untick the Values in the Label Contains section and exit the side panel.
- Finally, we will give the chart title by clicking on it and editing it to finish creating the timeline chart in Excel.
Things to Remember
- Always try to make the chart big enough to visualize all data labels and legends.
- You can further beautify the chart by adding a glow effect from the Format tab in the Ribbon.
- Keep the chart coloring light enough to see or understand the labels.
Conclusion
In this article, we demonstrate some of the basic methods to create a timeline chart in Excel. Insert Line, Insert Scatter, and Pivot Chart are the main go-to to create timeline charts. Hope you find your desired one among the mentioned ones. Comment, if you have further inquiries or have anything to add.