Do you want to create the timeline or project milestone chart for your business in order to showcase your progress to your boss? it looks like no easy task at first isn’t it? Well in this tutorial, we will teach you to how to create one for you that stand out!
Below is the datasheet we will use to create Project Milestone or Timeline chart in excel;
As you can see we have;
Date: Value when tasks were started.
Tasks: Timeline of milestones you have achieved.
Height: The value which represents how high you want to show it on charts.
Helper Column: This is used to create the timeline.
STEP1: Highlight The Columns
Holding the CTRL key and select 3 columns i.e. Date, Height, Helper Column;
STEP2: INSERT A-Line Chart
A detailed tutorial for the same is shared here.
Go to Insert > Charts > Link Charts with Markers
Now right click on the chart, click on select data;
Now select Helper column on left and select Move up option in order to move the option upwards!
Select the blue lines and right-click on it and then click on change series chart type option;
When change chart type window open, select second last option stacked area – clustered column;
Now in the below options for the same window, select the Secondary Axis for Height column and click ok;
STEP3: Adding Data Labels
Right-click the blue chart again and select add labels on the following options;
STEP4: Format Data Labels
Now we have added numbers on top of our clustered data, now its time to format data labels as we do not need the numbered data right now.
In order to do that, right-click on any data number and select Format Data Labels;
Now, label window will open on your extreme right, in order to add milestone names, we have to replace these values with Tasks values. In order to do that first select Value from cells in Label field options;
Now select the values of tasks options and press ok;
STEP5: Design Your Chart
Now in order to give a look of a timeline, we will change the bar columns into thin lines.
Go to Chart Design > Add chart Element > Error Bar > More error bar options;
Now select height and ok;
In Error bar options on your extreme right of your workbook, make sure it has the following values;
Direction = Minus
End Style = Cap
Error Amount = Percentage at 100%.
Now, we have thin lines inside the chart, lets remove the blue column;
In order to do that, select blue column, right click > Format Data Series;
now from format data series panel, select the bucket icon and select the following values;
And here you got the timeline chart ready for you;