How to Create a Timeline / Milestone Chart in Excel + Free Template Inside

Watch Video – Creating a Timeline / Milestone Chart in Excel

In the projects I have worked so far, Milestone Charts (also known as timeline charts) are often one of the most discussed parts.

A commitment to deliver is as important as the project itself. A milestone chart is an effective tool to depict project scope and timelines.

In this post, I will show you a simple technique to quickly generate a Milestone chart in Excel.

Something as shown below:

Timeline / Milestone chart in Excel Pic

Steps to Create Milestone Chart in Excel

  1. Get the data in place. To create this, I have two columns of data (Date in B3:B10 and Activity in C3:C10) and three helper columns.
    Timeline / Milestone Chart in Excel Dataset
  2. Go to Insert –> Charts –> Line Chart with Markers
    Timeline / Milestone Chart in Excel - Line Chart with Markers
  3. Go to Design –> Select Data
    • In Select Data Source dialogue box, click on Add
    • In the Edit Series Dialogue box
      • Series Name: Date
      • Series Values: Activity Cells in Column F
        This inserts a line chart with X Axis values as 1,2,3.. and Y axis values as 0
  4. In the Select Data Source dialogue box, click on Edit in Horizontal (Category) Axis Labels and select dates in Column E. This changes X Axis values to dates.
    Timeline / Milestone - Excel Line Chart Changing Horizontal Axis Values
  5. In Select Data Source dialogue box, click on Add
    • In the Edit Series Dialogue Box
      • Series Name: Activity
      • Series Values: Text Placement Cells in Column G
        This inserts a haphazard line chart. Something as shown below:
        Timeline / Milestone - Combination Line Chart
      • Click on any of the Activity data-point, right-click and select change chart type. In Change chart Type dialogue box, select Column chart. This will change the haphazard Line chart into Column Chart
        Timeline / Milestone Chart in Excel - Combo Chart Excel
      •  This will change the haphazard Line chart into Column Chart
        Timeline / Milestone Chart in Excel Line chart
  6. Right click on data bars and select Format Data Series. In series option pane, select
      • Plot Series on: Secondary Axis
        This would introduce a secondary vertical axis on the right of the chart. Click on it and delete it.
  7. Go to Design –> Select Data
    • In Select Data Source Dialogue box, select Activity series and click on Edit in Horizontal (Category) Axis Labels box. In the Axis Labels dialogue box, select Activity cells in column F
  8. Select bars, right click and select Add Data Labels
  9. Right-click on the data label and select Format Data Label
      • In format data label pane, select Category Name (and un-check any other). This adds activity name as data label. Adjust the position to get activity name at the tip of the bar
        Timeline / Milestone Chart in Excel - Data Label formatting
  10. Select bars and go to Design –> Add Chart Element –> Error Bars –> More Error Bar Options [For 2010, this option is in Layout –> Analysis] Timeline / Milestone chart in Excel - Insert Error Bars in Excel
  11. In the Format Error Bars Pane, make the following selections
      • Vertical Error Bar: Minus
      • End Style: No Cap
      • Error Amount: Percentage – 100%
  12. Right click on bars and select Format Data Series. In Format Data Series Pane (in Fill and Line)
      • Fill: No Fill
      • Border: No Border

That’s it!! Your Milestone Chart in ready. Garnish it and serve it hot 🙂

Try it Yourself.. Download the file from here
Download File Pic

You May Also Like the Following Excel Tutorials:

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)

  • Greg

    Thanks for sharing the Template. I will now use this in creating project plans.

  • Amber

    This is awesome! VERY useful and i shall implement it for my next project!!

  • Shishu

    I found it extremely helpful and was able to successfully create it but later when I happen to add any additional date, activity, it messes up the graphic completely. Can I gracefully modify it when a new task/activity has to be added without messing up the graphic?

    • Billy

      Did you get ever work this out in the end? I’m currently having the same problem. Is there a way for me to insert new events onto the timeline without having to start over again?