Project Milestone Chart Using Excel
March 7, 2021
Stock Data Analysis is no easy feat! Once you have a lot of historical stock data it’s hard to visualize the trend using technical analysis.
Thankfully Excel has a lot of stock charts to help you with that, and one of them is the Candlestick Chart!
A Candlestick Chart has a vertical line that indicates the range of low to high prices and a thicker column for the opening and closing prices:
Below is the data source that we are going to use in Excel:
You need a Date column which should be the first column.
Then this should be followed by a Open, High, Low, and Close column. This is the exact order that needs to be followed in order to create the Candlestick Chart.
STEP 1: Highlight your data of stock prices:
STEP 2: Go to Insert > Stock Charts > Open-High-Low-Close
STEP 3: Right click on your Legend and choose Delete as we do not need this.
STEP 4: Go to Chart Tools > Design and select the preferred design to make your chart more presentable!
And there you have it! Your own Candlestick Chart!
If you want to compare products or businesses Year over Year and have category names which are way too long, then the Clustered Bar chart is the one for you.
STEP 1: Select the table on where we want to create the chart on.
STEP 2: Go to Insert > Bar Chart > Clustered Bar.
Your Clustered Bar Chart is now ready:
Project Management is no easy feat! There are lots of moving parts, long hours and constant reporting.
It requires you to keep everybody on the same page and focused on the goals and timelines.
One of the key reporting tools needed is the project milestone or timeline chart. Luckily you can create a project milestone chart in Excel in just a few steps…well, 11 to be exact!
Below is the data source that we are going to use to create our timeline of milestones in Excel:
We have Dates and Milestones. The Height specifies how high we want the milestone to show up in the chart, whilst the Helper Column is used to create the timeline.
In this example I show you how easy it is to insert a Timeline of Milestones using Excel.
NB: This tutorial was made for Excel 2013 and Excel 2016 versions.
STEP 1: Highlight the following columns: Date, Height, and the Helper Column. You can select the 3 of them by holding the Ctrl button while highlighting them:
STEP 2: Go to Insert > Line Charts > Line with Markers
STEP 3: Right click on your chart template and choose Select Data.
STEP 4: Select the Helper Column and press the Up arrow to move it up in order and press OK.
STEP 5: Select the Blue Lines (Height) & Right Click & select Change Series Chart Type.
STEP 6: Under the All Charts tab, select the Combo chart.
From the Height drop down, select the Clustered Column chart type and “check” the Secondary Axis box.
From the Helper Column drop down, select the Line with Markers chart type and “uncheck” the Secondary Axis box.
Press OK to confirm.
The reason we are doing this is so that our heights would now show up as columns in our timeline.
The selections should look like this:
STEP 7: Select the blue column chart & right click & select Add Data Labels.
The magic is about to happen!
STEP 8: The height numbers will show up, however we do not need the height numbers to show up.
Select any one of the height numbers, right click and select Format Data Labels.
Under the Label Options (first icon from the right), make sure that only the Value From Cells is selected and the other boxes are deselected.
The Label Position should be Outside End.
Then highlight the Milestone values from the data source and press OK.
This will ensure that the Data Labels will be the Milestone text instead, which is what we want our timeline to show.
STEP 9: Now we will change the columns into thin lines. For this we will use the Error Bars.
Click anywhere on the chart template.
Go to Design > Add Chart Element > Error Bars > More Error Bar Options
Select Height. Click OK.
From the Format Error Bars pane on the right hand side of your workbook, make sure the Error Bar Options (first icon from the right) is selected and choose the:
Direction = Minus
End Style = Cap
Error Amount = Percentage at 100%.
This will ensure we have thin lines go all the way down the x-axis.
STEP 10: Now that we have the thin lines inside the column chart, let us remove the blue columns.
Right click on the blue column chart and select Format Data Series.
From the Format Data Series pane, select the Fill bucket (first icon from the left) and make sure that the following are selected:
Fill = No fill
Border = No line
STEP 11: And now for the finishing touches!
Let us delete the unnecessary labels in our chart to make it look nicer. Just click on each element and press the Delete key from your keyboard.
And there you have it! Your own Timeline of Milestones!
Feel free to change the data source so you can see how your cool new Timeline chart changes. Happy project managing using Excel!
March 7, 2021
August 28, 2020
December 21, 2017
June 21, 2017
April 24, 2015
October 6, 2014