If you’re managing projects, creating a Gantt chart in Excel can be very helpful, even though Excel doesn’t come with one built-in. In this tutorial, we’ll guide you through the steps to build a Gantt chart that includes progress bars to show how much of each task is completed.

We’ll also add a simple dropdown menu so you can switch easily between what you’ve actually done and what you planned to do.

And here’s a snapshot of how it will look:

This Gantt Chart in Excel uses two simple methods:

  1. First, we’ll use a stacked bar chart to display the duration of each task.
  2. Then, we’ll add error bars to show how much of each task is completed.

If you’re looking for a straightforward way to create a Gantt chart in Excel without the progress bars, check out our dedicated article 👉 HERE.

Step 1: Set Up Your Project List

Start by listing all your tasks; this will be your ‘Plan’. Next to this, make a copy of the list where you’ll record the ‘Actual’ time each task takes.

Include columns for both the planned and the actual working days for each task.

Also, make a note of any holidays that occur during your project timeline in column K.

💡 To make your spreadsheet easier to navigate, use consistent colors for different types of cells. For example:

  • Color all input cells light yellow.
  • Color all cells with formulas light green.

This color coding helps you quickly see which cells are for entering data and which contain calculations.

Calculate the End Date for Each Task

To figure out when each task will finish, we’ll use the WORKDAY function in Excel. This function helps skip weekends and any holidays you’ve listed.

For example, to find out the end date for a task called ‘Design completed’, enter this formula in cell D5:

=WORKDAY(B5, C5, $K$5:$K$14)

Once you’ve set this up for the first task, simply drag the formula down to apply it to other tasks in the list.

Use the same method in column H to calculate the actual end dates. This ensures consistency in tracking both planned and actual timelines.

To easily switch between the ‘Plan’ and ‘Actual’ views on your chart, you’ll need to set up a dropdown menu:

  • In cell A17, type “Select Scenario.”
  • Then, in cell B17, go to the ‘Data’ tab and select ‘Data Validation.’
  • Choose ‘List‘ as the criteria and enter ‘Actual, Plan‘ as the options.

This dropdown menu will let you later quickly change the view of your chart to either the planned or actual timeline.

Step 2: Data Preparation for the Gantt Chart

Preparing your data is crucial for any Excel chart. Let’s break it down into the separate parts.

Dynamic Labels for Each Task

We want to have dynamic labels for each task in our Gantt chart, based on the scenario selected from the dropdown menu.

In cell M5, enter the following formula:

=IF($B$17="Plan", A5&" - "&C5&" WD", A5&" - "&G5&" WD")
  • $B$17 refers to the dropdown where you select the scenario.
  • A5 lists the task name.
  • C5 shows the planned working days.
  • G5 shows the actual working days.

Then just copy down the formula to the tasks below. This will display the working days used for each task, depending on whether ‘Plan’ or ‘Actual’ is selected.

Dynamic Start Date and Total Days

The start date and duration of each task in your Gantt chart will vary depending on whether you choose the ‘Plan’ or ‘Actual’ scenario from the dropdown menu.

Start Date

In cell N5, use the formula:

=IF($B$17="Plan", B5, F5)
  • B5 is the planned start date.
  • F5 is the actual start date.

Copy this formula down to set the start dates for all tasks.

Total Days

In cell O5, enter:

=IF($B$17="Plan", D5-B5, H5-F5)
  • D5 is the planned end date.
  • B5 is the planned start date.
  • H5 is the actual end date.
  • F5 is the actual start date.

Copy this formula down to calculate the total days for each task based on the selected scenario.

This approach ensures your chart later accurately reflects the timeline for either the planned or actual progress of your tasks.

Percentage Completion

In your Gantt chart, you’ll want to visualize which tasks are fully completed and which are still in progress. We’ll use a green fill on each task bar to show this. For example, if a task is 100% complete, the entire length of the bar will be filled with green.

Let’s prepare the data for this in two steps:

  • For each task, put an input field in column I. You can enter the progress as a percentage.
  • Calculate Days Used: First, convert this percentage into completed days. This comparison shows how many days are spent versus the total days for each task. In cell P5, enter the formula:
=IF($B$17="Plan", 0, I5*O5)
  • I5 is the percentage completion
  • O5 holds the total days for the task

Copy this formula down to calculate the percentage completion for each task.

With our data now fully prepared, we’re ready to move on and create the Gantt chart.

Step 3: Creating the Gantt Chart in Excel

Before we add the Gantt chart, there’s an important step. We need to ensure it displays correctly. Temporarily switch the format of the Start date in your data from ‘Date’ to ‘General’.

