Microsoft Excel is a popular tool to create data-driven charts. However, it does not come with Gantt charts. If you’re looking for a step-by-step tutorial on how to make a Gantt chart in Excel, this article is for you.
In this article you’ll learn:
- What is a Gantt chart?
- What are the important elements of a Gantt chart?
- Advantages and disadvantages of creating Gantt charts in Excel
- Step-by-step process to create a Gantt chart using Excel
- Free Excel Gantt chart templates
- A simpler and free alternative to making Excel Gantt charts
Let’s get started.
What is a Gantt Chart?
A Gantt chart is a visual representation of a project’s timeline. It helps project managers, stakeholders, and team members understand:
- A project’s roadmap,
- Important milestones along the way,
- Work dependencies,
- Required resources,
- And the risks involved.
Like most charts, a Gantt chart has two axes. The horizontal axis represents the project’s timeline. On the other hand, the vertical axis lists the activities or tasks to complete to finish the project.
Important elements of a Gantt chart
A Gantt chart has 5 important elements. These are:
- Tasks: Project tasks are listed along the vertical axis. In the early stages, these are often high-level tasks. On the other hand, as the project progresses, tasks become more detailed.
- Timeline: A dateline runs across the horizontal axis of a Gantt chart. It is divided into days, weeks, and months.
- Bars: Each bar represents a task on the Gantt chart. Because of the timeline, you can visualize each task with its start date, end date, and duration.
- Milestones: Milestones represent important dates by which certain tasks must be completed. Project milestones make the project more manageable and risk-free.
- Resources: Each task may have one or more team members assigned to complete a task.
Types of Gantt Charts
Most projects have two types of timelines charts. These are:
- Project plan timeline chart: A project timeline helps visually plan a project, track its progress, and identify resources/risks on the way.
- Team timeline chart: A team timeline helps visually plan resource availability and allocation. It also helps manage the workload of your team members.
There are a lot of ways you can make a Gantt chart.
You can use a whiteboard, or spreadsheet software like Microsoft Excel, or an online tool like Toggl Plan.
Each tool has its own advantages and disadvantages. Let’s look at the pros and cons of using Excel to create a project Gantt chart.
Advantages of Excel Gantt Charts
Using Microsoft Excel to create a Gantt chart may sound like a no-brainer for the below reasons:
- Easy to get started: Over a billion people use the MS Office. It’s reasonable to assume that using Excel has almost a zero learning curve.
- No need to sign up for a new tool: Since most people already have access to Microsoft Excel, there isn’t a need to purchase or sign up for a new tool.
- Plays along nicely with other Office tools: Charts created in Excel can be easily embedded into a presentation and document. This is great for presentations and reports.
- Customizable: Excel makes it easy to customize charts. You can easily color charts to match your business brand.
Disadvantages of Excel Gantt Charts
Getting started with Excel charts is easy. But, collaborating with stakeholders and team members to keep the Gantt chart updated is hard. Particularly, if you’re working with a remote team.
Excel Gantt charts have the following disadvantages:
- Can’t work collaboratively: It’s almost impossible to work collaboratively on an Excel project plan. Whether it’s for creating a plan with stakeholders or keeping track of the project’s progress with team members.
- No access control: It’s often chaotic when multiple people work on the same spreadsheet. It takes just one mistake to ruin all the hard work.
- Hard to keep up-to-date: Plans change and need adjustments from time to time. You may need to update rows upon rows of data to accommodate these changes.
- Multiple versions: Often managers combat these disadvantages by keeping backup copies of Excel sheets. But, keeping track of all these versions can quickly become a nightmare.
How to make a Gantt chart in Excel?
Even with all these big drawbacks, there are times when it makes sense to create a Gantt chart using Excel. Particularly, in the early stages of a project. Or when you have to quickly present a project plan visually.
Here’s how you can make one using a stacked bar chart:
Note: The screenshots below are from Excel 2019. Some of these screenshots are slightly different in Excel 2010, 2013, and 2016. However, these steps work for all Excel 2010+ versions.
1. Add Project Data to Excel
Let’s start by adding our project’s planned timeline in text format.
Create a new spreadsheet and columns for tasks. Each task has the task name, a start date, and duration. Here’s a sample spreadsheet for a simple construction project.
2. Insert a Stacked Bar Chart
Excel does not come with an inbuilt Gantt chart.
Instead, we’ll improvise and create one using a stacked bar chart.
To do that, select your task information. Next, click on the Insert Tab > Bar Chart > Stacked Bar Chart.
Next, let’s populate the chart with our task data. To do that, right-click on the chart. Then click Select Data…
This opens the Select Data Source dialog. Here we’ll set the Legend Entries to task start dates and durations. Also, we’ll set the Horizontal Axis Labels to display task names.
Click on the Add button in the Legend Entries (Series) section to add the range of start dates and durations.
Task start date:
- Set the Series name to “Start Dates”.
- Set the Series values to the cell range that contains the task start dates.
- Set the Series name to “Durations”.
- Set the Series values to the cell range that contains the task durations.
Next, we want to add the tasks to the vertical axis. This is done by editing the Horizontal (Category) axis labels.
Click on the Edit button to add task names to the Horizontal (Category) axis labels.
3. Format the chart to make it look like a Gantt Chart
The stacked bar is ready. Your tasks are laid out on the chart as blue and orange bars. But, it doesn’t look like a Gantt chart.
Let’s format it to look like one.
Right-click on the blue part of the bar. Then click on Format Data Series… to open the Formatting dialog.
On the dialog, set the Fill option to No fill.
Now our chart looks a bit more like a Gantt chart. But still has a couple of issues:
- The tasks on the vertical axis are in reverse order.
- And, the start date range on the horizontal axis is too wide.
4. Fix the task order
To fix the first issue, right-click on the vertical axis.
Then click on the Format Axis… context menu to open the Format Axis dialog.
On this dialog, check Axis Options > Categories in reverse order. The tasks should now appear in the right order.
5. Fix the task durations range
Next, let’s fix the Minimum Bound of the horizontal axis. This can be done from Axis Options > Minimum Bound in the Format Axis dialog box
Unfortunately, it’s not that easy. That’s because Excel handles dates as numbers. We’ll first need to convert the project start date into a number that Excel understands
To do that, copy the first start date from your task data, and paste it into an empty cell. Next, set the formatting of the cell to Number from the Home tab > Number Format group. That should give you the Number formatted start date
Next, right-click on the horizontal axis and open the Format Axis dialog box. Set Axis Options > Minimum Bound to the Number formatted start date.
With that done, your Excel Gantt chart is ready. Any changes you make to the data will also reflect automatically in the chart.
Gantt Chart Templates for Excel
Microsoft Office comes with free and premium online templates for making Excel Gantt charts. Here’re some templates you may find useful.
- Simple Gantt Chart Template: This is a free template that you can download from the official MS Office Templates website. However, this template does not have any options to add milestones.
- Milestone and Task Project Timeline Template: This is a premium template. It offers more features than a simple Gantt chart. However, this template is available only for Office 365 subscribers.
- Agile Gantt Chart Template: This is another premium template for Agile project teams. Like all premium templates, it’s only available for Office 365 subscribers.
Using these templates, you can somewhat simplify the process of creating a Gantt chart in Excel. However, these templates still need you to add all that data. And, collaborating with your team is still not very smooth.
A Simple & Free Alternative to Excel Gantt Charts
Most people know how to create charts in MS Excel. That’s why it’s a natural choice to turn to when they want to create a Gantt chart.
But, as you’ve seen, creating a Gantt chart in Excel is quite a task. And, it’s almost impossible to collaborate with stakeholders and team members. Plus, it’s hard to keep the Excel sheet up-to-date
That’s where Toggl Plan can help.
With Toggl Plan, you can:
- Create a Gantt chart timeline to plan and track a project’s schedule.
- Create a team timeline to manage resource availability and workloads.
- Add project milestones to manage project risk better.
- Collaborate with stakeholders. Add them to a timeline or share a read-only view.
- Assign tasks to multiple team members, and track progress as they complete work.
Toggl Plan’s Gantt charts are easy to use. You can just click to add tasks on the timeline. And move them around with simple drag and drop
Best of all, Toggl Plan is completely free for up to 5 users.