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

Why Make Gantt Chart in Excel?

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:

• not everyone has a sophisticated project management tool, or can get one
• some people have Microsoft Project, but aren't experts at using it
• for a small project, people want to use Excel, because they have good skills in that program

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.

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, to create a project schedule.

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, to calculate task start dates. That formula is shown in the next section
4. In the Days column, enter the number of days that the task will take to complete -- the task duration.

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 function and SUM function are used, to prevent errors in cell C6

• C5 is text (zero value). The start date in B2 is greater than zero, so that start date is used instead
• D5 is text (zero value), so no days are added to the start date

Add Chart Data Headings

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.

• 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, so you can see the project timeline, 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
  • Do NOT select the column headings in the table -- just the data
• 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 2-D Bar Chart section, click on Stacked Bar

Stacked Bar Chart

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.

To move the chart:

• If the chart is not selected, click anywhere on the chart border, to select the chart
• Point to the chart border
• When the cursor changes to a 4-headed arrow, drag the chart to a new location on the worksheet

To resize the chart:

• If the chart is not selected, click anywhere on the chart border, to select the chart
• Point to one of the small round handles on the chart border
• When the cursor changes to a 2-headed arrow, drag the handle in or out, to resize the chart

Format the Gantt Chart

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

Reverse Order for Categories

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:

• Right-click the axis labels (task names) at the left side of the chart
• In the pop-up menu, click Format Axis
• In the Format Axis pane, the Axis Options tab should be automatically selected
• In the Axis Options section, scroll down, to the Horizontal Axis Crosses section
• In that section, click the radio button to choose At Maximum Category
• Scroll down to the bottom of the Axis Options list
• Add a check mark to "Categories in Reverse Order"

Now the project tasks are listed from earliest to latest, in the vertical axis at the left of the chart.

Format Start Date

Next, you'll format the Start Date series -- the blue bars -- and edit series so it isn't visible

• Left-click on any one of the blue Start Date bars
  • Excel will automatically select the entire Start Date series
• In the Format Series pane, change the fill colour to No Fill

• Scroll down to the Border options
• Click the radio button for No line, to make the series border invisible
• Click in an empty part of the chart, to see the effect of those formatting changes

Remove Chart Legend

A legend isn't needed in the Gantt chart, so follow these steps to remove it:

• Right-click on the chart legend
• In the pop-up menu, click the Delete command

After you delete the legend, the chart's plot area will automatically expand, to fill the empty space.

Format Horizontal Axis

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.

Remove the Gap

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:

• Right-click the Horizontal (Value) Axis, where the dates are shown
• In the pop-up menu, click the Format Axis command
  • 
• In the Format Axis task pane, on the Axis Options tab, click the triangle, to show the Axis Options list
• In the Bounds section, click in the Minimum box
• Delete the number that was automatically entered entered in the Minimum Box.
  • That number is a date, but in General format, instead of date format
• In the Minimum box, type the project date, in short date format
  • 
• Press the Enter key or Tab key, to complete the change
  • Note: Excel will automatically change the short date to a number, in general format
• Close the Format Axis task pane

Format Data Series Bars

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:

• Right-click one of the bars, and click Format Data Series
• In the Format Data Series task pane, click on the Series Options tab (green column chart icon)
• Under Series Options, click in the Gap Width box
• Change the gap width to zero, or a very low percentage number. In the screen shot below, the setting was changed to 10%
  • Note: You can type a number in the Gap Width box, or adjust the slider
• Close the Format Data Series task pane

Change Chart Title

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.

Type a Static Chart Title

If you want a chart title that is different from the project name, follow these steps, to type a static chart title:

• In the chart, click on the chart title, to select it
• With the cursor, select the default text in the chart title
• Type a new title to replace that text

Note: If you want a different chart title later, repeat these steps to change the title.

Make a Dynamic Chart 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:

• 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 entered
• Press Enter, to complete the formula

Completed Gantt Chart

The completed Gantt chart looks much better than the original stacked bar chart. The improved chart shows:

• timeline for the project tasks
• first task's bar starting at the vertical axis
• start date series is hidden
• project name appears in the title at the top of the chart
• legend was removed

More Formatting

As an optional step, you can add more formatting to the chart. For example:

• Add a border to the chart object, so the chart stands out on the worksheet, as shown below
  • Select the chart, and click the Format tab at the top of Excel
  • Change its Shape Outline to a thin black line

Or, make other changes to the chart formatting, such as:

• Change the series colour, from red, to match your company's preferred colour choices
• Use a different font for the chart title and axis labels
• Change the font sizes
• Use a different date format on the horizontal axis

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

More Tutorials

Create a Named Range

Date Functions

Charts, Interactive

Charts, Line-Column 2 Axes