Once exclusively used by professional project managers, advances in technology—particularly spreadsheet and Gantt chart software—have made using a Gantt chart accessible even to those with limited experience managing workflows.
In this blog post, I will cover why you shouldn’t actually use Excel to create Gantt charts, given the advancements I mentioned, as well as how to make a Gantt chart in Excel, if you so choose.
What Is A Gantt Chart?
Ah, the elusive Gantt chart—a tool so revered in the world of project management. But what is it exactly? Well, in layman's terms, it's a fancy chart that helps you visually organize and track tasks and timelines for a project.
Think of it as a high-tech to-do list on steroids. With its bar charts and timelines, a Gantt chart can help you visualize the project schedule, stay on top of deadlines, and ensure your project is running smoothly. The vertical axis includes all the tasks that will be completed during your project, and the horizontal axis represents the estimated amount of time each task will take.
So if you want to impress your boss and colleagues with your project management skills, hop on the Gantt chart bandwagon—and don't forget to wear a helmet.
Why You Shouldn’t Make A Gantt Chart In Excel
Hold up—before you open Excel and start mapping out your next big project, let me tell you why you might want to hit pause. Excel Gantt charts can be a hassle to create, difficult to modify, and prone to errors.
Plus, with so many innovative project management tools available, why settle for something as basic as Excel? Trust me, your project (and your sanity) will thank you if you ditch the Excel Gantt chart (or the Google Sheets Gantt chart) and explore some fresh alternatives. Here are my reasons why:
1. There are better alternatives available
There are many different Gantt chart software programs specifically designed for creating Gantt charts. These programs are much easier to use than Excel and will allow you to create a professional-looking Gantt chart with ease. Also, most project management software comes with built-in Gantt chart functionality these days.
Here are some Gantt chart makers and apps that you should consider using instead of Excel. Find out more about key features and pricing by reading our full guide to Gantt chart makers.
2. Gantt charts are meant to be simple
A Gantt chart is a type of bar chart that shows the start and end dates of various tasks in a project. Gantt charts are meant to be simple, but Excel makes them complicated by requiring a lot more legwork to set it than other tools (which you’ll see in the next section).
3. Excel is not designed for Gantt charts
Excel is a spreadsheet program, and it’s not designed for making Gantt charts. Sure, you can make a Gantt chart in Excel, but it’s going to be a lot more work than using a dedicated Gantt charting program.
4. You’ll end up with a messy chart
Because Excel wasn’t designed for making Gantt charts, you’re likely to end up with a messy chart if you try to make one in Excel. This is because Excel doesn’t have all of the features that you need to make a clean, professional-looking Gantt chart.
5. It will be hard to update your chart
If you need to make changes to your Gantt chart, it will be much harder to do so if you made it in Excel. This is because you’ll need to manually update each task’s start and end date, which can be time-consuming and error-prone.
6. You won’t be able to share your chart easily
If you want to share your Gantt chart with others, it will be much harder to do so if you made it in Excel. This is because you would need to send them the entire Excel file, which may be too large for some people to open. Additionally, people who don’t have Excel installed on their computer won’t be able to open the file at all.
Making a Gantt chart in Excel may seem like a simple and convenient solution, but don't be fooled—sometimes convenience is not always the best choice. Excel may be a powerful tool for number-crunching and creating spreadsheets, but it was not designed with project management in mind.
Gantt charts require a level of complexity that can be difficult to achieve in Excel, leading to a confusing and cluttered chart that can be more of a hindrance than a help. So, if you're tempted to take the easy route and use Excel for your Gantt chart, think twice—there are much better options out there that will make your life easier in the long run.
How To Make A Gantt Chart In Excel in 12 Steps
Let's dive into the steps of making a Gantt chart in Excel, in case you do decide to go this route (again, you really shouldn’t).
First, input your project tasks into an Excel spreadsheet. Next, set up a timeline with start and end dates. Then calculate the task duration, add a stacked bar chart, and format it according to your needs. Finally, add a legend and add some conditional formatting for the status indicators so that the chart is visually appealing and easy to read.
Follow this step-by-step guide to create a Gantt chart in Excel from scratch. Read more about creating a Gantt chart here.
1. Create a new spreadsheet
Open Microsoft Excel and create a new spreadsheet document. Add the Gantt chart title as the file name.
2. Enter the names of each task
In column B, in row 1, add the column description ‘Task Title’. Then continue from row 2 by entering the names of each task that needs to be completed for the project, listed in order from the first task to the last task. Once you added all the task names, I recommend you bold your milestones for easier reference.
3. In column C enter the title ‘Task Owner’
Then, in row 1, assign an individual task owner for each task. This is the person who is responsible for its progress and completion on time.
4. Enter the column title ‘Start Date’ in the first cell of column D
Enter the first start date in cell D2 and then provide a starting date for each task in column D, using a numeric format such as dd/mm/yyyy or whichever works best for you.
5. Enter the column title ‘Due Date’ in the first cell of column E
Then enter the finish date of your project in the second cell of column D, following the same numeric format used for the start date.
6. Calculate the duration of each task in days
In column F, use the function ‘=DAYS360(D3;E3)’ and so forth for each row.
Your Excel sheet should look like this:
7. Create a dropdown for the task status
Then, add a status for each task in column G. The recommended task status indicators are:
- Not started
- Green
- Yellow
- Red
- Completed
- Ongoing
8. Add WBS numbers
Add a WBS number or other numbering for easy reference in Column A, on the left side of the task names.
9. Add your dependencies
To do this, add a column for ‘Task Predecessor WBS Number’ in Column H and complete it for each task as required.
Now, your Excel sheet should look like this:
10. Insert a stacked bar chart
Add a stacked bar chart to your spreadsheet based on the task names, start date, and duration.
Within the same worksheet that contains your Excel table, click in any blank cell. From the Insert tab on the ribbon, choose Charts. Set up your Excel Gantt by selecting the chart type bar graph and then select Stacked Bar Chart from the drop-down menu in the Charts category of the ribbon (do not pick 100% Stacked Bar).
Then click inside the empty chart area to get to the Select Data Source menu.
To begin, we must give our project data a name (Series). Place your cursor in the blank area under Series Name and click on Start Date of your table's column header. Move down to Value for Series in the Edit Series window. This is where you will input your Task start dates. Just click on the little arrow icon and select all the start dates.
Then repeat the same steps for the duration column.
The tasks on your Gantt chart will probably be listed in reverse, with the most recent task at the top and the first item at the bottom.
You can simply change them around. To do so, click on each of the items along your Gantt's vertical axis to select them all simultaneously, as well as open up the Format Axis task pane. Tick the Checkbox Categories in reverse order under the header Axis Options and subheader Axis Position within the Format Axis task pane.
11. Customize the format of the stacked bar chart
Click on the blue part of any bar in your Gantt chart to select all of the task bars at once. Select Format Data Series from the right-click menu when you're done formatting each data series.
To access Fill & Line settings, go to Excel's Format Data Series window and click on the Fill & Line icon (like a paint can). Under fill select No fill from among the options. Choose No line as the border option beneath it.
For example, I have bolded the date/task axis labels and changed the color of the bars.
12. Add some conditional formatting
Add conditional formatting to the status indicator column and add a legend underneath the table to explain the status indicators in order to provide users like your team members with some extra information.
Here’s what your Excel Gantt chart should look like after completing steps 1-12.
Is There A Gantt Chart Template For Excel?
When it comes to project management, a Gantt chart is an essential tool for keeping everything organized and on track. There are plenty of Excel Gantt chart templates out there, and many Google Sheets templates can be repurposed for Excel.
You’ll find several Gantt chart templates and project timeline templates within DPM Memberhip, but I’d again emphasize to first try out some of the other tools listed above—they’ll make your life a lot easier.
Over To You
Making a Gantt chart in Excel can be daunting, but if you’re really set on doing it this way, these 12 steps make it much simpler. Again, making a Gantt Chart in Excel often is not the best option due to its inherent limitations.
Instead, using specialized software may be the best solution to ensure that the project runs on time and meets all expectations. If you found this article helpful, why not subscribe to The Digital Project newsletter for more tips about Gantt charts and other related topics? You'll be glad you gave it a read.