How To Make a Simple Gantt Chart in Excel With Dates and Subtasks? (Free Template Included)

A Gantt chart may be the easiest solution if you struggle with project management and find it difficult to manage tasks and follow their status. Henry Gantt developed it in the early 1900s and found its way to modern days due to its simplicity and efficiency. 

And if Henry Gantt was using paper and pencils for his charts, nowadays you can use Excel and simplify your job even more. You’ll have everything in one place, the possibility to share the file digitally, and all the support provided by free Excel templates. So here is everything you need to know about using a Gantt chart to manage your projects.

Contents:

  • What Is a Gantt Chart?
  • Why Are Gantt Charts Important?
  • What Are the Key Components of a Gantt Chart?
  • How to Make a Gantt Chart in Excel?
  • Free Excel Gantt Chart Template Download

What Is a Gantt Chart?

A Gantt chart is a chart that displays tasks and sub-tasks against time horizontally. It shows how much time was allocated for a task but also the current status of the task and the dependency between tasks. A Gantt chart is essentially a visualization of a project’s timeline and status.

Like for any two-dimensional chart, you need X and Y coordinates. Typically, a Gantt chart has tasks and activities on the Y-axis and time on the X-axis. The Y-axis is displayed on the left side of the chart, and the X-axis is at the top.


Why Are Gantt Charts Important?

It may seem strange that such an old chart is still popular today, but the Gantt chart’s advantages proved it to be useful to anyone managing a project. 

The Gantt chart allows you to visualize the initial timeline of a project, the scheduling of tasks, and the dependencies between tasks. It helps you break the project into tasks and sub-tasks and see which ones can be done in parallel and which ones need to wait for other tasks to be completed. When you have a clear map of the project’s tasks, you can assign them to your employees accordingly. In return, employees will know how to plan their work, when they can request leave, and which tasks have higher priorities.

The Gantt chart also allows you to visualize the progress of each task. If you notice a task is stuck, you can allocate more manpower or resources and avoid further delays. The chart helps you adapt and solve problems before it’s too late. You will know the project’s status with precision, be able to generate accurate reports and avoid overwhelming your employees with last-minute tasks or deadline stress.

Because it is such a transparent project management tool, the Gantt chart keeps managers and employees up to date, improves personal and team time management, and creates a trustworthy work environment.


What Are the Key Components of a Gantt Chart?

When we look overall at a Gantt Chart, the key components that we must consider are:

  • A list of activities, tasks, and sub-tasks that cover the entire project from start to end
  • Start date and duration of each activity, task, and sub-task
  • Employees in charge of task development or assigned to do the task
  • Milestones and important deadlines cannot be postponed.

You can customize the Gantt chart by adding important components for your project, such as meetings, deliverables, fairs or conferences participations, or outsourced tasks. You can change the colors to highlight the progress or priority of a task—for example, red for delayed tasks and green when they are according to the plan.


How to Make a Gantt Chart in Excel?

Although Excel doesn’t have a built-in Gantt chart, it provides all the tools to make one in just a few minutes. All you have to do is follow the next steps:


1. Add Data to the Spreadsheet

A Gantt chart displays the list of tasks on the Y-axis and time on the X-axis. So, start your chart by listing all the tasks and sub-tasks of the project. For each of them, add the start date and estimated end date in separate columns. Then, in the fourth column, add a formula to calculate the task’s duration. Rename the sheet ‘Data’ so you can know where to look for the project’s values and data.


2. Create a Stacked Bar Chart

Go to a new worksheet, give it a meaningful name, and insert a 2-D Stacked Bar chart from the menu Insert/Bar/2-D Bar. Although the chart is empty for the moment, resize and reposition it to have a clear view.


3. Add Start Date to the Stacked Bar Chart

Now, you need to populate the chart with data. Let's see how to do this:

  • Write-click on the new chart and choose ‘Select data’ from the dropdown menu. Excel will prompt a window allowing you to select the data you want to display on each axis of the chart. The first data series is for the x-axis. 
  • Click on the ‘Add’ button.
  • Write ‘Start date’ in the Series name edit box, and select the start dates from the Data sheet
  • Click ‘OK’ and move on to the next data series.


4. Add Duration to the Stacked Bar Chart

The second data series is for the y-axis and corresponds to the Duration series.

  • Click again on the ‘Add’ button.
  • Write ‘Duration’ in the Series name edit box, and select the duration values from the Data sheet.
  • Click ‘OK’ and take a look at the newly populated chart. Don’t close the ‘Select Data Source’ window just yet.


