Although Excel doesn't have a built in Gantt chart type, you can follow the steps below to create a simple project plan in Excel, with a Gantt chart timeline.
To see the steps for creating a project task list and Gantt chart, you can watch this short video. There are written steps below the video, with details and screen shots for this technique. In the video, the list are built using named Excel tables. You can learn more about named Excel tables, if you're not familiar with using them
There are specialized tools that let you build Gantt charts for project management, so why would you make a Gantt chart in Excel?
If project management is the main part of your job, you probably have sophisticated project management programs, such as Microsoft Project. If you need more information on that program, this link takes you to the Microsoft Project page on the Microsoft website, where you can see its features and pricing.
However, there are valid reasons for making a Gantt chart in Excel:
So, for small projects, where you don't need to manage a complex set of tasks and resources, Excel can probably do what you need.
With Excel, you can create a simple list of project tasks, and a Gantt chart to show the project timeline. With this solution, you don't need to invest time in learning Microsoft Project, or another project management program.
To keep track of non-working days, there is a list of holidays in the sample workbook, on a sheet named Lists.
In that list, there is a named range, HolidayList, based on the dates in column B. That name will be used in the task list formulas.
NOTE: To use this sample file for your own project, list all the upcoming holidays that are non-working days for your company. The table will expand automatically, when you add new items.
In this example, the project is named, Create Company Budget, and it starts on June 1st.
You can change that to your project name and start date, at the top of the Tasks worksheet.
The next step is to list all the project tasks, in the table on the Tasks sheet, to create a project schedule.
The formula in the Start Date column uses the WORKDAY function to calculate the starting date for each task.
The WORKDAY function has 3 arguments:
This is the formula in cell C6, to calculate the start date for the first task:
=WORKDAY(MAX(C5,$B$2), SUM(D5), HolidayList)
The heading row has text, so the MAX function and SUM function are used, to prevent errors in cell C6
It's easier for Excel to make a bar chart if only the number columns (Start Date, Days) in the chart data have headings.
However, a named Excel table can't have empty column header cells, so we can't clear cell B5, where the "Label" heading is.
To solve that problem, there's a new set of headings, in cells B3:D3, and row 3 is hidden.
To see the new set of heading cells, unhide row 3.
To create a Gantt chart, so you can see the project timeline, follow these steps:
A stacked bar chart is inserted on the worksheet. You can move and resize the chart, if it is covering the project task list.
Note: In the next section, you'll see how to format the chart. For example, those blue bars for the Start Date will be changed to No Fill, so only the red bars, for the task duration, are showing.
Next, follow these steps, to format the stacked bar graph, so it looks like a Gantt chart, instead of a normal stacked bar chart
In the stacked bar chart that was created, the earliest tasks are at the bottom of the chart, so we'll reverse the order. That will move the earliest tasks to the top of the chart, where people expect to see them.
Follow these steps to reverse the order of the project tasks in the chart:
Now the project tasks are listed from earliest to latest, in the vertical axis at the left of the chart.
Next, you'll format the Start Date series -- the blue bars -- and edit series so it isn't visible
A legend isn't needed in the Gantt chart, so follow these steps to remove it:
After you delete the legend, the chart's plot area will automatically expand, to fill the empty space.
When the chart was created, the minimum date on the horizontal axis was automatically set, and the first task's start date is near the centre of the chart.
For the Gantt chart, there should not be a big gap before the vertical axis and the first task. Tthe first task should start close to the vertical axis, at the left of the chart, instead of near the centre.
To remove the gap between the vertical axis and the first task, follow these steps, to format the horizontal axis:
By default, the chart's bars are thin, and far apart. In most Gantt charts, the bars are thicker, and close together.
To make the Gantt chart's bars thicker, and closer together, follow these steps:
When you created the stacked bar chart, Excel automatically created a default chart title -- Chart Title.
To change the default title to something meaningful, you could create a static title, or use a simple trick to create a dynamic chart title.
If you want a chart title that is different from the project name, follow these steps, to type a static chart title:
Note: If you want a different chart title later, repeat these steps to change the title.
If you want a chart title that is the same as the project name, follow these steps, to connect the chart title to your project name cell.
Then, if the project name is changed, the chart title will update automatically.
For a dynamic chart title, that is linked to your project name, follow these steps:
The completed Gantt chart looks much better than the original stacked bar chart. The improved chart shows:
As an optional step, you can add more formatting to the chart. For example:
Or, make other changes to the chart formatting, such as:
Gantt Chart - Named Tables: To see the task list and Gantt chart, with named Excel tables, get the Excel Gantt chart Tables workbook. This is the example used in the written steps above. The zipped file is in xlsx format, and there are no macros in this sample file.
Gantt Chart - No Tables: To see the task list and Gantt chart, with NO tables, get the sample Excel Gantt chart NO tables workbook. This is the example shown in the video above. The zipped file is in xlsx format, and there are no macros in the file.
Last updated: January 17, 2022 12:32 PM