This prevents Excel from mistakenly placing dates on the Y-Axis. When the format is set to ‘General’, Excel interprets the data as intended. It aligns it correctly for our Gantt chart. Let’s make this adjustment and then insert our chart.

Now that we’ve adjusted our data format, it’s time to create the chart. First, select the data you need for your Gantt chart, which includes the tasks, start date, and total days. Highlight the range from cells M4 to O13.

Then, go to the ‘Insert’ tab, and choose the ‘2-D Stacked Bar Chart’. This will generate your Gantt chart based on the selected data.

The orange part of the graph represents the total days each task will take. This section will form our Gantt chart. It will show how long each project task takes.

Formatting the Gantt Chart

Once the chart is inserted, you might notice some formatting issues. Here’s how you can fix these:

  • Reverse Task Order: To display the tasks in sequential order from top to bottom, double-click the task axis to access the Format Axis settings and select “Categories in reverse order”.
  • Remove the Bottom Stack: To focus on the key parts of our Gantt chart, remove the bottom stack of the bars, which are typically blue. Double-click on the blue series, then go to the ‘Fill’ section and choose ‘No Fill’. For the ‘Border’, select ‘No Line’.
  • Format the Bars: Next, adjust the visible orange bars to make them stand out more. Select the orange bars and in the ‘Format Data Series’ sidebar, set the ‘Gap Width’ to 100% to make the bars thicker. Change the ‘Fill’ color of the bars to gray.
  • Adjust Date Display on X-Axis: To show dates, not numbers, on the X-axis of your Gantt chart, adjust the format of the Start dates in your table. First, select the Start date cells (N5-N13) and switch from ‘General’ to ‘Short Date’ in the ‘Home’ tab. Then, make the chart wider to fit the dates.
  • Clean Up Chart Appearance: For a cleaner look, remove unnecessary vertical gridlines and the chart’s outline. Also, remove the legend.
  • Adjust Axis Start Point: To improve the layout of your Gantt chart, you may need to adjust the position of the gray bars to close any large gaps. Simply double-click on the X-axis, where the dates are displayed, and increase the minimum bounds.

Here’s a look at how our Gantt chart is shaping up after these changes:

Finally, check that the chart updates correctly. Do this by switching between ‘Actual’ and ‘Plan’ using the dropdown menu.

Add a Dynamic Chart Title

To clearly display whether the “Actual” or “Plan” view is shown on the Gantt chart, set up a dynamic title. Enter the following formula in a cell near your data table, such as M1:

 ="Project Timeline: " &B17 &" View"
  • Here, B17 is the dropdown cell where you choose between ‘Actual’ and ‘Plan’.

Next, click on the chart’s title box, go to the formula bar, and reference the cell containing the formula (e.g., M1).

This setup ensures the chart title updates automatically. It shows the selected view, like “Project Timeline: Actual View,” when ‘Actual’ is picked. Picking ‘Plan’ will update the title. To make the chart title stand out more, apply bold formatting.

Step 4: Add Percentage Completion

To visually indicate which tasks on your Gantt chart are complete and which are still in progress, you can use error bars. Here’s how to set this up:

Activate Error Bars: To add error bars to your Gantt chart, first click on the “invisible”, bottom stacks in front of the gray bars. Then, look for the ‘+ Sign’ near the chart, which opens the chart elements menu. From there, check the box for ‘Error Bars’ to activate them.

Configure Error Bars: Double-click on an error bar to bring up the ‘Format Error Bars’ sidebar. Set the Direction to ‘Plus‘ under ‘Horizontal Error Bar’ to only extend rightward. Next, select ‘No Cap‘ for the ‘End Style’.

Set Error Bar Length: To ensure the error bars show completion accurately, adjust their length. Make the length match the completed days. For example, if a task is 100% complete in cell I5 and the total days are 8 (in cell O5), set the error bar length to 8 days. Go to ‘Format Error Bars’. Then, choose ‘Error Amount’ and ‘Custom‘. Next, pick ‘Specify Value’. Finally, for ‘Positive Error Value’ select cells in the preparation table for completed days (P5-P13).

Format Error Bars: To format error bars, select ‘Fill & Line’ and change the fill color to green. Finally, increase the width to 8 pt to make them thicker.

We now have a Gantt chart in Excel that not only shows percentage completion but also allows for the flexible display of different scenarios. This makes it a powerful tool for tracking project progress effectively.

Download the Gantt Chart Template

Download our free Excel template 👉 HERE.

Customize it to fit your needs and streamline your project planning in just seconds!

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.