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, for the same technique, but using named Excel tables.
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.
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 and SUM functions 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 cart data have headings. However, a named Excel table can't have empty heading cells, we can't clear cell B5, where the "Label" heading is.
Instead, there's a new set of headings, in cells B3:D3, and row 3 is hidden. To see the heading cells, unhide row 3.
To create a Gantt chart, follow these steps:
A stacked bar chart is inserted on the worksheet
You can move and resize the chart, and in the next section, you'll see how to format it
Next, follow these steps, to format the stacked bar chart, so it looks like a Gantt chart
The earliest tasks are at the bottom of the chart, so we'll reverse the order:
Next, you'll format the Start Date series, so it isn't visible
A legend isn't needed in the Gantt chart, so remove it
The chart's plot area will expand to fill the empty space.
The minimum date on the axis is automatically set, and the first task's start date is near the centre of the chart.
To remove the gap between the Vertical axis and the first task, follow these steps:
To make the Gantt chart's bars taller, follow these steps:
To change the default chart title to your project name, follow these steps:
The completed Gantt chart shows the timeline for the project tasks, with the first task's bar starting at the vertical axis, and the project name appears at the top.
As an optional step, you can select the chart, and on the Format tab at the top of Excel, change its Shape Outline to a thin black line.
Gantt Chart - Named Tables: To see the task list and Gantt chart, with named Excel tables, download 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 the file.
Gantt Chart - No Tables: To see the task list and Gantt chart, with NO tables, download 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.
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: July 31, 2020 12:44 PM