How to Create a Milestone Chart in Excel

- Written by Puneet

how to create a milestone chart in excel

Tracking all the steps of a project is an important task. You know this, right It’s as important as the execution of each step. In fact, tracking makes execution easy.

In Excel, one of the simplest yet power charts which you can use to track your projects is a MILESTONE CHART. They also call it the “Timeline Chart”.

It’s one of the favorite project management tools of Experts. It visually shows a timeline where you can specify key milestones, deliverable, and other checkpoints.

According to Wikipedia: Milestones are tools used in project management to mark specific points along a project timeline.

The basic idea in a milestone chart is to track each step of your project on a timeline with its completion date and present it in a simple way.

Today in this post,  I would like to share with you a simple 3 steps process to create a milestone chart in Excel.

So let’s get started.

Benefits of Using a Milestone Chart

Before we get into it, let me tell you some core benefits of using a milestone chart.

  • It’s easy to check the progress of the project with a milestone chart.
  • Easy for the user to understand the project scheduling.
  • You have all the important information in a single chart.

Steps to Create a Milestone Chart in Excel

I have split the entire process into three steps to make it easy for you to understand.

1. Set Up Data

You can easily set up your data for this chart. Make sure to arrange your data like below data table.

data to create a milestone chart in excel

In this data table, we have three columns.

  1. The first column is for completion dates of the project stages. And, make sure the format of this column should be text format.
  2. The second column is for activity name.
  3. The third column is just for placement of the activities into the timeline (up and down).

2. Insert a Chart

Now here’s the game starts. Creating a milestone chart is a bit lengthy process but it’s worth for this amazing chart. 

Here are the steps.

  1. Select any of the cells from the data.
  2. Go to Insert Tab ➜ Charts ➜ Line With Markers.
    insert a line chart with markers to create a milestone chart in excel
  3. We’ll get a chart like this. But, this is not what we want, we have to re-create it.
    raw chart to create a milestone chart in excel
  4. So now, right click on the chart and then go to “Select Data”.
    select data to create a milestone chart in excel
  5. In select data window, just remove series from legend entries.
    remove series to create a milestone chart in excel
  6. Now, our chart is totally blank. So, we have to re-assign series and axis labels.
  7. Click on “Add” from legend entries.
    click add to create a milestone chart in excel
  8. In edit series window, enter “Date” in the series name and select activity column for the series values.
    select series to create a milestone chart in excel
  9. After that, click on edit in “Horizontal Axis Labels” and refer to the date column and click OK.
    edit axis label to create a milestone chart in excel steps
  10. Next, we have to insert an another series. Click on “Add” in legend entries and name it “Placement” and refer series values to placement column.
    add new series to create a milestone chart in excel
  11. Now, just click OK.

At this point, we have a chart which looks like a timeline. But we need a small touch of formatting to make it a perfect milestone chart.

final chart to create a milestone chart in excel steps

Download this file to get the chart up to this step.

3. Final formatting

A small touch of formatting. Follow these simple steps.

  1. Click on the line chart and open formatting option.
  2. For line, use “No Color”.
    no line to create a milestone chart in excel
  3. With the same selection, go to Design Tab -> Add Chart Element -> Error Bars -> More Error Bars Options.
    add error bars to create a milestone chart in excel
  4. Now, from the formatting options, select direction “Minus” and error value “Percentage: 100%” for error bars.
    format error bars to create a milestone chart in excel
  5. After that, convert your line chart into secondary axis and instantly delete the secondary axis.

Now, the last and important thing you have to do, add the activity name for each step.

  1. First of all, add data labels.
  2. Now, from data label formatting options, select “Category Name”.
    add data labels to create a milestone chart in excel
  3. After that, select your chart and click on “Select Data”.
  4. Click on “Placement” series.
    edit series to add data labels to create a milestone chart in excel
  5. From axis label, click on edit and refer to activity column.
    select data label range to create a milestone chart in excel
  6. Click OK.
    final chart with formatting to create a milestone chart in excel steps

Sample File

Download this sample file from here to learn more.

Conclusion

As I said, milestone chart is easy to understand for the end-user and you can track your project scheduling in a simple way. It looks a bit tricky when you are making it for the first time but, if you are an emerging project manager then it’s just for you to give it a try.

I hope you found this step-by-step method helpful but now tell me one thing.

Have you ever tried this chart before?

Share your views in the comment section, I’d love to hear from you, and please don’t forget to share them with your friends.

16 thoughts on “How to Create a Milestone Chart in Excel”

  1. Great tutorial thank you.

    It seems many had a hard stop when getting to the step to “Convert to secondary axis”.

    What I discovered is that this simply means to click on the horizontal and slightly dimmed lines. That will “select” the secondary lines for you and the edit dialog box that pops up will be for those lines.

    For this tutorial, once you click on the horizontal lines, you can just hit the delete key and the lines will be gone.

    Reply
  2. Hey, I really like your excel tutorial. It’s very detail, clear and simple steps that we can follow and practice. I try to follow your step to make it work. However, I have problem on last step. I am using your data sheet and unable to show different label name on this chart. If I change Placement Axis label range to activity name, the time/date label will also be changed to activity name at the same time. It’s hard to show different label name on two series line on last step.

    Reply
  3. convert line chart into secondary axis and instantly delete the secondary axis ! whats this, can you explain.

    Reply
  4. This is great! I had already used this method, but by converting the activities into a bar chart, which didn’t work so well. Keeping it as a line graph is better. One suggestion though, when you start the chart, insert a line graph when you are in a blank cell so you start from a blank chart – then you can add data from there. This cuts out having to remove data at the beginning 😀

    Reply
  5. Nice presentation but I have only Excel 2010 and a lot of the choices you require are not there. Oh well

    Reply
  6. How can we create a vertical timeline where the timeline goes up and down and the steps/tasks are on the left and right? The reasoning is that this can be displayed better in a blog post which is long and scrollable, thanks.

    Reply
  7. When you said: “After that, convert your line chart into secondary axis and instantly delete the secondary axis.”, I couldnt understand. may you explain different? Thanks

    Reply
  8. There are no images shown in this article. Can you fix it?

    Reply
  9. how can i convert line chart into secondary axis and instantly delete the secondary axis.

    Reply
  10. Thank you, Puneet! Using error bars solved the problem of the data labels floating around whenever the data changes. Now, they stay right next to the data point!

    Reply
  11. This is awesome! There are so many applications for this tip. I can see making a marketing timeline for a product launch!

    Reply

Leave a Comment