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.
Column 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.
Column M – # Days
This is a calculation to determine the difference between the End Date (H) and the Start Date (F).
Column N – # Days Completed
This is a calculation that takes the # Days (M) and multiplies it by the % Completion (I).
Column 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.
Column P – # Days
This is a calculation that takes the End Date (D) and subtracts the Start Date (B).
The 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.