How-to Make a Basic Gantt Chart in an Excel Chart in 7 Easy Steps

Project Managers and Executives love Gantt Charts.  They also love Microsoft Excel, Charts and Graphs.  So it is a natural to consider how you too can make a Gantt Chart in Excel.

Here is a sample of a Gantt Chart from Microsoft Project:image

Here is a sample Excel Gantt Chart:image

So how do we and how can you do this?  Read this tutorial to find the 7 easy steps.

 





The Breakdown

1) Create the Project Plan Data

2) Create Gantt Chart Data Chart Range

3) Create a 2-D Stacked Bar Chart

4) Change Fill Series to No Fill



5) Change the Horizontal Axis Minimum, Major Unit and Number Format

6) Change the Vertical Axis to Category in Reverse Order

7) Chart Clean Up: Remove the Chart Legend and the Vertical Gridlines

 

Step-by-Step





1) Create the Project Plan Data

You will probably have data that looks like this and you want to create a Gantt Chart.  But if you try and graph it now, it won’t give you what you want.  So create some sample data like this to try it out:image

2) Create Gantt Chart Data Chart Range

In order to make a Gantt Chart, you need to create a different range that you will use for the Excel Gantt Chart.

I am creating a range outside of your project plan for the Excel chart so that it is easier to graph, but it is not necessary.



A) Create a column of data called “Task Name” and then copy the Task Name from your project plan data in the spreadsheet.

B) Create a column of data called “Fill” and then copy the Start Date from your project plan data in the spreadsheet.

C) Create a column of data called “Days” and then put in a formula that Subtracts your task Start Date from the Finish Date (in the graphic below the formula for cell G3 =C3-B3.image

IMPORTANT: This is an important step when building your Gantt Chart Data Area.  You need to highlight your Fill series and change the Number Format to General or Number.

Your final Gantt Chart Data area should look like this:image





3) Create a 2-D Stacked Bar Chart

Highlight the Gantt Chart data range including the column headers from Cell E2:G6.image

Then go to the Insert Ribbon and in the Chart Group, Select a 2-D Stacked Bar Chart from the Bar Button.image

This is what you will now see in Excel:image

The next step is where your Excel Gantt will really take shape.



4) Change Fill Series to No Fill

This step makes it look very close to a Microsoft Project Gantt Chart.  Right Click on the Fill data series in the Excel Chart and select “Format Data Series…” from the Pop-up Menu:image

From the Format Data Series dialog box, you should select the Fill Options on the left and then choose “No Fill” on right.SNAGHTML155aab7a

Your Excel Gantt Chart will now look like this:image

Almost done as it looks like a Gantt Chart, but we need to fix the Axis formatting in the next tutorial steps.





5) Change the Horizontal Axis Minimum, Major Unit and Number Format

A) Right click on the Horizontal Axis on the bottom and click on “Format Axis…”image

Then from the Axis Options on the left, change the Minimum to the same number as your earliest Project Plan Task Start Date in a number format that you see in the Fill series.  Also, change the major unit equal to 7 so that it shows weekly dates.SNAGHTML1561a0f0

Now don’t leave the Format Axis dialog box as we can do the next part as well.

B) Select the Number options on the left, and then choose the Date Category on the right and select a short date format from the Type picklist.SNAGHTML1541f2d6



Your chart should now look like this:image

Looking like a real Gantt Chart.  But not good enough for us.  Lets do 2 real quick things:

6) Change the Vertical Axis to Category in Reverse Order

The MS Project Gantt Chart has the dates at the top of the chart, not the bottom.  So how do we move the Horizontal Axis dates on top?

Right Click on the Vertical Axis that displays the Project Plan Task Names and choose “Format Axis…”image





Then from the Format Axis dialog box, you should choose the Axis Options on the left and click the checkbox “Categories in reverse order”.

SNAGHTML15429bc1

Your chart should not look like this: image

7) Chart Clean Up: Remove the Chart Legend and the Vertical Gridlines

We now just need to clean up a few things. Let’s start by deleting the legend.  First select the legend and press the delete key.



You may also want to delete the vertical grid lines.  This is an optional step that is really based on user preference.  Select the grid lines and press your delete key.

Your final Gantt chart should look like this:image

 

Video Tutorial

Watch how to make a basic Gantt chart with this quick video tutorial.





http://youtu.be/Rnufw0fPYIg

Please let me know if this will help you make your own Excel Gantt Chart.  Also, don’t forget to sign up for my RSS Email feed so that you get the next Excel post delivered right to your inbox.

Steve=True





6 COMMENTS

    • Hello,

      Right Click on your horizontal axis and choose “Format Axis…” from the pop-up menu.
      Then choose Number options on the left.
      Then choose the Date category and the date Type you want on the left.

    • Hi Bobbie,

      You can correct this by:
      1) Selecting your chart
      2) Go to your Design Ribbon
      3) Click on the Select Data Button
      4) Click on the Horizontal (Category) Axis Labels button
      5) Select the range for your tasks and press okay on the Axis Label Range dialog box
      6) Click Okay on the Select Data dialog box

      That should do it.

      Steve=True

  1. Hi there,
    I am wanting to create a graph like the one above which is blue and red.
    I want this to be for 4 machines and for 24hrs.
    I am very rusty on excel and would appreciate any help.

    I do have a spread sheet of this already but there is a lot of info on it.

    Kind Regards

LEAVE A REPLY

Please enter your comment!
Please enter your name here