Are you looking for a tool to schedule your tasks, track their progress, and visualise your project’s progress? If so, a Gantt chart is the perfect tool for project management purposes, and you can create one in Excel using the chart feature.
Though Microsoft Excel does not have a predefined Gantt chart as an option, you can create one using this free template Gantt project planner template for Excel.
You can quickly create a Gantt chart in Excel using the bar graph functionality and some formatting.
This simple guide lists the steps to create a Gantt chart that is easy to follow.
What is a Gantt Chart and What is it Used For?
A Gantt chart is a project management tool that shows activities, tasks, or events displayed against time. The Gantt chart includes the start and end dates, milestones, and dependencies between tasks. It shows a list of activities on the left-hand side and depicts the time scale at the top of the chart.
Each activity is represented by a bar, and the bar’s length shows the activity’s duration and the end date. The position of the task in the Gantt chart reflects the start date of the activity.
A Gantt chart allows you to do the following:
- View planned activities.
- Track the date of the project’s beginning and end.
- View the duration of each activity.
- View what activities overlap and by how much.
- Track the progress of the whole project.
Gantt charts allow supervisors to quickly see if work schedules are behind, ahead, or on track. Initially, Gantt charts were written on paper, but with the rise of digital technology, they became increasingly complex and elaborate.
Gantt charts are helpful for everyone with different working schedules and natures. They can be used by:
- Managers, Chief Executive Officers, and General Managers at the management level.
- Construction Project Managers, Engineering, and Architectural Managers in monitoring a project.
- Operations Managers, Marketing Managers, Consulting/Finance Managers, Event Planning Managers, and Customer Service Managers in the business operations department.
- Military Officers and Public Infrastructure Managers in the public and government sectors.
Benefits of Using a Gantt Chart
Project execution involves planning, allocating, monitoring, and fulfilling the client’s requirements. There are multiple people with varying skills involved in the project completion, and the Gantt chart helps in the following ways to achieve the target.
Visualise a Project
Gantt charts help to plan, visualise the project, and organise it into smaller, more manageable tasks. The smaller tasks appear on the Gantt chart on a scheduled timeline, with dependencies between tasks, assignees, and milestones.
Allocate Resources
Gantt charts can be employed to track the logistics and resources required for a project. Task dependencies ensure that a new task can be started only if the previous task is completed.
If a task is delayed, then dependent issues are automatically rescheduled, which can be extremely useful when planning in a multi-team environment. This way, you can avoid deadlocks in resource allocation and planning.
Track the Progress
As your project moves forward, you can monitor it to ensure it is moving in the right direction. Otherwise, you can allocate more resources and make necessary changes to achieve your goal. You can include release dates, milestones, and other important metrics to track your project’s progress.
Better Time and Resource Management
Managers can efficiently monitor the resources across different departments, the budget allocated, working hours, software, or equipment to appropriately assign tasks to team members. Which helps to optimise every member’s work, based on completed tasks, pending tasks, and future plans.
Represents Milestones
Gantt chart milestones are used as checkpoints to mention important key points along the project schedule on the Gantt Chart. The key points can be starting dates or finishing dates of the project, meetings, or deadlines in the project.
How to Create a Gantt Chart in Excel?
Excel is extensively used to store data, perform calculations, and create graphical representations, such as charts and graphs. You can create a Gantt Chart using the chart feature in Excel.
Create a Table
A Gantt Chart deals with time elements, creating a table with tasks, start date, and end date.
Create a table with headers and include one more field, ‘duration,’ alongside the end date. Use the formula =C2-B2 to calculate the difference between the dates.
Create an Excel Bar Chart
Let’s take this Gantt Chart example mentioned below, and proceed with creating a bar chart first.
Step 1: Select the range of your start dates with the column header. It’s B2:B10 in the example taken. Ensure to select only the cells with data and not the entire column with empty spaces.
Step 2: Select Insert tab → Charts group.
Step 3: Select 2-D Bar, then Stacked Bar chart type. This generates a chart, as seen below in the example.
Include Duration to the Chart
Step 4: Right-click anywhere within the chart area and choose Select Data from the menu, as shown in the screenshot below.
The Select Data Source window opens with the Start Date already added under Legend Entries (Series). Now, you need to add Duration there as well.
Step 5: Click the Add button to select more data, the Duration column in the table, so that you can plot that too in the Gantt chart.
Step 6: The Edit Series window opens, prompting you to enter the Series name.
Step 7: In the Series name field, type “Duration” or any other relevant name. Or, you can place the mouse cursor into this field and click the column header in your spreadsheet. The clicked header will be added as the Series name for the Gantt chart.
Step 8: In the Series Value field, click the range selection icon 
next to the Series Values field.
Step 9: A small Edit Series window opens.
Step 10: Select the Duration column by clicking the cells in D2:D10.
Step 11: Click the Collapse Dialog icon to exit this small window.
Step 12: It brings you back to the previous Edit Series window with the Series name and Series values filled in.
Step 13: Click OK.
Step 14: Now you are back at the Select Data Source window with both Start Date and Duration added under Legend Entries (Series).
Step 15: Again, click OK for the duration data to be added to your Excel chart.
The resulting Gantt chart example looks like this:
Add Tasks to the Chart
If you notice in the Gantt Chart, the tasks mentioned in the table in the range A2:A10 are not mentioned in the chart; rather, they appear as numbers.
You need to replace the numbers with the list of tasks on the chart. Follow these steps to make those changes.
Step 16: Right-click on the chart as you had before, and choose Select Data.
Step 17: It brings up the Select Data Source window again.
Step 18: Ensure that the Start Date is selected on the Legend Entries (Series) found on the left-hand side.
Step 19: Now, click on the Edit button on the Horizontal (Categories) found adjacent to Start Date.
Step 20: A new window, Axis Label, opens, and you need to select the tasks in the range A2:A10.
Step 21: Once you have selected the cells, click OK on the Axis Labels window and one more time on the Select Data Source window.
Step 22: You find that the Select Data Source window has the task lists on the Horizontal (Category) Axis Labels. Click OK.
Step 23: Your chart will have the tasks listed on it.
Transform the Chart
There’s still some formatting left to be done on the chart to transform it into a Gantt Chart. It’s still a bar chart, and the tasks are listed in reverse order.
Step 24: Remove the horizontal blue bars so that only the orange parts are visible.
Step 25: You can’t delete them, but you can make them invisible by clicking on any of the horizontal blue bars. You should see that all the bars are selected.
Step 26: Right-click on it and select Format Data Series.
Step 27: Select the Fill tab and select No Fill.
Step 28: Switch to the Border Color tab, and select No Line.
Step 29: To reverse the order of the tasks, click on the list of tasks on the left-hand part of your Gantt Chart to select them.
Step 30: It opens the Format Axis window. In the Axis Options tab, select the Axis Options category.
Step 31: Select the Categories in reverse order option, then click the Close button to save all the changes.
Step 32: You notice that the tasks are arranged in the correct order on the Gantt Chart, and the dates have shifted to the top of the chart.
Step 33: A Gantt Chart is created, as seen in the example below.
Frequently Asked Questions
1. How do I use a Gantt Chart template in Excel?
Excel does not have a predefined Gantt chart feature, but you can create one using the Gantt project planner template for Excel. Microsoft 365 subscription offers premium Gantt Chart templates that you can try.
In Microsoft Excel versions from 2013 to 2021, you can download it from Microsoft’s website – Gantt Project Planner template. Then, go to File → New, type “Gantt” in the Search box, and start using the template of your choice
2. Are there any project management software alternatives available?
There are many project management software, such as Monday.com, ClickUp, Wrike, and Microsoft Project. All these are scheduling software that is used to track the progress of a project.
Wrapping Up
In this article, you learned how to create a Gantt Chart using Excel. Learning Excel gives you a competitive edge over other applicants in the job search as there are so many useful features. Here are 3 Reasons You Need to Learn Microsoft Office, Professionally.
At Academy of Learning Career College, we offer courses in Excel and Microsoft Office to teach you the basics or advance your knowledge. Contact us to learn more about the diploma programs and certifications available.
