A vertical timeline chart using a scatter chart in Excel is a clean way to show the milestones of a project from top to bottom. Dates run down a central axis, and each milestone sits on a connector that points left or right, with the date and name in a label. It is fully dynamic: put the data in an Excel table, add a new milestone, and the chart picks it up automatically. In this tutorial you will build it step by step with one scatter series, error bars and data labels taken from cells.
Watch the Video Tutorial
In the video, PK turns a milestone table into a scatter chart, draws the connectors with minus error bars, adds date and milestone labels from a helper column, reverses the date axis and fixes its start and end dates.
What You Will Build
- A milestone table with Date, Milestone, Position and Data Label columns.
- A central date axis running from top to bottom with cross tick marks.
- Connectors made from error bars, with a diamond at the milestone end, under each label.
- Callout labels that show the date on one line and the milestone on the next.
- A dynamic chart: change a date or a name, or add a row, and the timeline updates.
Part 1: Prepare the Timeline Data
Step 1: Enter the milestones
Type the project milestones with their dates. The example in the video uses 14 milestones from June to September 2019:
| Date | Milestone | Position |
|---|---|---|
| 2-Jun-19 | Start | 50 |
| 10-Jun-19 | Milestone-1 | -50 |
| 16-Jun-19 | Milestone-2 | 80 |
| 23-Jun-19 | Milestone-3 | -80 |
| 1-Jul-19 | Milestone-4 | 50 |
| … | Milestone-5 to Milestone-12 | … |
| 10-Sep-19 | Completed | -50 |
Put Date in A1, Milestone in B1 and Position in C1, with the data from row 2 down to row 15.
Step 2: Add the Position column
The Position column decides where each milestone appears. A positive number puts it to the right of the date axis, a negative number to the left, and the size of the number sets how far out it goes. PK uses the pattern 50, -50, 80, -80 and repeats it down the list. This alternates the labels left and right at two distances, so neighbouring labels don’t overlap.
Step 3: Convert the range to a table
Select the data and press Ctrl + L (or Ctrl + T), then click OK. Because the chart will point to table columns, any milestone you add at the bottom of the table shows up on the chart automatically. You never need to change the chart range.
Step 4: Create the label text with a formula
Add a fourth column, Data Label, and enter this formula in D2:
=TEXT([@Date],"D-MMM-YY")&CHAR(10)&[@Milestone]Press Enter and the table fills it down for every row.
How the formula works
TEXT([@Date],"D-MMM-YY")turns the date into text such as 2-Jun-19. Without TEXT, joining a date to text gives the serial number (43618) instead.CHAR(10)is the line-break character, so the milestone name goes on a second line in the chart label.[@Milestone]is the milestone in the same row of the table.- In the cell it looks like 2-Jun-19Start, because the cell is not set to wrap text. In the chart label the line break works.
Part 2: Build the Vertical Timeline Chart Using a Scatter Chart
Step 5: Insert a scatter chart with one series
Turn off the sheet gridlines with View > Gridlines. Click an empty cell and go to Insert > Scatter (the markers-only type). Right-click the blank chart, choose Select Data > Add and set:
- Series name: leave it blank.
- Series X values: the Position column, C2:C15.
- Series Y values: the Date column, A2:A15.
Click OK. This is the key idea: the dates go on the vertical axis and the positions on the horizontal one, so the timeline runs from top to bottom. Make the chart taller than it is wide.
Step 6: Format the date axis
Delete the chart gridlines. Right-click the vertical axis, choose Format Axis, open Tick Marks and set both Major type and Minor type to Cross. On the Home tab make the axis labels bold, a bit bigger and black, and on the Format tab set Shape Outline to black. Then select the horizontal axis and press Delete. The vertical axis stays in the middle of the chart, where the positions cross zero.
Part 3: Draw the Connectors with Error Bars
Step 7: Add error bars and keep only the horizontal ones
Click the chart, click the Chart Elements (+) button and tick Error Bars. Click any vertical error bar to select them all and press Delete.
Step 8: Make each bar run back to the axis
Right-click a horizontal error bar and choose Format Error Bars. Set:
- Direction: Minus
- End Style: No Cap
- Error Amount: Percentage,
100%
A minus error bar of 100% runs from the point back by its full X value, which is exactly to zero. So a milestone at 80 gets a bar from 80 to the axis, and one at -50 gets a bar from -50 to the axis.
Step 9: Style the connector line
Go to Fill & Line > Line. Choose Solid line, a light blue colour and a width of 2 pt. Set Compound type to double. Then set Begin Arrow type to the diamond arrow and pick a larger Begin Arrow size, so each connector has a diamond marker.
Step 10: Hide the scatter markers
Click the series markers, go to Fill & Line > Marker > Marker Options and choose None. Only the connectors remain.
Part 4: Add Labels and Finish the Axis
Step 11: Show the date and milestone in the labels
Right-click the series and choose Add Data Labels. Right-click a label, choose Format Data Labels and:
- Tick Value From Cells and select the Data Label column, D2:D15.
- Untick Y Value and Show Leader Lines.
- Set Label Position to Above.
Every label now shows the date and, on the next line, the milestone name.
Step 12: Put the first milestone at the top
By default the latest date is at the top, so Completed is first. Right-click the vertical axis, choose Format Axis and tick Values in reverse order. Now Start is at the top and the project flows downward.
Step 13: Style the labels as callouts
Select the data labels, right-click and choose Change Data Label Shapes. PK picks a callout with an arrow pointing down at the connector. Give the labels a colour with a style from Format > Shape Styles and make the text bold on the Home tab.
Step 14: Fix the first and last date of the axis
Excel picks the axis bounds automatically, which can leave odd start and end dates. To fix them, type the dates you want in two spare cells, for example 1-Jun-2019 and 30-Sep-2019, and change their format to General (PK uses Alt, O, E). They show 43617 and 43738. In Format Axis > Bounds type 43617 as the Minimum and 43738 as the Maximum.
Axis bounds are numbers, and Excel stores each date as a serial number: the count of days since 1 January 1900. That is why you enter 43617 instead of the date itself. Add a chart title if you want one, and the timeline is ready.
Version Note
This section is an addition and is not in the original video. Value From Cells for data labels arrived in Excel 2013, so this method needs Excel 2013 or later, including Microsoft 365. In Excel 2010 and older, you would have to type or link each label by hand.
Common Errors and Fixes
The timeline runs left to right instead of top to bottom
The X and Y values are swapped. In Select Data, edit the series so X is the Position column and Y is the Date column.
The connectors don’t reach the axis
The error bar amount is not 100% or the direction is Both or Plus. Set Direction to Minus and Percentage to 100%.
Labels show only a number or only the date
Value From Cells is not ticked, or Y Value is still ticked. Fix the label options in Step 11.
A new milestone does not appear on the chart
The data is not a table, or the new row was typed below the table instead of inside it. Convert the range with Ctrl + L and make sure the row joins the table.
Tips
- Use more than two distances in the Position column (for example 30, 60 and 90) when your milestones are close together.
- Keep the milestone names short, so the callouts stay small.
- Update the axis bounds when the project dates change, or the new milestones fall outside the chart.
- Microsoft explains date format codes in the TEXT function article on Microsoft Support.
Want It Ready-Made?
If you want the finished chart ready to use, get the Vertical Timeline Chart using Scatter Chart in Excel template on NextGenTemplates.com, or browse the Excel project management templates. To learn charts, infographics and dashboards in depth, see the Excel Pivot Tables and Dashboards course.
Related Tutorials
- Horizontal Timeline Chart using Scatter Chart in Excel (part 2 of this video)
- Project Timeline Dashboard in Excel
- Road and Flag Infographics Chart in Excel
- Candle Infographic Chart in Excel
- Waffle Charts in Excel Without Macro
Frequently Asked Questions
Why use a scatter chart for a vertical timeline?
A scatter chart lets you put the dates on the vertical axis and a position on the horizontal axis. That makes the timeline run from top to bottom, with milestones placed left or right of the date axis.
How are the connector lines drawn?
They are horizontal error bars set to Minus, No Cap and 100 percent. Each bar runs from the milestone point back to zero, where the date axis sits.
How do I show both the date and the milestone in a label?
Build the label text in a helper column with TEXT, CHAR(10) for a line break and the milestone name, as shown in Step 4. Then use Value From Cells in the data label options.
Will the chart update when I add a milestone?
Yes, as long as the data is an Excel table and the new row is added inside the table. The label column fills automatically too.
How do I show the first milestone at the top?
Format the vertical axis and tick Values in reverse order. The earliest date then appears at the top of the chart.
About the Author
PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel.
Conclusion
A vertical timeline chart using a scatter chart in Excel needs just one series: dates on the vertical axis, a Position column for left and right placement, minus error bars for the connectors and labels taken from a TEXT and CHAR(10) helper column. Reverse the axis, fix its bounds, and you have a dynamic milestone chart for any project dashboard or presentation. Next, try the horizontal version from part 2.
