How to Create A Timeline In Excel

You can transform a scatter plot into a timeline in Excel

Image of a project timeline

 NicoElNino/Getty Images

If you're in the process of planning a project or even just logging events, learning how to create a timeline in Excel can be a huge help. A timeline can help you track everything from important milestones, to smaller, more detailed events during the timeline you're trying to track.

Depending on the version of Excel you're using, there is both an easy way and a slightly more involved way to create a timeline in Excel.

The information in this tutorial applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Mac.

Creating an Excel Timeline Smart Graphic

Excel has a premade graphic you can use to create a timeline in Excel. This graphic is part of Excel's SmartArt collection, and is very easy to use.

The SmartArt timeline is a simple method of creating a general timeline that you can insert anywhere into an Excel sheet. However, it doesn't allow you to dynamically label each point using a range in your worksheet. You need to manually enter the label for each timeline point. Because of this, SmartArt timelines are best for shorter timelines.

This method only works with Excel 2019 or Excel 365.

  1. To create a SmartArt timeline, select Insert from the menu, and in the Illustrations group select SmartArt.

    Screenshot of selecting SmartArt in Excel
  2. In the Choose a SmartArt Graphic window, select Process from the left pane. You'll see two timeline options; Basic Timeline and Circle Accent Timeline. The Basic Timeline is best for a traditional one line timeline that most people are familiar with. The Circle Accent timeline displays circles for each task organized in a row. This option offers a more unique style for your timeline. Choose one of these options and then select OK.

    Screenshot of selecting a Basic Timeline from Excel SmartGraphic
  3. In the text box that says Type your text here, you can type a label for each point in the timeline. This is a manual process, so it's best for shorter timelines without too many elements throughout.

    Screenshot of creating a timeline in Excel
  4. When you select any cell in the spreadsheet, the entry pane will disappear. You can edit the timeline at any time in the future with additional entries just by selecting it and repeating the process above.

    Screenshot of an Excel timeline in an Excel sheet

SmartArt timelines in Excel are perfect for creating very quick, small timelines that you can use to track simple projects or any plan that involves a limited number of events. However, if you're managing a larger project with a long list of tasks, or if you're using an older version of Excel, the Scatter Plot timeline described below may be a better option.

Create an Excel Timeline From a Scatter Plot

If you have an older version of Excel, you're not out of luck. There's an advanced method you can use to turn scatter plots into well formatted timelines.

The Scatter Plot in excel lets you plot dots in sequential order in a chart. Because of this, it makes an ideal platform for you to order items in a straight line, organized by date. By formatting the scatter plot properly, you can transform it into a useful timeline graphic that dynamically changes based on the tasks and dates in your original project spreadsheet.

This process takes a little more time than the timeline option above, but in the end you can better customize it to suit your purposes.

This method of creating a timeline from a scatter plot works if you're using any version of Excel newer than Excel 2007.

  1. Any project needs a good timeline, but before you can visualize a timeline, you need to create a spreadsheet that contains every step in the project as well as due dates. To get started, it's also a good idea to create a "Milestone" column and rate the importance of each milestone on a scale of 1 to 4. This scale may change later as a way to better visualize the timeline (see below).

    Screenshot of a timeline worksheet
  2. This method of creating a visual timeline involves transforming a Scatter Plot. So, to get started, highlight the entire table you've selected. Then, select the Insert menu and select Scatter Plot from the Charts group.

    Screenshot of selecting a scatter plot
  3. Next, select specific data for the timeline by right clicking the chart and choosing Select Data.

    Screenshot of selecting Scatter Plot data
  4. In the Legend Entries (Series) pane, select Edit.

    Screenshot of selecting scatter plot data source
  5. Place your cursor in the Select X values field, and then highlight the entire column containing your task due dates throughout the project. This will use the individual due dates for bullet points in the timeline.

  6. Next, select the Select Y values field, and highlight the entire column containing your task milestone rankings for each task item. These will define the height of each point in the timeline. Select OK when you're done.

    Screenshot of selecting scatter plot series
  7. At this point you have a pretty good timeline, however there's still additional formatting you need to do to make the timeline display dates and tasks more clearly.

    Screenshot of initial timeline scatter plot
  8. Select the + icon at the upper right corner of the chart to open the Chart Elements dialogue box. Deselect Chart Title and Gridlines to give the timeline a cleaner look.

    Screenshot of removing scatter plot formatting
  9. Next, select the arrow next to Axes, and deselect Primary Vertical to remove the vertical axis labels from the timeline. This transforms the timeline into just a horizontal line with dates, and individual tasks represented as dots with a height defined by your milestones value for that task.

    Screenshot of removing scatter plot primary vertical
  10. Still in Chart Elements, select Error Bars to enable cross error bars for each point. These bars will transform into the vertical lines for each task item in your timeline. But to create these vertical lines, you need to reformat how the error bars appear.

    Screenshot of selecting scatter plot error bars
  11. To do this, right click the bottom axis of the chart and select Format Axis.

    Screenshot of formatting scatter plot axis
  12. Select the dropdown arrow next to Axis Options and select the X Error Bars selection.

    Screenshot of setting X error bars
  13. In these options, select No line. This will remove the horizontal line from each of the timeline points, and just the vertical line will remain.

    Screenshot of setting error bars no line
  14. Next, you'll want the vertical line to only reach up to each timeline point, but no higher. To do this, you need to edit the Y Error Bars. Right click the bottom axis on the chart again and select Format Axis. Select Axis Options and select the Y Error Bars from the dropdown list.

    Screenshot of setting Y Error bars
  15. Select the bar chart icon, and change the Direction selection to Minus. Under Error Amount, select Percentage, and change the field to 100%. These changes will make the vertical line stop at the timeline point. It will also stretch the vertical "error line" from the axis all the way up to each point.

    Screenshot of Vertical Error Bar settings
  16. Now your timeline looks like the one below, with the timeline points spread across and placed by date, with a vertical line stretching from that task date all the way up to the timeline point itself.

    Screenshot of almost finished scatter plot timeline
  17. However, each timeline point isn't very descriptive. It just displays the timeline milestone value you've given each point. Instead, you'll want to label each point with the task name.

    Screenshot of setting series data labels
  18. To do this, go back to the Axis Options dropdown and select Data Labels.

    Screenshot of setting Data Label Range
  19. You'll see a Data Label Range box appear. Select the Data Label Range field and then select the range of cells with the task descriptions. Select OK to finish.

    Screenshot of embedded data labels
  20. Now, you'll see all of the task descriptions appear as the data labels for each point. As you can see, while this makes each point more descriptive, it also creates a bit of a clutter for your timeline.

    Screenshot of fixing milestone settings
  21. You start fixing this by modifying the milestone value for each task item. As you adjust the milestone, it'll increase or lower the height of that point in the timeline.

    Screenshot of adjusting milestones
  22. While this does a good job organizing the timeline, you may not be able to thoroughly spread the timeline points apart enough so that the timeline is clear. Open the Axis Options again, and select the bar chart icon. Under Bounds, adjust the Minimum and Maximum fields. Increasing Minimum will move the first milestone closer to the left edge of the timeline. Lowering Maximum will move the last milestone to the right edge of your timeline.

    Screenshot of adjusting minimum and maximum bounds
  23. Once you're done making all of these adjustments, your timeline should be well organized, with plenty of room for each task label. Now you have a timeline that clearly displays the overall project timeline and the date of each milestone from start to finish.

    Screenshot of a completed timeline in Excel
  24. As you can see, using the scatter plot takes a little more effort to design an informative timeline, but in the end the effort results in a well designed timeline that everyone will appreciate.