Project Management is no easy feat!  There are lots of moving parts, long hours and constant reporting.

It requires you to keep everybody on the same page and focused on the goals and timelines.

One of the key reporting tools needed is the project milestone or timeline chart.  Luckily you can create a project milestone chart in Excel in just a few steps…well, 11 to be exact!

 

Below is the data source that we are going to use to create our timeline of milestones in Excel:

We have Dates and Milestones.  The Height specifies how high we want the milestone to show up in the chart, whilst the Helper Column is used to create the timeline.

In this example I show you how easy it is to insert a Timeline of Milestones using Excel.

NB: This tutorial was made for Excel 2013 and Excel 2016 versions.

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: Highlight the following columns: Date, Height, and the Helper Column. You can select the 3 of them by holding the Ctrl button while highlighting them:

 

STEP 2: Go to Insert > Line Charts > Line with Markers

 

STEP 3: Right click on your chart template and choose Select Data.

 

STEP 4: Select the Helper Column and press the Up arrow to move it up in order and press OK.

 

STEP 5: Select the Blue Lines (Height) & Right Click & select Change Series Chart Type.

 

STEP 6: Under the All Charts tab, select the Combo chart.

From the Height drop down, select the Clustered Column chart type and “check” the Secondary Axis box

From the Helper Column drop down, select the Line with Markers chart type and “uncheck” the Secondary Axis box

Press OK to confirm.

The reason we are doing this is so that our heights would now show up as columns in our timeline.

The selections should look like this:

 

STEP 7: Select the blue column chart & right click & select Add Data Labels.

The magic is about to happen!

 

STEP 8: The height numbers will show up, however we do not need the height numbers to show up.

Select any one of the height numbers, right click and select Format Data Labels.

 

Under the Label Options (first icon from the right), make sure that only the Value From Cells is selected  and the other boxes are deselected.

The Label Position should be Outside End.

Then highlight the Milestone values from the data source and press OK.

This will ensure that the Data Labels will be the Milestone text instead, which is what we want our timeline to show.

 

STEP 9: Now we will change the columns into thin lines.  For this we will use the Error Bars.

Click anywhere on the chart template.

Go to Design > Add Chart Element > Error Bars > More Error Bar Options

Select Height. Click OK.

From the Format Error Bars pane on the right hand side of your workbook, make sure the Error Bar Options (first icon from the right) is selected and choose the:

Direction = Minus

End Style = Cap

Error Amount = Percentage at 100%.

This will ensure we have thin lines go all the way down the x-axis.

 

STEP 10: Now that we have the thin lines inside the column chart, let us remove the blue columns.

Right click on the blue column chart and select Format Data Series.

 

From the Format Data Series pane, select the Fill bucket (first icon from the left) and make sure that the following are selected:

Fill =  No fill

Border = No line

 

STEP 11: And now for the finishing touches!

Let us delete the unnecessary labels in our chart to make it look nicer.  Just click on each element and press the Delete key from your keyboard.

And there you have it! Your own Timeline of Milestones!

Feel free to change the data source so you can see how your cool new Timeline chart changes.  Happy project managing using Excel!

 

HELPFUL RESOURCE:

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Candlestick Chart Using Excel Stock Data Analysis is no easy feat!  Once you have a lot of historical stock data it's hard to visualize the trend using technical analysis.Thankfully Excel has a lot of stock charts to help you with that, and one of them is the Candlestick Chart!A Candlestick Chart has ...
Stock Line Chart Using Excel Ever had a lot of historical stock data closing prices and you want to visualize how it is going so far?You can actually use the good old Line Chart in Excel to create your own Stock Line Chart!Below is the data source that we are going to use to create our Stock Line Cha...
Add Trendlines to Excel Charts With Excel Charts, it is very easy to create Trendlines for your data.  Trendlines show which direction the trend of your data is going, and gives you the trajectory as well.In this example, I show you how easy it is to insert a Trendline using an Excel Line Chart.DOWN...
Create a Radar Chart in Excel For Performance Revi... Radar Charts in Excel are very handy to show a rating on several performance areas.When I had to give out performance reviews, it was a very helpful snapshot for me to show to my colleagues how their performance was compared to last year!Here is an example of a table with...