Here’s how to use them
Key things
- A Gantt chart is a visual representation of tasks on a timeline.
- Excel does not have the ability to easily insert a Gantt chart into a spreadsheet.
- Creating a Gantt chart in Excel involves preparing the data, inserting a stacked bar chart, and formatting it to look like a Gantt chart.
Excel is a powerful project management tool, but it lacks one key feature: a built-in Gantt chart. This can be frustrating for project managers who rely on Gantt charts to visualize timelines, track progress, and manage tasks. Despite this, Excel can be turned into a powerful Gantt chart tool with a few simple steps.
What is a Gantt chart (and why should you use it)?
A Gantt chart is a commonly used project management tool that displays tasks as horizontal bars on a timeline. The bars represent the start and end date of each task, allowing you to see the entire project schedule at a glance.
You should consider using a Gantt chart when you need a detailed visual representation of your project schedule. Instances where you might use them include tracking deadlines, milestones, and task dependencies in a large project; coordinating all the moving parts in event planning; or planning project phases when carrying out a construction project or home renovation.
One of the most important uses of a Gantt chart is to highlight overlapping tasks and bottlenecks in a project and then take corrective action.
How to create a Gantt chart in Excel
Excel doesn’t have the ability to embed a Gantt chart, which means you’ll have to create one using a modified stacked bar chart. Don’t worry – it’s not as hard as it sounds.
Prepare your data
We will use the data below to create our Gantt chart. It is a simple table with tasks, start days and duration of each task (in days).
Task | Start date | Duration (days) |
---|---|---|
Planning | 1/11/2024 | 5 |
Reservation of place | 6/11/2024 | 3 |
Invitations | 9/11/2024 | 4 |
Catering | 13/11/2024 | 2 |
Decoration | 15/11/2024 | 3 |
Final preparations | 18/11/2024 | 2 |
The day of the event | 20/11/2024 | 1 |
Here is a screenshot of what it looks like in Excel.
Insert stacked bar chart
To insert a stacked bar chart, start by making a selection Insert tab in the top menu. Then click Insert a bar or bar chart in Charts section and select Folded bar in 2-D lane part of the offer. You can also select 3-D Folded Stripe in 3-D stripe section.
Select your data
Now we need to select the data that goes into the chart. Right-click on the stacked bar chart and click Select Data in the menu.
Next, click on Add v button Records of Legends (series) section.
Click on the text box below Series nameand then tap on Start date header in the sheet. Next, click in the text box below Series values and delete what’s there to make it empty. Then select all data and click OK to insert them into the graph.
Back in Select Data source dialog box, click Add button again. Then select Duration (days) header in the pro sheet Series name. For Series values select all days under this heading in the text box.
Next to Records of Legends (series) section v Select Data source dialog box, you will see Horizontal (category) axis labels section. Click on Edit button.
In the dialog box that appears, click on the text box and select all tasks under Task header in the sheet and click OK.
Click OK conclude Select Data source dialog box.
You will notice that the tasks are in reverse order. We can quickly fix this by right-clicking on them in the graph and selecting Axis format.
IN Axis format panel on the right, check Categories in reverse order check box – is under Axis position section.
This should reverse the tasks and put them in the correct order.
Format the graph into a Gantt chart
Our Gantt chart is doing well. To finish this we need to do a little formatting. First, let’s get rid of those blue bars.
To do this, right click on them and select Data series format in the menu. Select an option in the panel on the right Fill & Line tab and tap No padding switch in Fill section.
Now we have something that looks very much like a Gantt chart.
The last step is to remove the data, which gives us extra padding on the left. We do this by setting the minimum bounds of this series to the start date of the planning phase, which is 1/11/2024.
The problem is that the minimum bounds are represented by a number and the data here uses a character Short date format. Fortunately, Excel allows us to easily convert data into its numerical values.
To do this, switch to Home in the top menu and select the first date below Start date. Next, click the drop-down arrow next to it Date in Number ribbon group and select Number in the menu.
The date should now be converted to a number that we can use as a minimum bound for the dates.
Copy the number, right click on the data in the graph (it will also be converted to numbers) and select Axis format. IN Axis format panel on the right, enter the number in the text box next to it Minimum in Limits section.
Now this extra data should disappear and your Gantt chart should be complete.
With a little formatting, you can make your Gantt chart look more visually appealing. For example, you can change chart colors or add axis names to an Excel chart. However, if you don’t want to create one from scratch, there are many Gantt chart templates for Excel that you can download and customize to your needs.