There are two existing tutorials that cover the bulk of this Excel Gantt chart’s construction:

Easy Gantt Chart:

This chart is straightforward to create as it shows only a single view. You can easily see when a task begins, how long it will take, and when it ends.

For a step-by-step guide, click here to read the detailed article.

Gantt Chart with Progress Bars

This chart is designed to show more than just the start, duration, and end of tasks. It also displays how much of each task has been completed. We represent the actual progress with a thick gray bar and the percentage completed with a thinner green bar on top.

Additionally, you can switch views between the actual progress and the planned schedule. The planned schedule, or baseline, represents what you initially expected to happen, compared to the actual progress.

This feature allows you to compare both scenarios in one comprehensive chart, making it easier to read and more detailed than having two separate charts. This setup ensures that you can easily understand and analyze your project’s progress without missing any important details.

For more information and a detailed look at how this works, check out the details 👉 HERE.

Setting Up Your Gantt Chart: Plan vs. Actual

Let’s start by organizing our data, building upon what we covered in the first two Gantt Chart tutorials.

Here’s how we’ll structure our columns:

  • Column A – Task Name: List each task to be completed.
  • Column B – Planned Start Date: When you expect each task to start.
  • Column C – Planned Working Days: The projected number of days to complete each task.
  • Column D – Planned End Date: Use a formula to calculate the end date based on the start date and number of working days.

For example, for the first task, the formula is:

=IF(B5="", "", WORKDAY(B5, C5, $P$5:$P$19))

This formula uses the WORKDAY function to account for working days only, skipping weekends and holidays. If you’re not familiar with how the WORKDAY function works, you might want to review the first two Gantt chart tutorial videos.

  • Column F – Actual Start Date: When each task actually began.
  • Column G – Actual Working Days: The real number of days the task took to complete.
  • Column H – Actual End Date: Similar to the planned end date, this formula calculates when the task actually finished:
=IF(F5="", "", WORKDAY(F5, G5, $P$5:$P$19))
  • Column I – % Completion: Manually enter the percentage of the task completed. Ideally, this should reach 100% once the task is fully completed.

This structure will allow you to clearly compare your project’s planned timelines versus actual outcomes, helping you identify where adjustments may be needed as the project progresses.

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.

Data Preparation

To effectively display all relevant project information in a single Gantt chart view, we need to first organize our data properly. This involves setting up a data preparation table in columns K through P, which will help convert the data into a format that’s easier for the chart to process. Here’s how to structure this table:

  • Column K – Task: Simply replicate the task names from Column A:
=A5
  • Column L – Start Actual Date: Copy the actual start dates from Column F.
=F5
  • Column M – Total Actual Days: Calculate the number of days from the actual start to the end date.
=H5 - F5
  • Column N – Days Completed: Calculate how many days have been completed based on the percent of the task that’s finished.
=M5 * I5
  • Column O – Start Plan Date: Copy the planned start dates from Column B.
=A5
  • Column P – Total Planned Days: Calculate the number of days from the planned start to the end date.
=D5 - B5

The Challenge of Creating Perfect Charts

Many of us find that creating a chart in Excel that looks perfect from the start can be challenging.

For example, if you try to create a stacked bar chart using the data from columns K through M, you might not get the results you expect:

Often, Excel will mistakenly plot dates on the vertical axis, whereas we need them to appear as part of the bars, serving the X-axis.

To address this, you might think of adding each column to a blank chart by hand. But, that way is laborious and can be confusing. Instead, let’s use a simpler method:

  • Delete the original chart: It’s likely not displaying the data as you want, so start fresh.
  • Convert dates to numbers: Before creating a new chart, select the dates in Column L and change their format to ‘General’. This turns the dates into numbers, which Excel can handle more effectively in a chart.
  • Create a new stacked bar chart: Now, highlight the data in columns K through M and insert a stacked bar chart. The numeric data from Column L (previously dates) and the data from Column M will stack correctly.
  • Reformat the dates: Once the chart is created, change the numbers back to dates by selecting them and applying the ‘Short Date’ style.

