What is the Timeline Chart in Excel?
Excel Timeline Chart is also called a Milestone chart in project management terms. This is the chart used to track all the stages of a project in a visual way. As a project manager deliver the project to the end-user as committed before the start of the project.
So in order to help out a project manager to visualize things in excel, we can create a Timeline Chart.
How to Create a Timeline (Milestone) Chart in Excel?
Below is an example of a timeline (milestone) chart in excelChart In ExcelIn Excel, a graph or chart lets us visualize information we've gathered from our data. It allows us to visualize data in easy-to-understand pictorial ways. The following components are required to create charts or graphs in Excel: 1 - Numerical Data, 2 - Data Headings, and 3 - Data in Proper Order..
The first thing to create a timeline chart is to get the data ready. You need a task list with an estimated time of the task. Below is the data I have prepared for this example of a timeline chart.
Let’s create a timeline chart in excel by following the below steps.
- First thing we need to do is to create a modified data tableData TableA data table in excel is a type of what-if analysis tool that allows you to compare variables and see how they impact the result and overall data. It can be found under the data tab in the what-if analysis section.. Insert a helper column and insert the number 10 with one positive number and one negative number.
- Select the data and go to the INSERT tab and insert excel Line ChartExcel Line ChartLine Graphs/Charts in Excels are visuals to track trends or show changes over a given period & they are pretty helpful for forecasting data. They may include 1 line for a single data set or multiple lines to compare different data sets. with Markers.
- Now, Right-click on the chart and choose the “Select Data” option.
- In the below window, remove the legend entries.
- Now, click on the “Add” button.
- In the below window for “Series Name,” enter “Date,” and for “Series Values,” select task list.
- Now click on “OK.” In the same window, click on the “Edit” option on the right-hand side, i.e., “Horizontal Axis Labels.”
- In the below selection window, choose date values.
- Click on, OK. Now in the same window again, click on the “Add” button.
- Now, in the next window, enter “Helper” for Series Name, and for “Series Values,” choose helper column values.
- After all this, we need to do some formatting touches. Now click on the line chart and press the shortcut key “Ctrl + 1” to open the “Format Data Series” option to the right of the chart.
- Go to the “Fill & Line” option, and under the “Line” option, select the “No Line” option.
- Now, with the same selection, click on the Design tab. Under the Design tab, go to Add Chart Elements.
- Under Add Chart Elements, go to Error Bars in excel and choose “More Error Bars Options.”
- As soon as you click on “More Error Bars Options,” it will open up a formatting option to the right side again.
- Click on “Error Bar Options,” select direction as “Minus,” and Error amount percentage as 100%.
- Now we have a chart like this.
- Now select the horizontal flat line chart and make this as “Secondary Axis.”
- Now delete the Secondary AxisSecondary AxisThe secondary axis is the other axis that is used to denote different data sets that cannot be displayed on a single axis. The primary axis, for example, depicts time, whereas the secondary axis displays production. vertical values.
- Next, “Add Data Labels” to the chart.
- By selecting data labels to press Ctrl + 1 to open the formatting window. From the formatting window, select “Category Name.”
- Select the chart and click on the “Select Data” option. In the below window, choose “Helper” and click on the “Edit” option.
- For “Axis Labels,” choose an Estimated Date.
- Select “Dot” data labels and make this data label as Values from cells choose values from A2 to A9.
- Now select the “Dot” and choose “Marker Option” to make this as delta shapes marker with black color.
Now our Mile Stone chart is ready to rock.
Things to Remember
- Timeline chart (Milestone) isn’t the built-in excel chart, so we need to follow all the above steps to create our own chart.
- This is the simple visualization technique to track all your project schedules.
- It seems difficult because of formatting techniques, but the more time you spend on this, the more comfortable you will be.
This has been a guide to Timeline Chart in excel. Here we discuss how to create a Timeline (Milestone) Chart in Excel with the help of an example and downloadable excel template. You can learn more from the following articles –