In this article, the step by step guide on how to create a timeline in Excel is provided with pictures for easy understanding.
Also, a free Microsoft Excel timeline template is available to download and use for any type of project with simple editing and customization steps.
So, read the full article to learn how to make a timeline in Excel free template with tips of formatting for creating stunning visual effects.
Related Article: Steps on How to Make a Timeline in PowerPoint & Free Template
What is a Project Timeline in Excel?
A timeline is a presentation of sequential events along a line that helps the viewer to understand the time-based relationship between the events quickly.
Previously, the chronological tabular timelines were more common that contain more text than graphical figures.
However, now infographic based timelines are very more useful and appealing to the audience. It contains text along with graphical images for a stunning presentation.
There were a lot of tools available for creating project timelines before the advancement of computer technology, such as magnetic blocks and Legos TM.
So, now mostly timelines are created using Excel, Powerpoint, Google Sheets, Word, and Project Management or Gantt chart software.
Related Article: Steps on How to Make Gantt Chart in Excel | Free Template
Free Download Excel Timeline Template XLS File
I have prepared a template to create the timeline in excel a matter of few clicks.
So, simply free download the pre-made excel timeline template file and customize the milestones description, start date, and timeline height points in the project schedule table.
Thus, it will automatically make the timeline for your project with little to no effort.
How to Create a Timeline in Excel?
There are a lot of tools to create a timeline. However, Microsoft Excel is a handy and universal tool due to its advanced graphing feature of creating Smart Arts such as list, process, matrix, cycles, and hierarchy.
Also, it has an advanced feature of creating timeline using charts. Thus, a scattered chart tool is useful here to create project timeline using excel.
Moreover, it requires minimum effort to make a timeline using excel template for the timeline. Thus, many project management companies prefer to use a free template in Excel, Visio, PowerPoint, or google sheets for timeline making.
Furthermore, the presentation of the project timeline to clients and executives require easy steps to make a timeline in MS Excel, Word, or PowerPoint.
Although, many Excel timeline add-ins and extensions are available to make stunning project timelines quickly. However, this article will provide a guide to use scattered charts for making a graphic timeline in excel template.
So, the step by step complete guide for creating a timeline in Excel XLS using Scattered Chart is given below.
1. Create a Table of Required Data for Timeline in Excel
The project timeline creation using Excel scattered chart requires tasks/milestones description, start date, and timeline height points.
The timeline height points are hypothetical.
The purpose of these points is to set the different height to milestone lines in the final chart. So, no adjacent points should be on the same height. Otherwise, their titles may overlap.
Thus, set any numbers to milestones/tasks just keeping in view that there must be a difference of at least 2-3 points in each adjacent activity.
Also, basic tools of font styles and boarder are helpful to create project data tables in Excel 2007, 2010, 2013, 2016, & 2019.
Related Article: Free Project Schedule Template | Gantt Charts and Timelines
2. Insert a Blank Scatter Chart and Add Data to Create Excel Timeline
- Click in any blank cell and navigate to Insert > Charts > Scatter and click on the scatter chart to insert a blank chart.
- So, right-click on the blank chart area and choose the “Select Date” option from the pop-up list of properties.
- A “Select Data Source” window will pop-up. Then click on the “Add” button under Legend Entries (Series) head to insert the data.
- Now click on the “Series X values” field and choose the start dates of all the milestones. So, just click on the start date of the first milestone, hold on the shift button, and keep on tapping the Downward Arrow button until reaching the start date of the last milestone.
- Repeat the above step to choose the “Series Y values” from the timeline height points column of the project schedule data table.
- Finally, click on the OK button to create the Legend Entry Series. So, it will return you to the Edit Series pop-up. However, it will create a scattered chart view of scattered dots of selected data on the plot area.
Related Article: Free 2 Week Look Ahead Schedule Template XLS | Construction Schedule
3. Steps for Formatting of Excel Timeline
All the above steps help to create a standard scattered chart using project schedule data. Now, a few formatting edits are required to make it looks like a timeline in Excel.
Disable Y-Axis Label and Enable Data Labels and Error Bars
- Click anywhere on the scattered chart and edit options will appear on the right top side of the chart area.
- Now, click on the “+” sign to open the formatting dropdown menu and navigate to the second menu of Axes and unselect/untick the “Primary Vertical” setting.
- Further, go back to the main drop-down and click on “Chart Title” and “Gridlines” field to untick it.
- Moreover, click on the “Date Labels” and “Error Bars” fields to enable these features.
Error Bars Formatting
The error bars of the scatter chart will work as dropdown lines from the data point to the horizontal axis to make it look like a timeline.
- So, right-click on any horizontal error bar and choose the “Format Error Bars” option from the dropdown list.
- Now, navigate to Paint Bucket Icon > Line from the format error bar window appear on the right side. Further click on the “No line” option to hide the horizontal error bars.
- Also, click on the dropdown error adjacent to “Error Bar Options” heading at the top to open the dropdown list. So, choose the “Series 1 Y Error Bars” from the dropdown.
- Further, navigate to Histogram Icon > Vertical Bar Lines and set the direction of vertical lines as “Minus” and error amount as “100% Percent”.
Related Article: Top 7+ KPI Dashboard Excel Template
Data Labels Formatting
The following steps will help to make to show useful information such as milestones description and start date.
- So, left-click on any data label while the format window is open or right-click and choose “Format data labels” from the options.
- Now, navigate to Histogram Icon > Label Options and left-click on the “Value from Cells” option to enable it.
- Further, select the milestone description range from the project schedule table in the “Data Label Range” pop-up and click on the OK button.
- Moreover, disable the “Y value” option and enable the “X value” option of the labels options section. Also, set the separator to “New Line”.
- Finally, a project timeline created using scattered chart tool of MS Excel is complete.
Change Color of Data Points and Error Bars
- Right-click on any data point and choose the “Format Data Series” option.
- So, navigate to Paint Bucket Icon > Marker > Fill and enable the “Vary colors by point” option. It will set a different color on each data point.
- Now left-click on any error bar and navigate to Paint Bucket Icon > Line.
- So, change the “Color” of Error lines and set the “Transparency” to add a graphical effect on error lines.
Related Article: 12+ Free Project Tracking Templates for Excel | Project Trackers
Remove Empty Space on the Timeline in Excel
There is a lot of space on the left and right of the Timeline in the plot area. So, you have to adjust the minimum and maximum bounds of the horizontal axis from Format Axis options.
- Right-click on the horizontal Start Date values axis and choose the “Format Axis” option.
- Navigate to Histogram Icon > Axis Options and adjust bounds minimum and maximum settings.
- So, slightly adjust (Increase) the “Minimum Bounds’ value to remove the extra white space from the left side of the timeline area in Excel.
- Also, slightly adjust (Decrease) the “Maximum Bounds” value to remove the extra white space from the right side of the Excel timeline.
Add the Chart Title to Excel Timeline
- Click anywhere on the chart area, the “+” will appear on the right top corner of the chart area.
- Then click on the “+” sign to show the chart elements. So, mark the “Chart Title” option to enable the chart title.
- Click on the “Chart Title” text and type the title. Finally, change the font parameters such as font, style, and color.
Change the Chart Design
- Navigate to Chart Design > Chart Styles and select any design from the available list.
- Make sure to perform this step before formatting the scattered chart as many styles reform the scattered chart to default settings.
Related Article: Best Free Production Schedule Template Excel | Production Scheduling
Frequently Asked Questions
There are many questions in the FAQ list of Gantt Charts. However, the basic ones are:
Excel 2007, 2013, 2016, or 2019 is very beneficial to create a timeline using a SmartArt or Scattered Chart feature.
So, just insert the scattered chart and enter the milestones start date and height points data. Further, enable and format the vertical data bars to make it looks like a timeline.
The following are the four simple steps to create a Gantt chart in Excel 2007 to 2019.
1. Create a Project Schedule Data Table
2. Set up an Empty Scattered Chart
3. Add values of Milestones Description, Start Date, and Height Points.
4. Format the Scattered Chart to look like a Gantt Chart. Enable error bars, hide horizontal bars, and extend the vertical ones down to the x-axis.
The following simple steps are useful to make a timeline in Word and PowerPoint.
1. Insert a Basic Timeline from the process section of the SmartArt feature by doing to insert from the top ribbon.
2. Add the text to data points.
3. Change color and shape of starting and ending milestone.
4. Format the basic timeline to make it visually awesome.
Finally, it was all about the “How to Create a Timeline in Excel”. Please do share your feedback in the comments section and SUBSCRIBE us to learn more about Project Management.