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 - 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.

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.

List Project Tasks

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

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

Start Date Formula

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:

• 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

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

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

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

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"

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

• Scroll down, and format the border with 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.

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

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

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.

Get he Sample Files

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 the 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.

Don't Miss Our Excel Tips

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.

More Tutorials

Create a Named Range

Date Functions

Charts, Interactive

Charts, Line-Column 2 Axes