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 delivering 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 a change chart type. In Change chart Type dialogue box, select the 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 names as data labels. Adjust the position to get activity name at the tip of the bar
        Timeline / Milestone Chart in Excel - Data Label formatting
  10. Select 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 is 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:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

13 thoughts on “How to Create a Timeline / Milestone Chart in Excel + Free Template Inside”

  1. Hello, this is just fantastic, love it. I was wondering if you knew (or if it was possible) to not include middle unused dates. I am making an evolutionary timeline, so jumping 100’s of millions of years is going to make the graph rather long..

    Thanks!

    Reply
  2. Hi Sumit, what would the steps be to create a timeline like the first one shown, but using Excel for Mac 2011? My dialogue boxes are very different from your instructions, and I can’t quite figure it out!

    Reply
  3. Is there a way to use a chart like this but to have subtasks? I’m driving myself crazy trying to learn how to add in a subtask. I was thinking i would resort to hand coloring each task one color then the subtask would match but be a little lighter. But this project will span several years and I really want to avoid and manually coloring each task. Any suggestions?

    Reply
  4. 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?

    Reply
    • 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?

      Reply
      • 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

        Reply

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster