There are two existing tutorials that cover the bulk of this chart’s construction:
Quick Gantt Chart:
This is a simple chart to construct because it contains only a single view. You can see when the task starts, how long it takes to complete, and when the task ends.
Fully Featured Gantt Chart
This chart is a bit more sophisticated, showing not only the Actuals (start, duration, end), but also the percentage completed. The Actuals are represented as a thick gray bar while the percent completed are represented as a thinner green bar on top of the Actuals.
An additional feature is the ability to switch the chart from displaying the Actuals to the Plan. The Plan is often referred to as the Baseline; what you thought was going to happen (planned to happen) as opposed to what really happened.
This allows us to see two views in a single chart. The single chart can be made larger, easier to read, and have greater detail than two smaller charts, while retaining full functionality and messaging.
The goal is to have both charts represented as a single chart while remaining easily understood without sacrificing content.
Begin with the Plan and the Actuals
We will begin with the data that was created in the first two tutorials of the Gantt Chart.
Our columns are as follows:
Column A – Task
This is the task’s name.
Plan
The Plan is a listing of our projections; what we THINK is going to happen during project execution.
Column B – Start Date
This is the start date of each task.
Column C – # Working Days
This is the number of days to complete the task.
Column D – End Date
This is a formula that calculates the end date based on the Start Date (B) and the and the # Working Days (C). An example of this would be to calculate the end date of the first task:
=IF(B5="", "", WORKDAY(B5, C5, $P$5:$P$19))If you are unclear as to how the WORKDAY function operates, consult the first two referenced Gantt Chart videos.
Actual
The Actual is what really happened during project execution. This is where Reality steps in and starts to influence our projections.
Column F – Start Date
This is the day the task actually started.
Column G – # Working Days
This is the actual number of days it took to complete the task.
Column H – End Date
This is a formula that calculates the end date based on the Actual Start Date (F) and the and the Actual # Working Days (G). An example of this for the first task would be:
=IF(F5="", "", WORKDAY(F5, G5, $P$5:$P$19))Column I – % Completion
This is a manually entered percentage that represents the amount of work accomplished for the task. Ideally, all tasks will be set to 100% when the project is finished.
Creating the Gantt Chart
Now we will create a Gantt Chart that displays all the above information in a single view. Additionally, we want to see a line in the Gantt Chart that indicates the current date.
Preparing the Data
We will start by creating a data preparation table in columns K through P.
The data preparation table is an effective approach to rework the data, which may not be in an easily-chartable format and list the data in a structure that is better understood by the chart engine.
Each of the following descriptions will be accompanied by a sample formula/value for the first task. Each column will have the first task’s formula/data repeated downward for the remaining tasks.
Column K – Task
We want a repeat of the Tasks in Column A. This column is a direct reference to the tasks in Column A.
=A5Column L – Start Actual Date
We want a repeat of the Start Dates in Column F. This column is a direct reference to the dates in Column F.
=F5Column M – # Days
This is a calculation to determine the difference between the End Date (H) and the Start Date (F).
=H5-F5Column N – # Days Completed
This is a calculation that takes the # Days (M) and multiplies it by the % Completion (I).
=M5*I5Column O – Start Plan Date
We want a repeat of the Start Dates in Column B. This column is a direct reference to the dates in Column B.
=A5Column P – # Days
This is a calculation that takes the End Date (D) and subtracts the Start Date (B).
=D5-B5The Trouble with Charts
As many people have discovered, it is often not easy to create a chart, and have it look perfect from the moment of inception.
If we select the data in columns K through M and insert a common stacked bar chart, the results are less than stellar.
One of the main issues is that Excel is plotting the dates across the vertical axis. We want our dates to be above the bars in service of the X axis.
There are several techniques we could use to get the dates as part of the stacked bars instead of as axis labels. One technique is to start with a blank chart and manually bring each column of information into the chart one at a time.
Because that can be time-consuming and tricky for some people, we will use a sneaky trick.
Begin by deleting the original chart; it wasn’t helping our cause anyways.
Instead of feeding dates from Column L to the chart, we will convert the Column L dates to numbers.
Select the dates in Column L and apply the General number style to the dates.
Now when we highlight the data in columns K through M and insert a stacked bar chart, the numbers from Columns L and M are stacked atop one another.
Once the chart is in place, we can return to the numbers in Column L and convert them back to dates (using the Short Date style.)
Our resultant bar chart shows the start date, plotted as a long blue bar, and the number of days, plotted as short orange bars.
The orange bars are the important part of the chart. The blue bars need to exist to properly position the orange bars, but the blue bars will be hidden in the final version of the chart.
Adjusting the Chart Visuals
The first adjustment will be to flip the order of the items on the y-axis. We want the task “Sign Off” to be on the bottom of the y-axis.
With the y-axis selected, press CTRL-1 (or double-click on the y-axis) to bring up the Format Axis tools.
Place a check in the option labeled “Categories in reverse order”.
The next adjustment will be to hide the blue bars that represent the dates.
Select one of the blue bars and set the Fill option to “No Fill” and the Border option to “No Line”.
This results in a chart where the first orange bar begins in the middle of the chart.
To have the first orange bar begin just to the left side of the plot area, select the dates along the X axis (top of chart). Under the Axis Options, set the Minimum to 43450. This is a number you may need to experiment with to get the beginning of the chart to start in your desired position.
- Remove the Legend.
- Remove the vertical gridlines.
- Set the color of the orange bars to a medium gray.
- Adjust the width of the chart to space the dates accordingly.
We now have a Gantt Chart displaying the actual number of days for each task.
Adding Number of Days Completed
We will use a technique explained in the previously referenced videos (see below) which uses Error Bars.
We need to select the “invisible” bars to the left of the gray Actuals.
With the selected bars, click the Chart Elements button (upper-left corner of the chart; looks like a plus sign in a box) and place a checkmark in the Error Bars option.
This displays the Error Bars, but we need to customize them for our chart. Click once on any Error Bar to display the Format Error Bar options. Set the options for the Horizontal Error Bar to the following:
- Direction = Plus
- End Style = No Cap
- Error Amount = Custom – Specify Value – Positive Error Value – N5:N13 (these are the cell for the “# Days Completed” in Column N).
Now we will customize the Error Bars to make them more visible.
With the Error Bars selected, change the Line Color to green, and set the Width to 6pt.
Our chart now displays the percentage completed.
Time to Add the Plan (Baseline) Information
To reduce confusion, we will add a thinner line below the Actual gray bars.
What makes this tricky is that we need to plat a scatter plot on top of the existing bar chart. Scatter plot charts need values for both the X and the Y axis.
We already have the X axis information in the form of dates. We need the Y axis.
Something to be aware of when using Excel charts is that the default position for zero on the Y axis is at the bottom of the chart. Each item on the Y axis is plotted at a whole number interval. In our case, 1 up to 9.
If “Sign-off” is plotted on the 1 value on the Y axis, we want a bar that is below the 1 value on the Y axis. We will use 0.2 as our Y axis reference for the first bar.
Column Q will hold our values for the Y axis position. To make the calculation easier, we will enter the 0.2 reference value in cell Q13, then create a formula in cell Q12 to add 1 to the value defined in cell Q13.
=Q13+1If we fill the formula in cell Q12 UP to cell Q5, we will have Y axis plot positions for the remaining tasks.
By adjusting the value of the fraction in cell Q13, we can experiment with the up/down positioning of the Plan bars.
We now have our Plan scatter chart data at 1-unit intervals for the Y axis values. The X axis values will be the Start Plan Dates in Column O.
Adding the Plan Scatter Chart to the Bar Chart
A small issue we will have when integrating the new scatter chart data is that Excel does not know we want a scatter chart; it thinks we are adding new bars to the stacked column chart. We’ll start with that perspective, just to get the ball rolling, but then make the necessary adjustments to get the chart we want for our Actuals.
Right-click on the chart and select “Select Data…”
In the Legend Entries (left), click Add. In the Edit Series dialog box, set the Series Name to cell O4 and the Series Values to cells Q5 through Q13.
Because Excel has assumed we are adding more bars to our existing stacked bar chart, we need to change the newly added bars to a Scatter chart.
Right-click on one of the newly added gray bars and select “Change Series Chart Type…”
Remaining in the Combo category, set the Start Plan Date chart type to Scatter.
This will automatically set the scatter chart on the secondary Y axis with a range of 0 (zero) though 9.
But where are the scatter chart dots? Excel was only provided 1 axis of information when it created the additional bar on the stacked bar chart. When we converted the new bar into a scatter chart, we ended up with a chart that is lacking in necessary plotting information; the X axis values.
To add the X axis values to the scatter chart, right-click on the chart and select “Select Data…”
Select the Start Plan Date entry and click Edit.
For the “Series X values:” option, select the Start Plan Dates in cells O5 through O13.
We now have dots below our bars. The dots represent the Start Plan Dates. We don’t want to see the dots for the dates, we want to see bars for the number of days planned. These are the values in Column P.
We will again take advantage of the Error Bar trick to build the new bars.
As before, with the dots selected, click the Chart Elements button and place a checkmark in the Error Bars option.
This displays the Error Bars, but we need to customize them for our chart.
Scatter chart error bars go in 2 directions. We don’t need the vertical error bars, so click any Vertical Error Bar and press Delete.
With the remaining Error Bars selected, in the Format Error Bar options, set the options for the Horizontal Error Bar to the following:
- Direction = Plus
- End Style = No Cap
- Error Amount = Custom – Specify Value – Positive Error Value – P5:P13 (these are the cell for the “# Days” in Column P).
Now we will customize the Error Bars to make them more visible.
With the Error Bars selected, change the Line Color to light blue, and set the Width to 3pt.
We no longer need the original dot markers denoting the start dates. Select a dot and set the Marker – Marker Options to None.
While we’re at it, select the Secondary Y Axis (0-9) and delete it from the chart.
Our chart now displays the Actuals.
We can see the following information in our Gantt Chart:
- Plan – Gray bars
- Percentage complete – Green bars
- Actuals – Blue bars
Adding the “Today” Indicator
To plot a vertical line in our chart to indicate today’s date, we will create additional information in our data preparation table.
Starting in cell R5 enter today’s date. For this example, we will hard-set the date to 4/4/2019.
TIP: To get an automatically updating date, enter the formula =TODAY() in cell R5.
In cell R6, enter the following formula to point to the cell holding the hard-set date (R5).
=R5Fill the formula down from cell R6 down to cell R13.
We will add a new scatter chart to the existing bar chart. Right-click the chart and select “Select Data…”
Add a new Legend Entry with the following values:
- Series Name – R4
- Series X Values – R5:R13
- Series Y Values – Q5:Q13
We can’t see our dots in the chart.
This is usually due to the dots having a white fill color.
To select the “invisible” dots, utilize the drop-down list at the top of the Format Plot Area panel and select “Series “Current day””.
We can now see where the dots have been plotted.
We don’t want to make the dots visible, but we do want to see a line that connects the dos together.
Set the Line options to the following:
- Solid Line
- Color – Medium blue
- Transparency – 40%
- Begin Arrow Type – Arrow
To make the new line more informative, we will add a label to the top of the arrow.
Select the top of the arrow to select the entice set of plot points on the line. Now select the top of the arrow a second time to select only the top data point.
Right-click the single data point and select “Add Data Label”.
Click once to select the data label, then click AGAIN to select the data label. You should see small blue dots handles on the first click and larger white dot handles on the second click. This is us focusing our customization to only the data label for the top plot point.
Press CTRL-1 to launch the Format Data Labels panel and place a check in the Series Name checkbox and remove the check form the Y Value checkbox. Set the Label Position to Above.
Add a solid border to the label that is the same color as the line with a white fill that is 50% transparent.
Set the font color of the label “Current Day” to the same color light blue as the arrow and border.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
