Excel doesn't have a Gantt chart template to insert built-in, but that doesn't mean we can't make one.
There is a way to display the information from your Excel document in Gantt chart format in the same spreadsheet.
Unfortunately, it is a bit harder to make a Gantt chart in Excel than with a dedicated Gantt chart tool, but not impossible.
In this tutorial, you're going to learn how to make a Gantt chart in Excel, step by step.
By utilizing the bar graph offered in Excel, you can format it to function as a Gantt chart.
To begin, create a project table, a list of the projects with start dates, end dates and the duration of the project.
This is where you add in your specific projects with their corresponding dates and duration.
You can add as many dates and tasks as you want.
To calculate duration, use the formula [=C2-B2] as shown in the screenshot below:
From here, you need to create a stacked bar chart from the starting date data.
In order to create the Bar graph, highlight the starting date data and click:
Insert > Charts > Bar > 2-D Bar > Stacked Bar:
Once added, we're going to begin formatting and manipulating the bar chart to make it look like a Gantt chart.
First, you need to add an additional series to your graph.
Right click anywhere in the area of the chart and access the menu. You should Right Click > Select Data:
From here you’ll open a menu that lets you choose an additional data source. As you can see, the Date is already added under Legend Entries (Series) but now you need to add Duration as well.
This will open up the edit series menu which allows you to type the duration into the series name which will effectively add it to the series.
Then click the arrow next to the series values:
From here a mini edit series will open.
Select your project Duration data by clicking on the first Duration cell all the way to the last Duration – in our example, we're using D2 to D6.
D1 and D7 aren't included because they are a header/empty, respectively.
To finalize this step, click to minimize the screen > Ok > Ok
Once all the Edit Series screens have been okayed, the resulting graph should appear as seen below:
From here, you need to edit the information even further using the edit series menu from before.
Right-Click anywhere in the chart area > Select Data > Edit:
Then the mini edit series will open again. Select the cells containing all of your tasks.
You can hold shift and click on the first cell and the last cell – this will highlight all of the cells in between as well.
In our example, we're using A2 to A6.
Now the graph should look like this:
As seen above, you now have a cool looking stacked bar graph, but it's not a Gantt chart yet.
We need to remove the blue bars so that only the parts representing the project tasks will be visible.
Click a blue bar > Right Click > Format Data Series
Once there The Format Data Series window will pop up with several options. Go to the Fill tab and select No Fill. Then go to the border color and select no line
You may have noticed that the tasks are not in the right order.
That’s what we’re going to fix up right now. Click on the list of tasks in the left-hand part of your Gantt chart to select them.
This will display the Format Axis dialog for you. Select the Categories in reverse order option under Axis Options and then click the Close button to save all the changes.
To reverse the order of the tasks Click on the list of tasks > Format Axis Display > Axis Options > Select Categories in reverse order > Close Button
Now that you’ve made everything look properly like a Gantt chart, it’s time to improve the design.
You can adjust the hues and colors in the Themes menu using different themes, colors, or both.
Changing the design using color will just alter the colors throughout the design while themes will change most minor formatting aspects including font, hues, and sizes.
Go to Page Layout > Themes > Colors or Page Layout > Themes > Themes
With your finished Excel Gantt chart, it should look something like the screenshot below.
As you can see, making a Gantt chart in Excel is pretty tedious and time consuming.
Sure, it can be done, but you could save a lot of time if you used a dedicated tool.
Rather than trying to make a Gantt chart in Excel, Word or another application, using a dedicated tool is the best way to go.
Preceden is a great timeline and Gantt chart creation tool.
Check out this professional timeline we made in just minutes:
It's easy, fast, and free to sign up!