How To Make a Gantt Chart In Excel
If you’re only using calendars to gauge your team’s workload, then you’ll be limited in knowing exactly what is happening.
Calendar views are great for knowing when events occur, but not necessarily for determining the duration of a project.
In contrast, Gantt Charts provide a graphical depiction of what your organization is working on, so you can better match resources with the upcoming workload.
Working with only calendars is like being on a football field in the middle of the action, but without the knowledge of the coach above the field seeing all the plays unfold. Calendars lack the bird’s eye view, and if you’re a project manager, then you need something that goes deeper. That’s where Gantt charts come in.
What are Gantt Charts?
Gantt charts are visually appealing, sophisticated, and smart with many features that make project management a breeze. Ever since the Gantt Chart was introduced by Henry Gantt in the 1910s, it has provided a groundbreaking way to manage projects. The Gantt Chart is a simple and visual representation of all projects, every task within the project and interdependencies between them so you can track progress and ensure smooth workflow.
Knowing how your resources are being used allows you to allocate them more efficiently, and better plan for the future using Gantt charts in project management software.
If you’ve had any experience with project management, you’ve probably seen a Gantt chart. A Gantt chart shows the duration of tasks that need to be done in a project.
It looks like this:
A Gantt chart is a very helpful tool for any project manager. It allows you and your team to visualize how all the tasks in a project come together, and how they fit in a timeframe.
If you need of a Gantt chart for a project right away, here’s the good news. You don’t need anything fancier than MS Excel, a tool you probably already have on your computer, to make it happen. You can make your own Gantt chart in Excel and this is how to do it.
NOTE: This approach does have some limitations, and we’ll discuss them at the end of this article.
How To Make a Gantt Chart in Excel
Step 1: List Out All Project Activities and Make a Project Table
-To start, list out all the activities that need to happen in your project.
-Create four columns.
-Label the columns task, start date, end date and duration.
-Use a simple formula to calculate duration if you enter both start date and end date. Here’s an example of a workflow for creating a simple lead capture sequence or a microsite to support a marketing campaign:
Excel does not take into account weekends or holidays so you’d need to make sure your date range and duration are including non-working days.
Step 2: Create an Excel Bar Chart
With all the activities listed out, you can start creating the Gantt chart by setting up a Stacked Bar chart.
Select the cells with data (not the entire column) under “Start Date”
Go to Insert > Chart 3.
Then click on Bar > 2-D Bar > Stacked Bar
Now you have a stacked bar chart added to your Excel worksheet.
Step 3: Add Duration Information
Next, you need to include the duration data in your bar chart.
Right-click anywhere within the chart and choose Select Data
This will call up the Select Data Source window:
As you can see, the start date information (column B) is already in place.
Now you’ll add the duration by clicking on the “add” button.
In the Series Name or Name field (depending on the version of your software), enter “Duration,” or any label you want to use.
Then, click the range selection icon next to Y values or Series values. A small Edit Series or Select Data Source window will show up:
Add your project duration data by selecting the cells in the Duration column. Do not to include the header cell or any empty cell.
When you’re done, you should see the information populated in the Y values or Series values box:
8. Click OK in the dialogue box to add that data to your Excel chart.
9. Now, you’ll have a bar chart that looks like this:
Step 4: Include Task Descriptions in Your Gantt Chart
Now you may notice the items on the vertical axis on the chart are just numbers. We’re going to replace those numbers with the names of the tasks.
- Right-click on the chart area and choose Select Data:
2. The Select Data Source window will open up. Select Start Date under Legend Entries or Series. If you have a newer version of Excel, click on the Edit button under Horizontal (Category) Axis Labels:
3. If you’re using an older version of Excel, you may instead see a field called Category (X) axis labels. Click on the range selection icon:
A small Axis Label or Select Data Source window will open up.
4. Select the cells under the header “Task” – again, make sure you don’t select the header cell or any empty cell.
5. After you’ve made the selection, click on the range selection icon again to exit the Axis Label window. You’ll be taken back to the Select Data Source dialogue box. Click OK to confirm your changes and exit the window.
6. Now your bar chart’s vertical axis is showing task names instead of numbers:
7. Next, right-click on the text box that says “Start Date” and “Duration,” then select Delete:
Step 5: Turn the Bar Chart Into a Gantt Chart
Now you have a stacked bar chart. To turn it into a Gantt chart, you need to remove the blue bars so only the orange parts that represent the tasks are visible. Note that we’re not actually deleting the blue bars. We’re going to make them transparent so they’re no longer visible.
Click on any blue bar on the chart and all the blue bars will be selected:
Right click and select Format Data Series:
Go to the Fill tab and select “no fill”:
4. Then go to the Line or Border Color tab and select “no line”:
You’ve probably noticed that the tasks on the vertical axis are listed in reverse order. To make them display in chronological order from top to bottom, first click on the task names on the left-hand vertical axis to select them:
Then right-click and select Format Axis:
After you’ve called up the Format Axis window, select the Categories in reverse order option and click OK to save:
Now your chart will look like this:
Step 6: Design Your Gantt Chart
Now your chart is starting to look quite a bit like a Gantt Chart. But you’re not done yet. There are a few things you can do to improve the design and make it presentation-ready.
Remove the white space on the left side of the chart – when we made the blue bars invisible, the space taken up by them remained.
To fix this, go back to your project table, select the first Start Date and right-click on it. Select Format Cell > General and notice there’s a number showing under “sample”:
After you’ve noted down that number, click cancel because you don’t want to make any changes. Go back to your Gantt Chart and click on any date above the horizontal axis. Right-click and select Format Axis:
If you’re using a newer version of Excel, change Minimum to Fixed and type the number you noted down earlier.
If you have an older version of Excel, uncheck the “auto” box next to Minimum and enter the number you noted down earlier:
Now you’ve moved the orange bar so the one representing the first task starts right next to the task description on the vertical axis:
Now you’ll want to adjust the date intervals so that the dates on the Gantt chart may be displayed at a scale that makes sense for your project’s timeframe.
Just like the last step, click on any date above the horizontal axis on your chart. Right-click and select Format Axis. Uncheck the box under “auto” next to Major unit and Minor unit. Then input the numbers you want for your date intervals:
You might need to play around and adjust the values to find the best interval for your project timeframe.
Tighten up the white space between bars – this will make your Gantt chart more compact and easier to read. Click on any orange bar and all will be selected. Then right-click, and select Format Data Series:
In the Format Data Series dialogue box, decrease the number for Gap width until the separation between the orange bars looks good to you:
Now you have a tighter and better-looking chart:
You can also go back into the Format Data Series dialogue box to adjust the color and even add 3-D format to your bar.
Tada! You have made a Gantt chart!
Here are a few things to know about making Gantt charts in Excel
- The chart will automatically resize when you add or delete tasks
- When you change the start dates or duration on your project table (note: not the end dates, because they’re not set up as a dependency,) the chart will automatically adjust and reflect the changes
- You can then save your Gantt chart as an image or publish as HTML.
Here’s the Truth About Making A Gantt Chart in Excel
It definitely seems convenient to make your Gantt chart and manage your project with Excel – after all, it’s a “standard-issue” software that most would already have on their computer.
While an Excel Gantt chart may suffice for a simple project that involves just a few resources and a short duration, it often falls short when you need to manage complex projects of larger scopes and involve multiple stakeholders.
Manually creating Gantt charts even with a smart and useful program like Excel can take hours. That’s right–hours!
Here are some limitations to tracking and managing projects on Excel:
- The file often sits on the hard drive of one particular person in the team, making real-time collaboration and update of project status almost impossible.
- It doesn’t have functionalities necessary to support the management of a more complex project. E.g. management dashboard, the ability to add task details and dependencies, resource allocation, and reporting. While some of these could be set up in Excel, the process will need to be repeated for every single spreadsheet and it’s definitely not at a push of a button.
- It doesn’t have the built-in flexibility to respond to changes in project requirement.
- Excel doesn’t allow you to make templates or automate project set up to simplify your workflow.
If you’re leading projects that involve many disciplines and resources, managing projects in Excel is very inefficient, frustrating and challenging.
Instead of having to spend an hour building a Gantt chart for every single project, and wrangling with Excel any time there’s a change of plan, wouldn’t it be more efficient to simply enter all the dates and dependencies then have the rest done for you?
If that sounds good to you, then it’s time to look into investing in a project management software that takes “building Gantt chart,” yelling at Excel, and more off your to-do list.
Automatically Make Gantt Charts With Project Management Software
Project management software with Gantt charts provide online collaboration features, which allows users to get a “snapshot” of all ongoing projects and send document from a central dashboard. Gantt charts are great for a broad view of the project deadlines and schedule. They do not have the individual project tasks, but you’ll be able to quickly set and determine those tasks with project management software, something Excel can’t do.
Many departments would love to see updates across the whole organization and project management software can make it happen without having an Excel superstar. Gantt charts work well for marketing, research and development, strategy, operations, finance, human resources and more.
There are many project management software you can choose from, depending on your budget and objective. If you’re looking for one that allows you to manage a large volume of complex projects all within a user-friendly interface, Workzone project management software may fit the bill.