A milestone chart is a visualization tool that can show scheduled events in a specific time. These significant events are called as milestones. Unfortunately, milestone charts are not natively supported by Excel. In this guide, we're going to show you how to create a milestone chart in Excel.
A milestone chart consists of upright lines on a time-based horizontal axis. Each upright line represents a milestone. Alternating the “milestone” lines is a common design preference. Let’s start with data preparation.
Chart data
A data for a milestone chart should include 3 columns:
- The friendly name for the milestone.
- Dates of milestones.
- Numeric values that visualize the data points.
The Values column contains positive and negative versions of the same number. Since these numbers will be marked as data points on the chart, the sign of the numbers indicates the position of the milestone lines. Positive numbers will be above the axis and negative ones below it.
Creating the milestone chart
Once your data is ready, select the Dates and Values columns and create a chart by using the chart icons from the Insert tab of the Ribbon. Create a standard chart with axes, like a line or a bar chart. In our example, we selected the bar chart.
After creating the chart, click on chart and add Error Bars by following Chart Design > Add Chart Element > Error Bars > More Error Bars Options. This path also will show the right pane for the error bars. The error bars will be the milestone lines.
Set the Direction to Minus and Error Amount to Percentage. Also, change the percentage value to 100%.
Since we have the error bars, the actual chart drawings are no longer needed. Double-click on the chart itself or any of the data points on your chart to see their properties on the right side. Alternatively, you can use the dropdown in the properties pane to do this.
Once the value visualization is selected, hide them by using the No Fill and No Line options under the Fill & Line section. This action does not affect the error bars.
The next step is to show the milestone names as labels. You can add data labels by either using the plus button on the top-right corner of the chart or going to Chart Design > Add Chart Element > More Data Label Options on the Ribbon.
The chart will show the numeric values by default. You need to replace values with milestone names.
Open the properties pane if it is not visible already. Remove the check for Values and check Value From Cells instead. Select the milestone names and click OK.
Since the measures are not important in a milestone chart, y-axis and gridlines are not necessary. Remove them by unchecking the options in the Chart Elements menu.
If your data contains actual dates like in our example, you may see that some of the lines are not matching with the dates. This is because of Excel automatically recognizing the dates and generating the axes. If you are OK with equally divided dates, you can leave it as it is. Otherwise, double click on the horizontal axis to open options pane. Select Text axis in Axis Options.
If you haven't already done so, change the title of the chart. Double click on it and type in the name.
