Excel Gantt Chart Project Plan

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.

Introduction

List Holidays

Project Information

Download Sample Files

Introduction - Excel Gantt Chart

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.

List Holidays

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.

HolidayList named range

Project Information

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.

enter project information

List Project Tasks

The next step is to list all the project tasks, in the table on the Tasks sheet.

Enter the task name in the Task column
In the Label coluimn, enter a short name, to be used in the Gantt chart labels
Do NOT type in the Start Date column -- it has a formula
In the Days column, enter the number of days that the task will take to complete

enter project tasks

Start Date Formula

The formula in the Start Date column uses the WORKDAY function to calculate the starting date for each task.

WORKDAY function

The WORKDAY function has 3 arguments:

start_date: Previous task's start date (C5)
days: number of days for the previous task (D5)
holidays: named range - HolidayList

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

C5 is text (zero value). The start date in B2 is greater than zero, so it's used instead
D5 is text (zero value), so no days are added

start dates for each task

Add Chart Data Headings

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.

Cell B3 is empty --
Cell C3 is linked to the Start Date heading in the task table
Cell D3 is linked to the Days heading in the task table

hidden chart heading cells

Create the Gantt Chart

To create a Gantt chart, follow these steps:

On the Tasks sheet, select the 3 heading cells -- B3:D3
Press Ctrl, and select the data in the Label, Start Date and Days columns
On the Excel Ribbon, click the Insert tab
In the Charts group, click the drop down arrow on the first chart type - Insert Column or Bar Chart
In the Bar Chart section, click on Stacked Bar

insert stacked bar command

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

stacked bar chart

Format the Gantt Chart

Next, follow these steps, to format the stacked bar chart, so it looks like a Gantt chart

Reverse Order

The earliest tasks are at the bottom of the chart, so we'll reverse the order:

Right-click the labels at the left, and click Format Axis
In the Format Axis pane, scroll down, and for Horizontal Axis Crosses, choose At Maximum Category
At the end of the options list, add a check mark to "Categories in Reverse Order"

categories in reverse order

Format Start Date

Next, you'll format the Start Date series, so it isn't visible

Click on one of the Start Date bars, to select the entire series
In the Format Series pane, change the fill colour to No Fill

Start Date bars no fill

Scroll down, and format the border with no line

Start Date borders no line

Remove Legend

A legend isn't needed in the Gantt chart, so remove it

Right-click on the chart legend
Click the Delete command

The chart's plot area will expand to fill the empty space.

remove the chart legend

Format Axis

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:

Right-click the Horizontal (Value) Axis, where the dates are shown
Click the Format Axis command
In the Format Axis task pane, on the Axis Options tab, type the project start date in the Minimum box for Bounds.
- NOTE: Excel will automatically change the minimum to the start date's serial number
Close the Format Axis task pane

Format the Bars

To make the Gantt chart's bars taller, follow these steps:

Right-click one of the bars, and click Format Data Series
In the Format Data Series task pane, go to the Series Options tab
Change the gap width to zero, or a very low percentage number
Close the Format Data Series task pane

change the gap percentage

Chart Title

To change the default chart title to your project name, follow these steps:

Click on the chart's title, to select it
In the Formula Bar, type an equal sign
Click on cell A2, where the project name is
Press Enter, to complete the formula

change the gap percentage

Completed Gantt Chart

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.

completed Gantt chart

Download Sample Files

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.