With these steps, your chart will show the start dates as long blue bars and the duration as short orange bars on top.

The orange bars show task duration and are key to understanding the chart. The blue bars, only used to align the orange ones, will be removed in the final version. This step helps focus on the important data.

Formatting the Gantt Chart

To improve the readability of your Gantt chart, you’ll want to make several adjustments:

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.

  • Select the y-axis by clicking on it or pressing Ctrl + 1 to open the Format Axis tools.
  • Check the “Categories in reverse order” option. This will place the task “Sign Off” at the bottom of the y-axis, which is often a more logical presentation.

Hide Unnecessary Bars:

  • Click on one of the blue bars that represent the dates.
  • Set the Fill option to “No Fill” and the Border to “No Line.” This action hides the blue bars we don’t need for the Gantt chart.

Adjust the Start Position of the Orange Bars:

  • Click on the dates along the X-axis at the top of the chart.
  • Under the Axis Options, adjust the Minimum value to 43450. You might need to tweak this number a bit to align the start of the orange bars exactly where you want them on the chart.

Simplify the Chart:

  • Remove the legend to clean up the appearance.
  • Eliminate the vertical gridlines, which can clutter the visual presentation.

Change the Color and Size of the Bars:

  • Change the color of the orange bars to medium gray for a more subdued, professional look.
  • Adjust the chart’s width to space the dates well. Make sure each segment is clear and not crowded.

These adjustments will create a more visually appealing and easy-to-understand Gantt chart that displays the actual number of days for each task effectively.

Adding Progress Bars

To visually represent the number of days completed on our Gantt chart, we’ll use error bars, a technique discussed in our earlier videos.

Begin by selecting the invisible bars to the left of the gray ‘Actuals’ bars. These will serve as the base for our error bars.

Enable Error Bars:

  • Click the ‘Chart Elements’ button, with the plus sign located at the upper-right corner of the chart.
  • Check the ‘Error Bars’ option to make them visible on the chart.

Customize Error Bars:

  • Click on any error bar to bring up the ‘Format Error Bar’ options. Configure the following settings:
  • Direction: Choose ‘Plus’ to extend the bars rightward.
  • End Style: Select ‘No Cap’ for a cleaner appearance.
  • Error Amount: Set to ‘Custom – Specify Value’. Then, for the Positive Error Value, select the range N5:N13, which contains the data for ‘# Days Completed’ from Column N.

Make Error Bars More Visible:

With the error bars still selected, change the Line Color to green and set the Width to 6pt to ensure they are noticeable.

With these changes, our Gantt chart now shows the percent done for each task. Green error bars highlight the completed percent. They make it easy to see progress.

Adding Planned (Baseline) Data with a Scatter Plot

To clearly distinguish planned project data from actual progress, we will overlay a scatter plot on the existing bar chart. Here’s how to set this up effectively:

Prepare the Y-Axis Data

  • The Y-axis positions each task at whole number intervals (1 to 9 in our case).
  • To place the planned data just below the actual data, we’ll use fractional values.
  • In Column Q, set the Y-axis position for the baseline data. For the task at the bottom of the chart, enter a value slightly less than 1, say 0.2, in cell Q13 (which corresponds to the “Sign-off” task).
  • To set the Y-axis positions for the other tasks, create a formula in cell Q12 that adds 1 to the value below it:
=Q13+1
  • Then, fill this formula upward until cell Q5. This assigns a position just below each corresponding task for the planned data.

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.

Integrating a Plan Scatter Chart with 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 a “Today” Indicator to Your Gantt Chart

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.

💡To get an automatically updating date, use the formula =TODAY() in cell R5.

In cell R6, enter the following formula to point to the cell holding the hard-set date (R5).

=R5

Fill 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.

Download the Gantt Chart Template

Feel free to Download the Workbook HERE.

Excel Download Practice file

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.