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?

      • Lew Brereton

        Hi, you can do this with the following steps:

        1. Add the additional data to your table.
        2. Click on one of the long thin lines on the graph which points to the ‘Activity’.
        3. You should see that on the table, the data is highlighted in little coloured boxes. Simply drag this down to incorporate the additional data. Click anywhere on the sheet besides the table or graph when the new data has been highlighted to deselect them.
        4. Right click the chart and choose: ‘Select Data…’
        5. Select ‘Date’ in the left column (Legend Entries)
        6. Select ‘Edit’ in the right column (Horizontal Axis Labels) and select all dates, including the new dates.
        7. Select ‘Activity’ in the left column and repeat step 6, ensuring all activities are highlighted, including the new activities.
        8. Press OK to exit the Data Selection window.

        That should be it! Just adjust the new labels to be in the correct position and you should be good to go.

        Lew