5. Bring Tasks and Sub-tasks to the Chart

However, at this point, you have some strange dates on the y-axis and not the name of your tasks and sub-tasks. So,

  • Select ‘Duration’ from the Legend entries (Series) panel.
  • Click on ‘Edit’ in the Horizontal (Category) Axis Labels. This allows you to select the labels you want to display on the y-axis, in this case, the list of tasks from the Data sheet. 
  • Press ‘OK’; the chart will start to look much more like a Gantt chart. 
  • Press ‘OK’ again to close the Select Data Source window and apply all selections to the chart.


6. Edit y-axis Labels

Even if the chart now shows time on the x-axis and tasks on the y-axis, it still doesn’t look right. To begin with, the tasks are in reversed order (from last to first). Secondly, the dates on the x-axis don’t start when the project starts. Thus, it’s time to adjust labels.

  • Select the y-axis labels.
  • Write-click and choose ‘Format Axis…’ from the dropdown menu. The Format Axis window provides many settings for both the y-axis and the x-axis and may be confusing. However, keep in mind what you need to do: reverse the order of tasks on the y-axis and choose more appropriate dates for the minimum and maximum values of the x-axis.
  • In the Format Axis window for the y-axis, check the ‘Categories in reverse order’ checkbox. Excel will automatically reverse the tasks’ order on the y-axis and move the x-axis labels from below the chart to on top of the chart. 
  • Close the window to apply the changes.


7. Edit x-axis Labels

Now you need to do the same for the x-axis labels. The x-axis Format Axis window will look a little bit different because it includes minimum and maximum values, which is exactly what you need. The trouble is it doesn’t let you select dates. It requires numbers. Therefore, you need to transform the start and end date of the project into numbers.

To do that, go to the Date sheet, select the start date of the first task, write-click, and choose ‘Format Cells…’ from the dropdown menu. In the Format Cell window, change the Category from Date to Number and copy the number in the Sample box. For example, February 6th, 2024, will be 44963. Click Cancel (because you don’t want to change the format, but just to see the corresponding number). Repeat the process to find the number corresponding to the project’s end date. 

Note: Excel considers January 1st, 1900, day 1. When transforming a date into a serial number, Excel counts how many days after January 1st, 1900. You can also use the function DATEVALUE () to transform a date into a number.

Once you have the numbers,

  • Select the x-axis labels.
  • Write-click and choose ‘Format Axis…’ from the dropdown menu.
  • In the Axis Options category, check the Minimum Fixed value and introduce the number corresponding to the project’s start date. 
  • In the Axis Options category, check the Maximum Fixed value and introduce the number corresponding to the project’s end date
  • Close the window.


8. Get from a Stacked Bar Chart to a Gantt Chart

There is one more step to make the Stacked Bar a Gantt chart, and that’s cleaning up. First, you don’t need the legend. 

  • Select the legend and press Delete.

Then, you don’t need the blue bars; you only need the red ones that represent the duration of each task. So, 

  • Click on the blue bar. All of them will be selected. 
  • Write-click and choose ‘Format Data Series’ from the dropdown menu. 
  • Go to the Fill category and check the ‘No fill’ option.
  • Go to the Border Color category and check the ‘No line’ option. 
  • Press Close, and the blue bar will disappear.

This is also the place to check your data. If you see a gap between the horizontal lines, it means you don't have continuous planning. It may be intentional (e.g., employees are working on other projects or are on leave) or an error.


9. Personalize Your Excel Gantt Chart

Although it may seem you don’t need to customize your Gantt chart, fine-tuning brings a lot of benefits. For example, you may want to:

  • Add the project’s name, manager, and start date.
  • Use different colors for tasks and sub-tasks.
  • Set up more accurate time labels, such as daily or weekly

These small details improve your workflow and efficiency. Remember, the Gantt chart is a visual tool.


Download the Free Excel Gantt Chart Template

Suppose you don’t have time to make a Gantt chart in Excel from scratch. In this case, we offer the option to freely download our Gantt chart template for Excel and customize it for your project. Our template includes all the basic data discussed here, plus task assignments, task status, and task progress indicators. You can even print it on A3 paper and hang it on the office’s wall.

Gantt Chart Free Template for Excel


Conclusion

A Gantt chart is a simple method to visualize a project’s development. It’s easy to create in Excel, customizable, and transparent. And if you aren’t using smart HR apps to track tasks and time off, you can use a Gantt chart to monitor employees’ time off and see when their leaves overlap. It’s always much easier to spot trends and analyze data when you can visualize them.